Slowly changing dimensions (SCD) with Example

In Data warehouse, the data remains persistent, but the dimensions evolve slowly over the period.For example, customers change their names, relocate, have children, change their Incomes. These dimensions are known as slowly changing dimensions.

To capture changes these changes, a combination of strategies are evolved. These strategies are usually known as SCD types.

The Strong Knowledge of Slowly changing dimensions in a data warehouse is the key to success in the BI domain.

Why Slowly changing dimensions (SCD)?

To report data historically, these changes in the attribute of dimensions are captured. This help us to track the events.

In a normal scenario of the employee database, The fact table and dimension table would be linked by way of foreign keys. One dimension table may contain the office location of the employees.The employees may get transferred to other location over the period. To prepare a report, it may require tracking the location history of the employee.

To handle this issue some methodologies or strategies are adopted, which are referred as SCD type 0 to 6.

SCD Type 0: No Change

This type is rarely used. It maintains the dimensional changes as they were initially at the time the record is added, so No action is required.

SCD Type 1: Replaces the Previous record

In this strategy, the New record replaces the previous record and finally no historical data is maintained.

For example: Once the location of the employee is updated we would have no records of his previous locations.

SCD Type 2: Maintain Additional Record

In this strategy, the history is maintained with the additional records in the dimension table with two columns  Active_Flag and Expired_date. The active_flag  column determines the latest records and Expired_date column determines the period till the records were active.

Emp_ID DEPT Manager Active_flag Effective_Date Current_location Expired_date
12345 IT John Mathew False 22-Dec-2004 London  22-Dec-2003
12345 IT John Mathew True 22-Dec-2003 Germany NULL

SCD Type 3: Attribute as Column

This method maintains the history by means of additional columns for holding the value for the previous events. History is limited to columns defined to hold historical data.

Emp_ID DEPT Manager Previous_location Effective_Date Current_location
12345 IT John Mathew New Delhi 22-Dec-2004 London

In the above example, the Location history is maintained using extra column Previous_location.

SCD Type 4: History table

In This method, Dimension table holds the current data, and the previous history is maintained with history table. Keys in both tables are linked with Fact table to improve the result.

Main Dimension table with current records

Emp_ID DEPT Manager  Current_location
12345 IT John Mathew London

History table: Dimension table with previous records:

Emp_ID DEPT Manager  Effective_Date Current_location
12345 IT John Mathew 22-Dec-2004 London

Share this post

Leave a Reply