Slowly Changing Dimensions
Slowly Changing Dimensions are dimensions that change slowly over time, rather than changing on regular schedule, time-base. In Data Warehouse there is a need to track changes in dimension attributes in order to report historical data. In other words, implementing one of the SCD types should enable users assigning proper dimension's attribute value for given date. Example of such dimensions could be: customer, geography, employee.
There are many approaches how to deal with SCD. The most popular are:
Type 0 - The passive method
Type 1 - Overwriting the old value
Type 2 - Creating a new additional record
Type 3 - Adding a new column
Type 1 - Overwriting the old value. In this method no history of dimension changes is kept in the database. The old dimension value is simply overwritten be the new one. This type is easy to maintain and is often use for data which changes are caused by processing corrections(e.g. removal special characters, correcting spelling errors).
Before the change:
After the change:
Type 2 - Creating a new additional record. In this methodology all history of dimension changes is kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and new rows contain as attributes the natural key(or other durable identifier). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e. start_date and end_date, the end_date for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added in the future.
Before the change:
After the change:
Type 3 - Adding a new column. In this type usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current/new' column and the old one into 'old/previous' column. Generally speaking the history is limited to the number of column created for storing historical data. This is the least commonly needed techinque.
Before the change:
After the change:
There are many approaches how to deal with SCD. The most popular are:
Type 0 - The passive method. In this method no special action is performed upon dimensional changes. Some dimension data can remain the same as it was first time inserted, others may be overwritten.
Type 1 - Overwriting the old value. In this method no history of dimension changes is kept in the database. The old dimension value is simply overwritten be the new one. This type is easy to maintain and is often use for data which changes are caused by processing corrections(e.g. removal special characters, correcting spelling errors).
Before the change:
Customer_ID | Customer_Name | Customer_Type |
---|---|---|
1 | Cust_1 | Corporate |
After the change:
Customer_ID | Customer_Name | Customer_Type |
---|---|---|
1 | Cust_1 | Retail |
Type 2 - Creating a new additional record. In this methodology all history of dimension changes is kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and new rows contain as attributes the natural key(or other durable identifier). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e. start_date and end_date, the end_date for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added in the future.
Before the change:
Customer_ID | Customer_Name | Customer_Type | Start_Date | End_Date | Current_Flag |
---|---|---|---|---|---|
1 | Cust_1 | Corporate | 22-07-2010 | 31-12-9999 | Y |
After the change:
Customer_ID | Customer_Name | Customer_Type | Start_Date | End_Date | Current_Flag |
---|---|---|---|---|---|
1 | Cust_1 | Corporate | 22-07-2010 | 17-05-2012 | N |
2 | Cust_1 | Retail | 18-05-2012 | 31-12-9999 | Y |
Type 3 - Adding a new column. In this type usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current/new' column and the old one into 'old/previous' column. Generally speaking the history is limited to the number of column created for storing historical data. This is the least commonly needed techinque.
Before the change:
Customer_ID | Customer_Name | Current_Type | Previous_Type |
---|---|---|---|
1 | Cust_1 | Corporate | Corporate |
After the change:
Customer_ID | Customer_Name | Current_Type | Previous_Type |
---|---|---|---|
1 | Cust_1 | Retail | Corporate |
Comments
Post a Comment