date/time dimension

Question

I am designing a data warehouse and I have a sticky issue with time. The grain I need is hourly (to calculate aggregate counts of events per hour) and I also have to accommodate a shift pattern that does not conveniently fit inside a 24 hour period (in fact it is possible that 'blue' shift wont cover the same time of day for several days).

With this in mind I am contemplating one of 3 approaches

1. a single time dimension with 175K rows in it.
2. a snowflake time dimension with 7300 rows in a calendar dimension and 175k rows in a time dimension
3. separate dimensions so that the fact table has foreign keys for the event date and for the event time.

I am tending towards approach 3 as it allows the small calendar dimension to be referenced separately in joins, but I would appreciate any thoughts.

Answer 1

Yes, manufacturing shifts are tricky and do change over time, often one shift starts day before, etc.

Keep in mind that there are two calendars here. One is the standard calendar and the other is the production calendar -- the shift belongs to the production calendar. In general, a day in production calendar may last more (or less) than 24 hours.

For example:

Part produced on Monday, 2011-02-07 23:45 may look like

TimeOfProduction = '2011-02-07 23:45'
DateKey = 20110207
TimeKey = 2345
ProductionDateKey = 20110208 (the first shift of the next day started at 22:00)
ProductionTimeKey = 145 (1 hour and 45 minutes of the current production date)     
ShiftKey = 1
ShiftTimeKey = 145 (1 hour and 45 minutes of the current shift)

So, my suggestion is:

1. Plain Date Dimension (one row per date)
2. Plain Time Dimension (one row per minute for 24 hours = 1440 rows + see note below)
3. Shift Dimension -- type 2 dimension with rw_ValidFrom, (rw_ValidTo) , rw_IsCurrent
4. Role-play the DateKey into ProductionDateKey
5. Role-play the TimeKey into a ProductionTimeKey and ShiftTimeKey.
6. Keep the TimeOfProduction (datetime) in the fact table too.
7. During the ETL process, apply the current shift logic to attach ProductionDateKey, ProductionTimeKey, ShiftKey, ShiftTimeKey to each row of the factPart table.

Note that you may need to add extra rows to the Time Dimension if a production day can last more than 24 hours. It usually can if a local time is used and there is a daylight savings time jump.

So, the star may look something like this