Chapter 2: Dimension History

The data values stored in a dimension table are called dimensions. The product_dim table in Chapter 1, for example, contains product dimensions.

Most dimensions change over time. Customers change addresses, products get renamed and recategorized, sales orders get corrected, etc. When a dimension changes, such as when a product gets a new category, you must maintain the dimension history. In the event of a product being recategorized, for example, you must maintain the product dimension history. In this case, you must store the product’s old category as well as the product’s current category in the product_dim table. In addition, product category information in old sales orders keep referencing the old category.

Slowly Changing Dimension (SCD) is the technique for implementing dimension history in a dimensional data warehouse. This chapter teaches you how to maintain dimension history using SCD. You will also learn various SCD scripts and test them to prove that they maintain the dimension history correctly.

Slowly Changing Dimension Techniques

There are three variants of the SCD technique: SCD Type 1 (SCD1), SCD Type 2 (SCD2), and SCD Type 3 (SCD3). SCD1 updates dimension records by overwriting the existing data-no history of the records is maintained. SCD1 is normally used to directly rectify incorrect data.

SCD2 maintains dimension history by creating newer ‘versions’ of a dimension record whenever its source changes. SCD2 does not delete or modify existing data.

SCD3 keeps one version of a dimension record. It keeps history by allocating more than one column for a data unit to maintain its history. For instance, to maintain customer addresses, a customer_dim table has a customer_address column and a previous_customer_address column. SCD3 effectively maintains limited history, as opposed to SCD2’s full history. SCD3 is rarely used. It is only used in situations where you have a database space constraint and your data warehouse users can live with limited dimension history.

Note 

This book covers SCD1 and SCD2.