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

210
Chapter 7
When and Where
Offset Calendars
An offset calendar
dates facts from a
fact-specific origin
date; e.g. policy
facts are dated from
a policy start date
Events such as insurance claims or policy payments can benefit from having their
own specialized calendar dimension in addition to the standard calendar. A
POLICY MONTH dimension like the one shown in Figure 7-3 would be used to
offset the facts from the creation date or last renewal date of the policy rather than
January 1 or the first day of the financial year as the normal calendar dimension
would. For example, if a policy renews on April 1, an August claim fact for that
policy would be labeled as MONTH “August” or MONTH NUMBER 8 by
CALENDAR but POLICY MONTH 4 by the POLICY CALENDAR.
An offset calendar, like POLICY MONTH, can be use in conjunction with a stan-
dard MONTH dimension to define a MONTHLY POLICY SNAPSHOT with a granu-
larity of POLICY by MONTH by POLICY MONTH. This fact table will contain exactly
twice as many rows as a standard monthly snapshot
will allow the facts to
be queried by either calendar or policy month or a combination of both.
Year-to-Date Comparisons
Problem/Requirement
To perform year-to-date (YTD) comparisons—such as YTD Sales 2011 versus
YTD Sales 2010—the following needs to be known about the date range:
What is the “year to
date” date for valid
comparisons with
The “from date” when the year began. This seems obvious but are we talking
about the beginning of the calendar year, or the organization’s fiscal year or
the tax year?
previous years
The “to date.” Are you running the YTD calculation up to now or some
specific date in the past. If you are defaulting to “up to now”, what does “now”
mean? Do you have complete data loaded right up to today or yesterday?
Which days to include. Should YTD figures from previous years include facts
up to the same “to date” in those years, or the same number of days (this copes
with the extra day caused by the February 29 in leap years)? If it is based on
the number of days, is that calendar days or workdays (for example, the same
number of weekdays excluding public holidays)?
CALENDAR
dimensions support
YTD comparisons
The CALENDAR dimension can support consistent year-to-date (YTD) calcula-
tions by providing conformed definitions for the beginning of each year (calendar
and fiscal) and which workdays to include. The attributes needed to do this are:
by providing
conformed
definitions of
workday and
fiscal year
DAY (NUMBER) IN YEAR
DAY (NUMBER) IN FISCAL YEAR
WORKDAY IN YEAR
WORKDAY IN FISCAL YEAR
WORKDAY FLAG