Loading Duration Data

View previous topic View next topic Go down

Loading Duration Data

Post  parishilton on Fri Feb 03, 2012 11:11 am

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

parishilton

Posts : 3
Join date : 2011-12-21

View user profile

Back to top Go down

Re: Loading Duration Data

Post  BoxesAndLines on Fri Feb 03, 2012 2:24 pm

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)?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Loading Duration Data

Post  LAndrews on Fri Feb 03, 2012 2:26 pm

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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Loading Duration Data

Post  parishilton on Wed Feb 08, 2012 6:38 am

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.

parishilton

Posts : 3
Join date : 2011-12-21

View user profile

Back to top Go down

Re: Loading Duration Data

Post  ngalemmo on Wed Feb 08, 2012 11:07 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Loading Duration Data

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum