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

Dimensional Design Patterns for Time and Location
209
Period Calendars
A day granularity calendar is not the only calendar you will need. Periodic snap-
shots and aggregate fact tables hold weekly, monthly, or quarterly facts and will
require rollup (RU) calendar dimensions. Theoretically you could attach the
CALENDAR dimension to these higher granularity fact tables by using the last
date of the period that the facts represent; for example, a monthly sales snapshot
could join to the CALENDAR using the last day of the month—the date on which
the snapshot was taken. However, this is not a good idea, because it does not
explicitly document the time granularity of the facts, and could lead BI users to
incorrectly believe they can analyze the monthly sales facts using any calendar
attribute, including day-level attributes like DAY OF WEEK or WORKDAY flag.
Do not use the
standard day
CALENDAR
dimension with
higher level periodic
snapshots and
aggregate fact
tables
Month Dimensions
Instead of using a day calendar with monthly fact tables you should create a
MONTH rollup dimension similar to the one shown in Figure 7-3, and define
monthly fact tables using MONTH KEY foreign keys. This makes the granularity
of these fact tables explicit, and limits queries to using only the valid monthly
attributes. You can build the MONTH dimension from the CALENDAR dimen-
sion using a materialized view. MONTH KEY can be created using the DATE KEY
for the last day of each month: the MAX(DATE_KEY) if the materialized view
groups by month.
Monthly fact tables
should use a
MONTH dimension
to make their time
granularity explicit
Even though CALENDAR and MONTH dimensions have different time granularities
they are still conformed dimensions because they use common attribute values:
they are conformed at the attribute level.
Figure 7-3
Period calendars
Some BI tools find it difficult to cope with separate day and month calendar tables
and prefer all common date dimension attributes to be defined using a single
table. If this is the case, having a MONTH KEY that matches the last day of the
month DATE KEY can be useful. In that way, BI tools that need to, can use the
CALENDAR dimension instead of MONTH at query time.