Time and date dimension in data warehouse?

My guess is that it depends on your reporting requirement. If you need need something like.

My guess is that it depends on your reporting requirement. If you need need something like WHERE "Hour" = 10 meaning every day between 10:00:00 and 10:59:59, then I would use the time dimension, because it is faster than WHERE date_part('hour', TimeStamp) = 10 because the date_part() function will be evaluated for every row. You should still keep the TimeStamp in the fact table in order to aggregate over boundaries of days, like in: WHERE TimeStamp between '2010-03-22 23:30' and '2010-03-23 11:15' which gets awkward when using dimension fields.

Usually, time dimension has a minute resolution, so 1440 rows.

Kimball recommends having separate time- and date dimensions: kimballgroup.com/html/designtipsPDF/Kimb... In previous Toolkit books, we have recommended building such a dimension with the minutes or seconds component of time as an offset from midnight of each day, but we have come to realize that the resulting end user applications became too difficult, especially when trying to compute time spans. Also, unlike the calendar day dimension, there are very few descriptive attributes for the specific minute or second within a day. If the enterprise has well defined attributes for time slices within a day, such as shift names, or advertising time slots, an additional time-of-day dimension can be added to the design where this dimension is defined as the number of minutes (or even seconds) past midnight.

Thus this time-ofday dimension would either have 1440 records if the grain were minutes or 86,400 records if the grain were seconds.

1 for quoting someone smart. – Björn Pollex Mar 25 '10 at 9:05 Agree, this is a good solution. – Paul James Apr 16 '10 at 8:16.

Time should be a dimension on data warehouses, since you will frequently want to aggregate about it. You could use the snowflake-Schema to reduce the overhead. In general, as I pointed out in my comment, hours seem like an unusually high resolution.

If you insist on them, making the hour of the day a separate dimension might help, but I cannot tell you if this is good design.

If date is a dimension for 10 years it has only about 3650 records. Hour-by-hour reports are very useful here - we need to compare days: monday to monday, tuesday to tuesday and hours monday 11:00-12:00 to tuesday 11:00-12:00. Do you think that snowflake is more useful/efficent than star?

– Peter Gwiazda Mar 24 '10 at 12:11 Snowflake can help reduce the redundancy in the dimension-tables, but if that helps you performance- or memory-wise in your particular case I cannot say. – Björn Pollex Mar 24 '10 at 12:30 A date dimension with 10 years and hours is still small: 87,660 rows. Further, you can summarize old data to reduce the time resolution.

After 10 years, how much relevance can 10AM on Thursday actually have? – S. Lott Mar 24 '10 at 20:45.

I would recommend having seperate dimension for date and time. Date Dimension would have 1 record for each date as part of identified valid range of dates. For example: 01/01/1980 to 12/31/2025.

And a seperate dimension for time having 86400 records with each second having a record identified by the time key. In the fact records, where you need date and time both, add both keys having references to these conformed dimensions.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.