Databases Reference
In-Depth Information
their properties or attributes as of March 2, 2011; that result set will be
combined with the transactions on March 3, 2011, which will be joined
to objects and their properties or attributes as of March 3, 2011; and so
on, until finally that result set will be combined with the transactions on
March 31, 2011, which will be joined to objects and their properties or
attributes as of March 31, 2011. The collective result set from those time
variant result sets will represent activity within the enterprise during the
month of March 2011.
Regardless of the groupings of times and dates, Time Variance still
occurs at the lowest level, which is the moment in time. That moment in
time, of course, is the moment the transaction occurred and the objects
and their properties or attributes that were in effect as of that moment in
time. So, we can gather the result sets of all those time variant joins and
hierarchically group them into a calendar week, calendar month, calendar
quarter, calendar year, day of week, and holiday/non-holiday. All of that
can typically be found in any data warehouse. Why? Well, conveniently
the calendar and hierarchical groupings of the calendar have been stable
for centuries. We have gone a very long time without reordering or renam-
ing the days of the week, days of the month, months of the year, or the
number of days in a year. It would be quite feasible to populate a full set
of calendar tables for the next five hundred years. Even though we can do
that, we typically don't. The reasons we don't populate the calendar tables
far into the future are the Left Outer Join and Transaction Summaries.
• Invariably, a d ata warehouse user will want to see dates when an
event does and does not occur. That data warehouse customer will
begin by querying a calendar table of dates and then use a Left Outer
Join to join a fact table of transactions. The Left Outer Join will cause
the result set to include all dates, even when there are no transac-
tions on those dates. In that case, the transaction columns will sim-
ply be null, and the result set will extend out for five hundred years
of null transactions. Rather than allow that to happen, most data
warehouses simply load a much more reasonable span of future dates
and date hierarchies on a periodic basis.
• Transaction Summaries are also adversely afected by the availability
of five hundred years of dates for which there are no corresponding
transactions. Typically, enterprise transactions are summarized by a
hierarchical grouping of time (e.g., Day Summary, Week Summary,
Quarter Summary). Rather than allow the summary process to
