Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Loading Duration Data

Go down

Loading Duration Data Empty Loading Duration Data

Post  parishilton 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

Back to top Go down

Loading Duration Data Empty Re: Loading Duration Data

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

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

Back to top Go down

Loading Duration Data Empty Re: Loading Duration Data

Post  LAndrews 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

Back to top Go down

Loading Duration Data Empty Re: Loading Duration Data

Post  parishilton 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

Back to top Go down

Loading Duration Data Empty Re: Loading Duration Data

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

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

http://aginity.com

Back to top Go down

Loading Duration Data Empty Re: Loading Duration Data

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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