Databases Reference
In-Depth Information
their current properties or attributes. That approach simultaneously sup-
ports decisions made in the context of the current state of the enterprise
while conserving the resources of the data warehouse.
type 2—All history in Its own context
Type 2 Time Variant data is the form of Time Variance discussed previ-
ously in this chapter. Every transaction is presented in its historic context.
A restaurant meal is presented with the restaurant manager at the time
of the meal. A retail transaction is presented with the product placement
at the time of the transaction. In that way, every transaction is presented
in the context in which it occurred.
type 3—Alternate history
Type 3 Time Variant data presents enterprise transactions with an alter-
nate history. The most common application of Type 3 Time Variant data is
to reverse an enterprise reorganization. For example, if a region of stores
originally had five thousand stores, and then two thousand of them were
split off into a new region, Type 3 Time Variant data could bring those two
thousand stores back to their original region. The reason for doing this is
to present that region, throughout its history, in a comparable (i.e., apples
to apples) configuration. In that way, typically Type 3 Time Variant data is
used very sparingly and only for the purpose of presenting the enterprise
as though the configuration or alignment of the enterprise looks exactly
as it did at a moment in the past. This should not be confused with Type 1
Time Variant data, which makes the enterprise history look like the pres-
ent. Rather, Type 3 Time Variant data makes the enterprise present look
like the past (Kimball 1998, 2005).
Kimball envisioned this being accomplished by adding a column to a
dimension table. The additional column would hold the value from a prior
period, right next to the Type 1 or Type 2 value in that same dimension
table row. That way, a query could leverage the dimensional data struc-
tures without modification by referencing the additional column (e.g.,
pre_reorg_district, conference_2003) rather than the standard column
(e.g., district, conference). By referencing a Type 3 column a query can cast
a dimensional structure as a date in the past. Obviously, a new column
cannot be added to a dimension table every time a dimensional change
occurs. Rather, a Type 3 column is added to a dimension table only when
