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

Dimensional Design Patterns for Time and Location
213
SELECT Year, SUM(Revenue)as Revenue_YTD
WHERE Year = 2010 or Year = 2011
AND Day_In_Year <= Most_Recent_Load_Day_in_Year
GROUP BY Year
To select the last three complete weeks of facts, the constraint would be:
WHERE Week_Overall BETWEEN (last_complete_week_overall - 3
AND last_complete_week_overall)
You should create a fact-specific calendar for each fact table that is used for YTD
comparisons, ideally as (materialized) views so that they will be updated automati-
cally whenever the FACT STATE table is updated. If a fact table has a single time
dimension, its fact-specific calendar can be given a unique role-specific name, such
as SALE DATE (shown in Figure 7-5). If a fact table has multiple date dimensions,
each one must use the same (more generically named) fact-specific calendar as its
role-playing (RP) time dimension. It is possible for all fact-specific calendars to
share the same conformed dimension name if each one is defined within a separate
fact-specific database schema (that also contains its matching fact table). The
naming approach you can adopt will depend on how your BI toolset qualifies
tables when accessing multiple star schemas simultaneously. Create a fact-
To help keep the SQL that builds fact-specific calendars simple, the YTD compari-
son attributes within CALENDAR should be mirrored in FACT STATE; for
example, if there is a QUARTER IN FISCAL YEAR attribute in CALENDAR there
should be a MOST RECENT LOAD QUARTER IN FISCAL YEAR and a LAST
COMPLETE QUARTER IN FISCAL YEAR in FACT STATE. FACT STATE
specific calendar
view for each fact
table used for YTD
analysis
attributes should
mirror calendar
attributes to keep
view building simple
You can expand fact-specific calendars to hold additional Y/N indicator flags—
such as MOST RECENT DAY, MOST RECENT MONTH, PRIOR DAY, and PRIOR
MONTH—that are based on the MOST RECENT LOAD DATE. Some BI tools may
also find it useful to have a MOST RECENT DAY LAG column that numbers every
date in the calendar relative to the MOST RECENT LOAD DATE; i.e., the most
recent date is 0, the previous day is –1, the following day is +1.
Using Fact State Information in Report Footers
The system date (SYSDATE) is often used in report headers or footers to provide
basic time context of a report. You can add FACT STATE information to produce
a more descriptive report footer, such as:
Report run on 23 rd March 2011. Report reflects data available up to
17th March 2011. The last complete week's data is for week 10,
data up to week 12 is included but is incomplete.
The bold values above are derived from SYSDATE, MOST RECENT LOAD
DATE, LAST COMPLETE WEEK IN YEAR, and MOST RECENT LOAD WEEK
IN YEAR, respectively. FACT STATE tables can be expanded to hold additional
audit and data quality information, such as whether the latest facts have been
signed off or not. This information, too, is handy stuff to print in a report footer.
FACT STATE
information can be
used to provide
descriptive report
footers that explain
the data available