September 24, 2013 \ Ananth TM Slowly Changing Dimension/SCD in datastage To understand what is Slowly Changing Dimension, we first understand these: Most frequently, we insert into Fact tables . Very infrequently we update the facts that were loaded incorrectly. It is even less likely to delete rows from the fact table; the only time this might happen is if you wanted to archive the old data that is no longer relevant for the current portion of the data warehouse. Dimension tables, on the other hand are likely to see frequent updates. A classic example of this is a consumer dimension. People can change names, phone numbers, addresses, etc regularly. Dimensions that are changing over time are referred to as slowly changing dimensions (SCD). Slowly Changing Dimension Type 1: override the existing value Customer_key Customer_name Customer_city Customer_state 123 Ms. Brown Houston Texas 123 Ms. Brown Dallas Texas Slowly Changing Dimension – SCD Type 1 Type 2: create a new record with effective dates Customer_key Customer_name Customer_city Customer_state Effective_date Obsolete_date 123 Ms.Brown Houston Texas 1/1/2000 1/1/2005 234 Ms. Brown Dallas Texas 1/1/2005 NULL Slowly Changing Dimension – SCD Type 3 Type 3: create a new and old columns, no new rows Customer_key Customer_name Customer_city Customer_state Change_date New_city 123 Ms. Brown Houston Texas 1/1/2005 Dallas Slowly Changing Dimension – SCD Type 3