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

Dimensional Design Patterns for Time and Location
205
dimension will exist, BI tools should present the two roles as separate dimensions
using views that rename each time attribute based on its role. For example,
ORDER TIME, ORDER DATE, ORDER MONTH and DELIVERY TIME,
DELIVERY DATE and DELIVERY MONTH.
Figure 7-1
CALENDAR and
CLOCK dimensions
used to play two
roles with ORDER
FACT
The ORDERS FACT table in Figure 7-1 documents Delivery Time (duration) as a
fact. This is the elapsed time in hours taken to fulfill the order. This duration
would be difficult to calculate and aggregate using the separate time dimensions
alone, and is best stored as a fact.
Calendar Dimensions
Calendar dimensions should support all the groupings of day, week, month,
quarter, year, fiscal period, and season that are needed as report row headings and
query filters; for example, CALENDAR in Figure 7-1 contains the commonly used
calendar attributes: DAY (Sunday–Saturday), DAY IN WEEK (1–7), MONTH
(January–December), MONTH IN YEAR (1–12), and YEAR. It also contains
several “Overall” attributes such as DAY OVERALL and MONTH OVERALL.
These are epoch counters that increment for each new Day, Week or Month from
the earliest date in the data warehouse (the epoch date). Overall values are used for
calculating interval constraints that can cross year boundaries, such as: “last 60
days” or “last 4 weeks”. The BEAM ✲ excerpt of Pomegranate’s CALENDAR, in
Figure 7-2, shows that the company has 13 fiscal periods per year, and that its fiscal
year runs February to January—not January to December. The full dimension
would make all of Pomegranate’s calendar information available for reporting, so
that BI users do not have to decode any dates or remember which fiscal periods
contain 29 days rather than 28 or even the name of the current period.
A good calendar
dimension should
include all the date-
related attributes
that stakeholders
need. Ideally BI
tools should never
have to decode a
date to provide a
good report row
header or filter