Slowly Changing Dimensions

Summary: in this article, you will learn about slowly changing dimensions type 1, type 2 and type 3 and corresponding techniques to deal with each of them.

In dimensional modeling, it is very important to determine how the change of data in the source system reflects in dimension tables in the data warehouse system. This phenomenon is known as slowly changing dimensions. This term comes from the reason that dimensions accumulate their changes at the slow rate in comparison with facts in the fact table.

There are three most common slowly change dimension types which are known as slowly changing dimension type 1, type 2 and type 3. Let’s examine each type and technique to handle the situation in a greater detail.

Slowly changing dimensions type 1

In slowly changing dimensions of type 1, when data in the source system changed, the corresponding dimension attribute is overwritten. As the result, the dimension table does not represent the history and historical context of existing facts is changed.

Slowly changing dimensions type 1 should be avoided as much as possible. It should be used only in case there is a need for correcting data in the source systems that will reflect in dimension table in data warehouse system.

Slowly changing dimensions type 1 example

Let’s take a look at an example of slowly changing dimensions type 1 to get a better understanding.

In our sample star schema, we take DIM_PRODUCT dimension table as an example. Below is the sample data of DIM_PRODUCT dimension table.

Slowly Changing Dimensions Type 1 Example

Figure 1. Slowly Changing Dimensions Type 1 Example

For some reasons, the product called Nikon Coolpix has a typo in the source system and the incorrect value was loaded in the dimension table in data warehouse system.  When we apply slowly changing dimension type 1, the value of source system changed and value in data warehouse system get overwritten.

Slowly changing dimensions type 2

In slowly changing dimension type 2, when data in the source system changed, a new version of the corresponding dimension row is created to preserve a version history and historical context of the facts. It is important to note that whenever a change is made, a new record is inserted to preserve history, therefore, we can preserve unlimited version history with slowly changing dimension type 2.

In the dimensional design, if you cannot decide what type of slowly changing dimension that applies to a particular dimension, the type 2 is the safest way to choose because when data in the source system changed, the dimension can use either type 1 or 2.

Slowly changing dimensions type 2 example

In figure 2 below, for example, if the product category of the product id 4 changed into Electronics. Instead of overwriting the product category, we create a new record with a single attribute that has the new value. As the result, we have two records: one is to preserve the history and one is for the current analysis.

Slowly Changing Dimensions Type 2 Example

Figure 2. Slowly Changing Dimensions Type 2 Example

Slowly changing dimensions type 3

In dimensional design, sometimes you will have requirements that allow business analysts to analyze facts recorded before and after the change occurred using the old value and new value. None of both types above is suitable to address these requirements. In this case, type 3 is used.

In the slowly changing dimension type 3, a pair of attributes is created to track current value and previous values. Whenever a change occurs, both attributes get updated, no new row is added.  It is noticed that type 3 only keeps tracking the current value and the most recent values, not all historical values like type 2.

Slowly changing dimensions type 3 example

In the product dimension table, we created a pair of attributes called CAT_CURRENT and CAT_PREVIOUS to track the changes. As shown in figure 3 below, the product category of product Id 4 is kept track.

Slowly Changing Dimensions Type 3 Example

Figure 3. Slowly Changing Dimensions Type 3 Example

Slowly changing dimension summary

The following table demonstrates the action of each slowly changing dimension type and its effects on facts.

Slowly changing dimensionsActionEffects on facts
Type 1Overwrite attribute value in dimension tableRestate history
Type 2Insert new row in dimension tablePreserves unlimited history
Type 3Create a pair of attributes to keep previous and current values. No new rows are added.Ability to analyze facts recorded before and after change happened using old value and new value.

In this article, you’ve learned about the most common slowly changing dimensions and corresponding techniques to deal with them in dimensional design.

  • Was this tutorial helpful ?
  • YesNo