Assitance with Time Dimension

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

JQ

Kimball posted a good article on this a few years ago. (http://www.rkimball.com/html/designtipsPDF/KimballDT51LatestThinking.pdf)

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.

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.

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.

JQ