Slowly Changing Dimension

Slowly Changing Dimension

Dimentions that change over time rather than changing on a regular schedule.

Types of Slowly Changing Dimensions

• SCD Type 1
• SCD Type 2
• SCD Type 3

SCD Type 1

Keeps only the current data and does not maintain the historical data.

i.e., Overwriting the old/existing value.

Example :

Suppose the employee changes his location. i,e from India to USA and then he gets shifted to Germany.

EMPLOYEE STAGING

EMPLOYEE DIMENSION

Employee Dimension

ADV:  Do not consume a lot of space to maintain data
DISADV:  It does not maintain historical data.

SCD Type 2 or Version Number Mapping

Keeps the current data and historical data as well.

It creates a new column PM_VERSION_NUMBER in the table to track the changes and PM_PRIMARY_KEY in the table to maintain the history.

EMPLOYEE DIMENSION- VERSION MAPPING

SCD Type 2 or Flag Mapping

Keeps the current data and historical data as well.

It creates a new column PM_CURRENT_FLAG in the table to track the changes and PM_PRIMARY_KEY in the table to maintain the history.

EMPLOYEE DIMENSION - FLAG MAPPING

SCD Type 2 or Effective date range Mapping

Keeps the current data and historical data as well.

It creates PM_BEGIN_DATE and PM_END_DATE to maintain the date range in the table to track the changes and

PM_PRIMARY_KEY in the table to maintain the history.

EMPLOYEE DIMENSION - EFFECTIVE DATE RANGE MAPPING

SCD Type 3 - Dimension Mapping

Keeps the current and partial historical data by adding new column PM_PREV_COL_NAME column to the table.