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

212
Chapter 7
A FACT STATE
table contains all
the necessary YTD
information but it
can be difficult to
use for BI queries
When and Where
To use FACT STATE information, you add FACT STATE to your fact table query
and filter it on the fact table name you are using. You can then use any of its
attributes in place of a SYSDATE-based calculation. Unfortunately, because the
FACT STATE table is not “properly” joined to any other table in the query, many
BI tools complain about a possible Cartesian product. Even if your BI tool doesn’t
complain, using FACT STATE in this manner can be confusing for both BI users
and developers, not to mention dangerous—if it is not properly constrained to the
correct fact table. To overcome this issue, you can provide the FACT STATE
information as part of a fact-specific calendar dimension.
Fact-Specific Calendar Pattern
FACT STATE
information can be
repackaged in easy
to use fact-specific
calendars
A fact-specific calendar is built by merging the dynamic FACT STATE row for a
fact table with the static rows of the standard CALENDAR dimension. This creates
a version of the calendar that is “aware” of the YTD status of the facts that it is
designed to work with. Figure 7-5 shows an example fact-specific calendar SALE
DATE, built by joining the one row in FACT STATE ( where FACT_TABLE =
“SALES_FACT” ) to every row in CALENDAR.
Figure 7-5
SALE DATE:
a fact-specific
calendar
with added
FACT STATE
information
A fact-specific
calendar makes
ETL load dates as
easy to use as
SYSDATE
At first sight, it seems wrong or at the very the least wasteful, to repeat the same
FACT STATE information on every row in the new calendar, but remember this
calendar is still tiny by fact table standards and now it is simple to compare its
attributes to their equivalent FACT STATE attributes. Because the fact-specific
calendar will always be present in every meaningful query involving its specific fact
table, the MOST RECENT and LAST COMPLETE attributes can be used just as
easily as the DBMS system variable SYSDATE, without having to worry about
constraining FACT STATE on the right fact table or a BI tool (or developer)
complaining about a missing join. For example, to compare 2011 (the current year)
YTD sales with 2010, based on the most recent load date, a query would contain
the following simple SQL: