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.