Would it be better to create a time dimension with hh:mm:ss altogether or would it be better to split them up into 3 dimensions since separately they will take up less space?
What would you recommend and why?
3 Answers
Why not a Date Dimension ? The Date Dimension is a key dimension in data warehousing as it allows to analyze data in different aspects of date. Apart from the standard date attributes like year, quarter, month, day , hour, min ..., the date dimension can be extended to richer analysis
Working with star schema model is a best practice. Date dimension is called a conformed dimension.
Spliting up in 3 dimension = more joins = complex queries.
Why not create a time dimension with the following (and maybe more) columns on the dimension table?
- HH
- MM
- SS
- AM PM Indicator
- HH:MM:SS
- Nickname (e.g. 'noon')
- Daypart (e.g. 'morning')
- HH Military (here you might put 14 for 2 pm)
The key could be interesting here. Perhaps it's a sequence but, alternatively, you could use HHMMSS as the key (with leading zeros). I don't think performance will differ significantly.
What I ended up doing was just creating a separate time dimension with a row for each possible combination of hour, minute, and second.
I did this vs creating 3 separate tables for hours, minutes, and seconds each to keep the number of joins down and not make the database too clustered with tables.
I kept it separate from my date table to keep the number of rows down and making performance slower.
Seems to work pretty well, thanks for the feedback everyone.
Related