Dates and times comment

A few days ago, Farooq posted some comments on an old post of mine. The final comment in our exchange concerned storing activities in our data warehouse by time (actual hour and minute). This is quite a common requirement, especially where the performance of customer service agents is being measured (as in Farooq's question)

I would be tempted to split the event date and time into two separate dimensions a 'date' dimension with a granularity of day and a 'time' dimension that has sufficient resolution to capture the events of interest; there is no date component in the time dimension, for example events that occurred at 09:18:00 on May 6 2008 will share the same time dimension key as events that occurred at 09:18:00 on February 5 2008. This approach keeps the time dimension compact, we only need to key the possible time values that occur in a single day, and more importantly it opens up the possibility of simple time range queries aggregating over many days.