Loading Duration Data

Hi all, I'm faced with what must be quite a common problem.
Our transactional system stores downtime for a machine as a start date and duration.
Our time dimension has days at the lowest level (rolled up to months and years).

I'm using SSIS to load data to a fact table and would like thoughts on how to break a 'duration' of say 72 hours into days in the time dimension.

I could use some ugly sql to split the duration up using datediff or so something similar, but I'd hope there is a more elegant solution out there!

Thanks
P

Duration is a metric, down_time_hour_cnt. The date dimension relationships would be start and end date. Are you trying to calculate end date e.g. dateadd(hh, down_time_hour_cnt, start_dt)?

If duration is a measurement of your fact, why do you need to convert it to "days in the time dimension" ?

Your time dimension should be used for the start date, not the duration.

Sorry , I didn't explain that too well.

I do indeed have a Time dimension, with duration being a fact.

Take an outage that starts at 3pm on Feb the 1st, and lasts until 6am on Feb the 3rd.

I want to see the following
Feb 1 - 9 hrs
Feb 2 - 24 hrs
Feb 3 - 6hrs

I have managed it by joining to a 'dates' table , it just feels ugly.

Store the begin and end timestamps as degenerate dimensions on the fact table. Duration is a simple derivation of the two. Wither you store actual duration on the fact or derive it dynamically is your option. The former has advantages over the latter if you are loading the fact before it has been completed. It also allows you to control the business definition of duration (i.e. control the precision of the duration value, such as rounding to 15 minutes or hour, so that sums and averages are consistent across queries). You would still have the date dimension references for other uses.

If you need day to day numbers like that, you need to load multiple rows in the fact table making the grain event and date. Duration then becomes a bit simpler, just store the number of hours or minutes during that day. You need not store the timestamps.