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

Dimensional Design Patterns for Time and Location
219
Solution
To overcome the “one country at a time” query limitation and prevent calendar
and fact mismatch you need a different calendar design that truly matches multi-
national fact tables. MULTINATIONAL CALENDAR in Figure 7-9 looks re-
markably like a standard calendar dimension, but it handles date descriptions that
vary geographically by storing multiple versions of the dates that have varying
descriptions, each with a unique DATE KEY; for example, Figure 7-9 shows the
three versions of March 17, 2010 needed to support the different combinations of
SEASON and HOLIDAY in the UK, U.S., South Africa, and Ireland on that date.
Figure 7-9
Multinational
calendar dimension
showing 3 versions
of March 17 th 2011
But how do these multiple versions of a date behave in fact queries? The answer is
“just like a single version of the date” when you ignore multinational attributes.
For example, all sales for March 17, 2011 will roll up to a single line on a report if
they are grouped solely on CALENDAR_DATE. Only if sales are grouped by
SEASON or HOLIDAY (attributes that vary internationally) will the report contain
any additional lines, which is exactly what you want. In this way, the multinational
calendar is similar to an HV employee dimension that uses surrogate key values to
represent historical versions of an employee, except here the surrogate keys repre-
sents geopolitical versions of a date. A multinational
The benefit of the multinational calendar is that it keeps both the model and
queries simple while handling the complexity of the geopolitical attributes. BI users
are totally unaware of the multiple versions of a date, they do not have to think
about which national calendar to use, their queries can cross national boundaries,
and they can use whatever calendar attributes interest them. With a multinational
Consequences
BI user interfaces that provide date lists driven from a multinational calendar must
do a Select Distinct. But that should be the default for all value lists anyway!
ETL fact loading processes must know how to assign the correct DATE KEYs
based on the when and where details of business events. You also need to think
carefully about how ETL processes create DATE KEYs for multiple versions of a
day, in the first place.
calendar uses a
date key that
represents a
geopolitical version
of a date to match
multinational facts
calendar, simple
queries can safely
cross national
boundaries