"Unrelated" Time Dimension

So what I have is a basic schema, which I've included below. The highlights are a FACT_EMPLOYEE table which contains a row for every day an employee clocks in and clocks out. There is a basic TIME dimension, with a primary key on DATE.

For every month, there are obviously a certain number of gross work days. These are all the days available to work minus weekends. I have this. There is another table I created that is called FACT_AVAILABLE_HOURS. IT consists of three columns: MONTH, YEAR, AVAIL_HOURS.

For each employee, they have a specific START_DATE and an END_DATE. What I need to be able to do is: if an employee's start date falls within a month I'm reporting on, I need to be able to take the range between START_DATE (i.e., September 15th) and the first of the month, calculate the number of work days in that range, then subtract those from the AVAIL_HOURS value. This also holds true for an employee's END_DATE. If their END_DATE falls within that month, then I need to take the end of the month and calculate the number of work days between the end of the month and their END_DATE (i.e., September 15th) then subtract those hours from AVAIL_HOURS.

My first question is modeling the actual new dimension. Since the primary key of my TIME dimension is a specific date, how can I get that to work? Everything else in my cube works perfectly, and I would hate to have to start from scratch just to add this piece of functionality. Would I need to create an entirely new TIME dimension where the primary key is just month and year? Not sure how to model this particular scenario...unfortunately my current TIME dimension won't work since the primary key is an actual DATE.

Secondly, the calculation to do all of this is way beyond me! There are going to be a whole lot of of IIF's I'm sure!!

If your TIME dimension had a WORKDAY attribute that contained a 0 or 1, you would be able to calculate available work days (or hours) in a month for an individual employee something like this:

SELECT SUM(t.WORKDAY) AS [AVAILABLE_DAYS]
, SUM(t.WORKDAY) * 8 AS [AVAILABLE_HOURS]
FROM TIME t
JOIN EMPLOYEE e
ON t.DATE BETWEEN e.START_DATE AND COALESCE(e.END_DATE,’2999/12/31’)
WHERE t.YEAR = @year AND t.MONTH @month
AND e.EMPLOYEE_NO = @employee_no

I would suggest a new fact table FACT_EMPLOYEE_AVAILABLE_HOURS in which you pre-calculate the available hours for each employee for each month. You can then bring this “finished” fact table into your cube.

In any case I would try to "build up" the available hours for each employee rather than back out the hours for which they are unavilable.

VHF:

Funny you should mention that! I literally about three hours after I posted did just what you suggested. I created two new fact tables:one a FACT_AVAILABLE_HOURS table and a FACT_HOLIDAY_HOURS table and introduced them into my cube. It was SO much easier!!

Now the calculations I have to perform are going to be fairly tricky with regards to the whole START_DATE / END_DATE issue. I really have zero idea how to even approach that piece of the puzzle. In my head I can see what has to happen, but being an absolute beginner in the world of MDX, I haven't got a clue!

...and I also did realize right after I posted it would be easier to "build" the hours available (take start date of employee and calculate days going forward in that month versus trying to "subtract" days).

Would you have any ideas / insights on how to best go about this? I'm baffled!! EMP_START_DATE and EMP_END_DATE are attributes of each employee, versus actual facts.

Since I have a lot more SQL experiance than MDX, I usually tend to do calculations on the SQL side when possible. This also means the calculated values are available in the relational DW as well as in the cube.

Take another look at the sample query. it is based on START_DATE and END_DATE being attributes of the employee. Here's a revised version that should calculate the available hours for each employee for each month in your TIME dimension--add a WHERE clause if you don't need it for all the months that exist your TIME dimension:

SELECT
e.EMP_KEY
,t.YEAR
,t.MONTH
,SUM(t.WORKDAY) AS [AVAILABLE_DAYS]
,SUM(t.WORKDAY) * 8 AS [AVAILABLE_HOURS]
FROM EMPLOYEE AS e
JOIN TIME AS t
ON t.DATE BETWEEN e.EMP_START_DATE AND COALESCE(e.EMP_END_DATE,’2999/12/31’)
GROUP BY e.EMP_KEY, t.YEAR, t.MONTH

This should generate a row for each employee for each month where they were employeed for at least one day out of the month.

This does still depend on a WORKDAY 0/1 field in your TIME dimenson. 1=workday, 0=weekend/holiday. You could take the results of this query and insert directly into an FACT_EMPLOYEE_AVAILABLE_HOURS fact table which you could then bring directly into your cube. If your data isn't huge, just truncate the fact table and regenrate it every day to keep it current.

If you decide to takle it in MDX instead... well, good luck!