Acme Supply 123 ABC CA CA Y Co The Current_State and the Historical_State are the same. The Current_Flag attribute indicates that this is the current or most recent record for this supplier. When Acme Supply Company moves to Illinois, we add a new record, as in Type 2 processing: Supplier_Key Supplier_Code Supplier_Name Current_State Historical_State Start_Date End_Date Current_Flag Acme Supply 123 ABC IL CA N Co Acme Supply 124 ABC IL IL Y Co We overwrite the Current_State information in the first record (Supplier_Key = 123) with the new information, as in Type 1 processing. We create a new record to track the changes, as in Type 2 processing. And we store the history in a second State column (Historical_State), which incorporates Type 3 processing. If our example supplier company were to relocate again, we would add another record to the Supplier dimension, and we would once again overwrite the contents of the Current_State column: Supplier_Key Supplier_Code Supplier_Name Current_State Historical_State Start_Date End_Date Current_Flag Acme Supply 123 ABC NY CA N Co Acme Supply 124 ABC NY IL N Co Acme Supply 125 ABC NY NY Y Co Note that, for the current record (Current_Flag = 'Y'), the Current_State and the Historical_State are always the same. Type 2 / Type 6 Fact Implementation Surrogate Key Alone In many Type 2 and Type 6 SCD implementations, the surrogate key from the dimension is put into the fact table in place of the natural key when the fact data is loaded into the data repository. The surrogate key is selected for a given fact record based on its effective date and the Start_Date and End_Date from the dimension table. This allows the fact data to be easily joined to the correct dimension data for the corresponding effective date. Here is the Supplier table as we created it above using Type 6 methodology: Supplier_Key Supplier_Code Supplier_Name Current_State Historical_State Start_Date End_Date Current_Flag Acme Supply 123 ABC NY CA N Co Acme Supply 124 ABC NY IL N Co
123 ABC Acme Supply Co CA CA Y The Current_State and the Historical_State are the same. The Current_Flag attribute indicates that this is the current or most recent record for this supplier. When Acme Supply Company moves to Illinois, we add a new record, as in Type 2 processing: Supplier_Key Supplier_Code Supplier_Name Current_State Historical_State Start_Date End_Date Current_Flag 123 ABC Acme Supply Co IL CA N 124 ABC Acme Supply Co IL IL Y We overwrite the Current_State information in the first record (Supplier_Key = 123) with the new information, as in Type 1 processing. We create a new record to track the changes, as in Type 2 processing. And we store the history in a second State column (Historical_State), which incorporates Type 3 processing. If our example supplier company were to relocate again, we would add another record to the Supplier dimension, and we would once again overwrite the contents of the Current_State column: Supplier_Key Supplier_Code Supplier_Name Current_State Historical_State Start_Date End_Date Current_Flag 123 ABC Acme Supply Co NY CA N 124 ABC Acme Supply Co NY IL N 125 ABC Acme Supply Co NY NY Y Note that, for the current record (Current_Flag = 'Y'), the Current_State and the Historical_State are always the same. Type 2 / Type 6 Fact Implementation Surrogate Key Alone In many Type 2 and Type 6 SCD implementations, the surrogate key from the dimension is put into the fact table in place of the natural key when the fact data is loaded into the data repository. The surrogate key is selected for a given fact record based on its effective date and the Start_Date and End_Date from the dimension table. This allows the fact data to be easily joined to the correct dimension data for the corresponding effective date. Here is the Supplier table as we created it above using Type 6 methodology: Supplier_Key Supplier_Code Supplier_Name Current_State Historical_State Start_Date End_Date Current_Flag 123 ABC Acme Supply Co NY CA N 124 ABC Acme Supply Co NY IL N
04-8-SCD-from%20wikipedia%20added%202011.docx