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

218
Chapter 7
When and Where
Multinational Calendar Pattern
Problem/Requirement
Holiday and season
descriptions are
geopolitical time
attributes that vary
by location as well
as date
For a single-country data warehouse, adding holiday schedules and season desc-
riptions to the calendar dimension is relatively straightforward. But when a data
warehouse goes global, these attributes become problematic, because holidays and
seasons are location-specific or geopolitical time attributes that vary by location, just
as time zones do. If the number of countries to be covered is small—and will
remain that way—then their holiday variations can be handled dimensionally by a
small repeating group of attributes; for example, if a company operates only in the
UK, a single SEASON and the following holiday attributes may be sufficient:
ENGLISH HOLIDAY FLAG
WELSH HOLIDAY FLAG
NORTHERN IRISH HOLIDAY FLAG
SCOTTISH HOLIDAY FLAG
A national calendar
table holds
geopolitical time
attributes keyed on
a combination of
date key and
country which can
lead to over-
counting
However, if the data warehouse is expected to cover more than a few countries, you
will need a more robust solution. NATIONAL CALENDAR in Figure 7-8 attempts
to solve the geopolitical attribute problem by using a composite key of date and
country to record holiday information for each date and country combination as
separate rows. Unfortunately, this design demands that BI users and developers
remember to constrain NATIONAL CALENDAR to a single country when query-
ing the facts, otherwise their answers will be overstated by the number of countries
they “let into” the query. For example, if NATIONAL CALENDAR holds holiday
information for ten countries and a busy sales manager forgets to correctly con-
strain the calendar, an ad-hoc analysis of holiday sales revenue will be overstated
ten times. The figures would be wrong even if the query filters sales to just one
branch for one holiday, because even a single sales transaction on that date will be
joined to, and over-counted by, the multiple countries that observe that holiday.
Commissioned sales staff may be happy by this oversight—few other BI stake-
holders will be so enthusiastic.
NATIONAL CALENDAR, in Figure 7-8, is a multi-valued dimension. It contains
multiple date values for each fact. If not used carefully it has the potential to over-
count the facts. Chapter 9 covers multi-valued dimensions in detail.
Country-specific
calendar views are
safer to use but they
limit analysis to one
country at a time.
They are not a good
match for
international facts
A safer solution for ad-hoc queries is to provide country-specific calendar views
that pre-join CALENDAR to NATIONAL CALENDAR constrained to a single
country. BI users can then choose (or be defaulted to) the most appropriate calen-
dar view. Unfortunately, this solution limits analysis to one country at a time, and
even then, BI users must still take care to constrain the geography of their queries
to precisely match their chosen calendar, otherwise the geopolitical time attributes
they use will not actually match the facts. Country-specific calendar dimensions are
an international data warehousing anti-pattern: they do not match international
fact tables.