Slowly Changing Dimension
Dimentions that change over time rather than changing on a regular schedule.
Types of Slowly Changing Dimensions
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.
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.
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.
SCD Type 2 or Effective date range Mapping
SCD Type 3 - Dimension Mapping
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.
 | |
|
Keeps the current and partial historical data by adding new column PM_PREV_COL_NAME column to the table.
 |
