Database Reference
In-Depth Information
Notice I used a physical dimensional modeling technique called a degenerate dimension in
this example. A degenerate dimension is when you remove the dimensional structure and
just store the dimensional attribute directly in the fact table. Most of the time, the degener-
ate dimension contains just a single field—in this case, an indicator. So we removed those
dimensions such as
University
that contained only a single field.
STEP 2: ACCOMMODATE HISTORY
There are four options in addressing how field values change over time. Each option for
handling these changes is given a different number (either zero, one, two, or three) and is
often called a Slowly Changing Dimension (SCD for short). An SCD of Type 0 means we
are only storing the original state and not storing changes. That is, a collection of Type 0
means that if the data changes, we just store the way the data was originally and do not
apply any changes to it. A Type 1 means the most current view. With a Type 1 we are just
storing the most current state of the collection by applying the latest changes. A Type 2
means we are storing all changes to the data in a collection—a complete history whenever
anything changes. Type 2 is also known as “auditing” because we are storing all changes
and therefore have the ability to analyze the way the world existed at any point in time.
Type 2 is like having a time machine. Type 3 means we have a requirement for some his-
tory—for example, the most current view and the previous view or the most current view
and the original view.
We need to determine the type of history needed for each collection by completing the Col-
lection History Template:
Collection History Template
Store only the original state
(Type 0)
Store only the most current state
(Type 1)
Store full history
(Type 2)
Store some history
(Type 3)
