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

206
Chapter 7
When and Where
Figure 7-2
Pomegranate
CALENDAR
dimension
(excerpt)
The Data Warehouse Toolkit, Second Edition, Ralph Kimball, Margy Ross (Wiley,
2002) pages 38–41 provide further examples of useful calendar attributes.
Date Keys
Date keys are
integer surrogates
but they should be
in calendar date
Calendar dimensions, like every other dimension, should be modeled with integer
surrogate keys. But unlike other surrogate keys, date keys should have a consistent
sequence that matches calendar date order. Sequential date keys have two enor-
mous benefits:
order
Date key ranges can be used to define the physical partitioning of
large fact tables. Chapter 8 discusses the benefits of doing this.
Date keys can be used just like a datetime in a SQL BETWEEN join
to constrain facts to a date range.
Historic value hierarchy maps (HV, HM) that use effective dating to track history
can use sequential date keys (EFFECTIVE DATE KEY and END DATE KEY) rather
than datetimes to improve efficiency when joining to fact tables. For example,
Where Reporting_Structure.Employee_Key = Sales_Fact.Employee_Key
and
Sales_Date is Between Reporting_Structure.Effective_Date_key and
Reporting_Structure.End_Date_key
This will join the historically correct version of the reporting structure HR hierar-
chy to the salary facts. Joins like this are complex (or theta) joins that are hard to
optimize and need all the help they can get.