In a dimensional database where updates are infrequent
(as opposed to OLTP systems), most dimensions are relatively constant
over time, because changes in sales districts or regions, or in company
names and addresses, occur infrequently. However, to make historical
comparisons, these changes must be handled when they do occur. The
following figure shows an example of a dimension that has changed.
Figure 1: A dimension that changes
You can use three ways to handle changes that occur in
a dimension:
Change the value stored in the dimension column
In the previous figure, the record for Bill Adams in the customer dimension
table is updated to show the new address Arlington Heights.
All of this customer's previous sales history is now associated
with the district of Arlington Heights instead of Des Plaines.
Create a second dimension record with the new value and a generalized
key
This approach effectively partitions history. The customer dimension
table would now contain two records for Bill Adams. The old record
with a key of 101 remains, and records in the fact table are still
associated with it. A new record is also added to the customer dimension
table for Bill Adams, with a new key that might consist of the old
key plus some version digits (101.01, for example). All subsequent
records that are added to the fact table for Bill Adams are associated
with this new key.
Add a new field in the customer dimension table for the
affected attribute and rename the old attribute
This approach is rarely used unless you need to track old history
in terms of the new value and vice-versa. The customer dimension
table gets a new attribute named current address, and the old
attribute is renamed original address. The record that contains
information about Bill Adams includes values for both the original
and current address.
