My first Publication Agile-Data-Warehouse-Design-eBook | Page 235

Dimensional Design Patterns for Time and Location
215
Day Clock Pattern - Date and Time Relationships
Problem/Requirement
The standard attributes of time—such as Hour, Minute, AM/PM, and Minute in
Day—are independent of date; for example, 11:59 a.m. is always 11:59 a.m. no
matter what the date or day of the week is. This is why you model a clock dimen-
sion separately from a calendar dimension. But as you embellish the clock with
additional attributes—such as the peak/off peak and work shift name—you often
find that some of these time descriptions vary by date. For example, 11:59 a.m.
might be classified as “Work Time” on Friday March 27, 2010, and “Play Time” on
Saturday March 28, 2010. Does this mean that you have to recombine time and
create a dimension at a granularity of one minute for every day in the data ware-
house?
Certain time of day
descriptions vary
based on date
attributes
Solution
Thankfully not! Date and time don’t have to be combined to solve this problem.
Time of day descriptions, like work shift or peak/off peak, are seldom dependent
on the actual date (March 27 or March 28) but on the day type (weekday, weekend,
holiday, or unusual day.) You can handle this level of variation in the CLOCK
dimension by using the TIME KEY to represent versions of a minute. Figure 7-7
shows a DAY CLOCK dimension, with a granularity of one record per minute, per
day of the week, per day type. It holds 14 versions of each minute—one for each
day of the week, plus an additional version for each day of the week when it falls on
a holiday. This results in 20,160 rows in total. If CLOCK attributes vary only by
weekday, weekend, and holiday then you would just need three versions of each
minute, cutting the table down to 4,320 rows.
A DAY CLOCK
contains a version
of each minute for
each day type; e.g.
weekday, weekend
Figure 7-7
DAY CLOCK
dimension with
weekend and
holiday variations
Resist any temptation to combine CALENDAR and CLOCK dimensions into one.
The resultant dimension would be unnecessarily large and difficult to maintain,
having 525,600 records (365×1440) for each year at the granularity of minute.
Don’t even think about it down to the second.