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

Assitance with Time Dimension

3 posters

Go down

Assitance with Time Dimension Empty Assitance with Time Dimension

Post  Jqmartin Wed Feb 02, 2011 5:29 pm

Hello all

I am in the process of trying to build a database that will allow for effective reporting on the performance of an automated task scheduling system. This system can perform a number of tasks that take less than a second up to tasks that take over an hour. After discussion we have come to the conclusion that we can handle reporting periods of five minutes, as such I would appreciate some advice on the best way to go about this design. I envisage the table to look something like the example below (I have only put a subset of columns as others are superfluous).

recId TaskId DurationInSec startDateId endDateId startTime endTime
1 22 3 2589 2589 09:09:32 09:09:54
2 55 360 2589 2589 09:09:55 09:15:55

I am unsure on how to proceed with the time element, do I look to turn them to full timestamps and handle creation of a hierarchy in something like SSAS. Or do I move them out to a separate time dimension table and then link that in either directly to the fact table or should I look at taking an approach that encompasses both routes? As a side note can someone please advise what is the best practice when populating a time dimension that has a grain of either hours, minutes or seconds. Do you start at 0 or 1 for the first entry in the table, this is something that has been nagging away at me.

I have never worked at this level of detail before, the most I have done is to work at a granularity of days.

Any advice, pointers or links to appropriate reference material would be greatly appreciated. Should you need any more information in order to try to help me then please ask and I will do my best to supply it.

Many thanks



Posts : 2
Join date : 2011-02-02

Back to top Go down

Assitance with Time Dimension Empty Re: Assitance with Time Dimension

Post  LAndrews Wed Feb 02, 2011 5:58 pm

Kimball posted a good article on this a few years ago. (

I'd suggest in your case you look at the combination approach..... full datetime fields (to support time interval calculations) plus a Date Dimension and a Time Dimension. The time dimension starts at midnight.

The advantage of the time dimension is that it allows you to create analytical groupings without revisiting the fact. For example, you could add a Peak Hours/Non-Peak Hours attribute to the time dimension to support that type of analysis.


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

Back to top Go down

Assitance with Time Dimension Empty Re: Assitance with Time Dimension

Post  hang Wed Feb 02, 2011 7:50 pm

Andrew, that's good article by Kimball and the article is now part of his ETL book, p170, Date and Time Dimensions.

The good thing about Kimball's books is that he is always adopting new concepts in his new books based on the result of practice, even they go against his previous ideas.

However based on the latest publications by Kimball Group, there are two points worth mentioning regarding the Date and Time Dimensions, as they run against Kimballís initial idea:

1. In addition to date key from a date dimension, embed a full SQL date-time stamp directly in the fact table for all queries requiring the extra precision. Normally, you donít need a time dimension unless there is a predefined time unit. Reference: The Data Warehouse ETL Toolkit - 2004.

2. Use smart yyyymmdd integer key for date dimension instead of totally meaningless surrogate date key. The self derived smart date key is more resilient to changes on date dimension, and makes table partitioning more manageable. Reference: The Microsoft Data Warehouse Toolkit: With SQL Server2005 and the Microsoft Business Intelligence Toolset - 2006.


Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

Back to top Go down

Assitance with Time Dimension Empty Re: Assitance with Time Dimension

Post  Jqmartin Thu Feb 03, 2011 6:07 am

Thanks guys, that has helped me to focus my mind in on what to look at doing.

I am thinking that I will make use of the date & time dimension tables and the timestamp on the fact table.

Thanks once again.



Posts : 2
Join date : 2011-02-02

Back to top Go down

Assitance with Time Dimension Empty Re: Assitance with Time Dimension

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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