Database Reference
In-Depth Information
Figure 4-21.
Adding a surrogate key
It is considered best practice to do this on all tables with the possible exception of the time dimension table,
as mentioned previously. The existence of surrogate keys helps when you are merging data into a data warehouse
from many different OTLP databases as well as when you are tracking any changes to dimensional values, such as
an author changing his or her name.
Slowly Changing Dimensions
Most of the time, dimensional values do not change over time; for example, it is unlikely that the names of the
months will change any time soon. Some data does change, however, such as people names. We use the term
slowly changing dimension
(SCD) to describe such data.
When changes like these occur, you may want to track them for reporting purposes. For example, if the
reports show sales connected with an old name, the author of those sales may not be given proper credit. It could
be that whoever is reviewing the report knows only the new name of that author and has no way of knowing the
previous name.
Slowly changing dimensions are certainly not a new concept. There have been several designs created over
the years to help handle these types of situations. These designs are categorized into named types. The most
common of these named types being used today are types I, II, and III.
Type I
The SCD type I, oddly enough, does not track changes at all. This is the normal state of a dimension table in which
no additional columns or rows have been added for the purpose of tracking changes. If an author changes his or
her name, simply record the new name and (using your best New York accent), forget about it. All reports now
show the new name, and old ones will not be corrected. With type I you are not tracking historical changes, and
perhaps that is preferred. The important thing here is that you made a conscious decision instead of letting it be
the default behavior of your dimensions.
Type II
SCD type II is the complete opposite of type I. Type II tracks all changes rather than no changes at all. To do this,
add additional columns and rows to the table specifically for the purpose of tracking the changes.
In Figure
4-22
, the Authors table has been modified to include SCD columns. The modifications in this case
included adding a column to indicate when a particular author's name was recorded using the start date column
and when it ended using the end day column. In this way, every time an author changes his or her name, it can
be recorded when the name change occurred and the length of time that it was applicable.
