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

214
Chapter 7
When and Where
Conformed Date Ranges
FACT STATE
information helps
define conformed
date ranges for
comparing business
processes using
multiple star
schemas
In addition to defining meaningful date ranges for YTD comparisons on a single
fact table, a FACT STATE table can help define the set of sensible comparisons that
can be performed across fact tables. For example, you can't meaningfully compare
current YTD sales with YTD commissions based simply on current year totals if
SALES FACT contains data up until the end of May and COMMISSION FACT
only contains data up until the end of April. However, the two processes can be
compared on a "year to end of April" basis. You derive this conformed date range
from FACT STATE using the earliest LAST COMPLETE DATE among all the fact
tables involved in the analysis.
Clock Dimensions
A clock dimension
contains time of day
descriptions,
typically at the
minute granularity
Time down to the
second is best
treated as a fact
A clock dimension contains useful time of day descriptions, such as Hour of Day,
Work Shift, Day Period (Morning, Afternoon, Evening, Night), Peak and Off-Peak
periods. Its granularity is typically one row per minute, half hour, or hour of the
day—whatever level of detail is needed to provide the row headers and filters that
BI users need. Figure 7-6 shows a typical CLOCK dimension with minute granu-
larity. It contains 1,440 rows—one for each minute in a day—plus a zero Time key
row for unknown or not applicable time of day. You should avoid defining clock
dimensions with a granularity of one row per second unless there really are useful
rollups of less than a minute. For most business processes, time at the precision of
a second or less is not useful as a dimension (as a report row header or filter), but it
may be useful as a fact for calculating exact durations. Storing precise timestamps
as facts allows the time dimensions to remain small and concentrate on being good
dimensions—sources of good descriptions for report row headers and filters.
Figure 7-6
CLOCK dimension
CLOCK in Figure 7-6 is an HV dimension because work shifts and peak time can
change but their historical names and times must still be used to describe historic
facts. A standard CALENDAR is an FV dimension because date descriptions are
fixed and do not change. A fact-specific calendar is a CV dimension because it
must contain the current ETL status dates for its specific fact table.