A dimension whose attribute values change over a period of time is called as a Slowly Changing Dimension; there are many ways of SCD implementation, depending on the business requirements. The following are most commonly used SCD implementation.
Type 1: Overwriting the old values. As the old value is overwritten by the new (current) value, there is no way to find out what the old (historical) value(s) were. In other words, it holds ONLY current value.
Ex: Any changes in the Residence Address of a Customer of a retail outlet can be a SCD Type 1, where the past address of the customer can be overwritten with the new one.
Type 2: Adding an additional record in the dimension table. In this case, an additional record is added in the dimension table. This method is very useful for the ACCURATE reporting purpose as all the OLD VALUES are captured. Do not update the existing record. Create a new record (with version number or change date as part of key) of the dimension, while retaining the old one. In this type of implementation, an n-level history is possible.
Ex: Whenever the product category attribute of a product is changed, it is required to keep the history of the old and new category values, for internal audit purpose, so a product category attribute of the product dimension can be treated as SCD Type 2.
Type 3: In this implementation, a column to retain the old value is maintained in the dimension table. In this method, when the value of the dimension is updated (by the current value), the old value column of the table is updated by the most previous value of the dimension. Thus we have the CURRENT value and the MOST PREVIOUS value of the dimension, and hence only 1 level of history can be maintained in this implementation.
Ex: Any changes in the Residence Address of a Customer of a retail outlet can be a SCD Type 3 If the store decides to maintain at least 1 level of history, Here the previous address of the customer as well as the current address is maintained in two different columns.
No comments:
Post a Comment