Just the sound of the phrase “slowly changing dimension“ can sound quite intimidating when uttered in a technical meeting, but you might be shocked at how simple this concept really is. In fact, it has nothing to do with shifts or alterations in the universe at all. J

If you are involved in data warehousing you should have a firm grasp of SCDs as they will be necessary to structure your data in a meaningful way that takes advantage of the capabilities of a data warehouse; specifically when you wish to store historical data rather than simply overwriting a record. So, let’s break it down:

Slowly Changing Dimensions: Dimensions in your DW that include attributes that will gradually change over a period of time.

There are three types:

Type I – Overwrite old values: The old record is completely over written by the new record. Type I is used when you have no desire to store the historical attributes of the record.

Type II – Create an additional record: The old record will be kept and a new record will be inserted. Type II is used when you wish to store the historical attributes of the record.

Type III – Create new fields: This type creates new fields in the record to maintain the history and the current values of the specific attributes in the record.

Below are examples of how to use each type. In this example the price of a product is changing.

Type I: The old values are over written

Product price in 2011

Product price in 2012

Type II: A new record is created.

Product price in 2011

Product price in 2012

As you can see in the above example there are a few problems with the structure of the table. When using a type II SCD you will need to add a couple of basic fields to effectively track the historical records. There are several possibilities, but the idea is to be able to differentiate between the two records. Also you may notice that there are two identical IDs for this record. That is OK. This is still the same product so a common identifier is necessary. We will be able to narrow down our selection criteria by choosing the active record. A more realistic type II record would look like the below.

Product price in 2012

Type III: New fields are inserted

Product price in 2011

Product price in 2012

There are many scenarios in business where each of these SCD types will be useful. The most common are types I and II because type III can only store a limited amount of historical data.

I hope that this explanation has helped to simplify your understanding of how to use slowly changing dimensions in data warehousing.