Managing Date_FROM/Date_TO measures

Hello,

What's the best approach for managing Date_FROM/Date_TO measures.

I've a table with Employees assign on specific Projects regarding a specific Date range

In my Dimensional model:

Dimensions:
dEmployee
dProjects
dTime

Fact:
fAssignment

Must I make 1 line for every day in the fact table (meaning that an assignment of 1 people on 4 years will generate 1460 lines)?
Is there other way to manage "range date" measures?

Thank you

Why don't you add a Number of Days measure to the fact table? If the Employee was on a project from 1/1/2010 to 12/31/2010, the value would be 365.

Thank you for your reply Jeff.

Small precision, I've other measures related like daily rate, daily cost.

Users want to be able to drill around the time dimension in every possible ways (yearly, monthly report, daily focus...).
And I can for instance have an employee assigned from the 15/01/2011 to the 21/2/2012.

thank you

What about setting your date dimension between the 2 dates? This would give you a cartesian product. You could create a view or set it up in the reporting software.

Build an accumulating snapshot fact. Each assignment is one row with the various dates that you want to track. When a particular end date shows up, just update the row. You can also add metrics correspond to the date ranges to make summing even easier.

The dates are not measures, they are dimensions. Store the start and end dates on the fact, either as degenerate dimensions or FKs to the date dimension.

No reason user could not query on any kind of date range. And, if you have your date dimension set up correctly (i.e. including a date sequence attribute) it is easy to calculate period lengths and averages.

ngalemmo wrote:The dates are not measures, they are dimensions. Store the start and end dates on the fact, either as degenerate dimensions or FKs to the date dimension.

No reason user could not query on any kind of date range. And, if you have your date dimension set up correctly (i.e. including a date sequence attribute) it is easy to calculate period lengths and averages.

Hello,

you're approach seems very interesting, however I do not understand it completely.

How can I degenerate a Time dimension from Start_Date and End_date?

What do you suggest for the relationship between the fact and the time dimension?



Thank you

They already are degenerate dimensions as they exist in your table.

My comment about measures was a technical one. The Start_Date and End_Date columns in your table are not measures, they are degenerate dimensions. These dates provide context, the period of time the particular assignment is effective. Dimensions provide context, not measures. It is degenerate because it stands alone, there is no related dimension table.

If all you are concerned about is dates (i.e. the time of day is not important) then you could formalize the dimensions and replace the two columns with FKs to the date dimension. This could offer some advantages, particularly if you need to calculate the length of the assignment and need to take into account weekends and holidays.

When I design a date dimension table I always include a day sequence column (among others). Basically you chronologically assign a sequence number to each row. If needed, you can provide a sequence based on the business calendar, skipping days not considered 'business days'. The difference between this number on two different days is the number of days between them.