SLOWLY CHANGING DIMENSION (SCD) TYPES

Dimensions data change slowly over time, rather than changing on regular basis.

Slowly changing dimension (SCD) is the concept which describes how we store dimensional data (Historical or current) in warehouse.

Below are the types of SCD:

    Type 0 – In this the old dimensional data will be persisted and remains unchanged.

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

    Type 1 – In this the old dimensional data will be overwritten by the new data and history will not be preserved.

Before change

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

After change

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Retail

    Type 2 – In this the new record will be inserted for any change in the source and history will be preserved by creating a new additional record

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 – This is similar to Type 2 SCD the only difference being instead of adding a new row we will be adding a new column.

          Unlike Type 2 SCD this will only store partial history as shown in below table. 

 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

    Type 4 – This is combination of SCD Type 1 and Type 2 where we create 2 dimension table one with the current data and other which stores historical data similar to type 2 dimension.

Current table: 

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

Historical table: 

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date
1	Cust_1	Retail	01-01-2010	21-07-2010
1	Cust_1	Oher	22-07-2010	17-05-2012
1	Cust_1	Corporate	18-05-2012	31-12-9999

    Type 6 – It is a Combination of SCD type 1,2,3 (1+2+3=6) which stores historical data in rows and columns both as shown in below table.

Customer_ID	Customer_Name	Current_Type	Historical_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	Retail	01-01-2010	21-07-2010	N
2	Cust_1	Corporate	Other	22-07-2010	17-05-2012	N
3	Cust_1	Corporate	Corporate	18-05-2012	31-12-9999	Y

HOW TO UPDATE A RECORD IN TARGET TABLE WITHOUT USING UPDATE STRATEGY

A target table can be updated without using 'Update Strategy'. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as "Update as Update" and check the "Update" check-box.

Let's assume we have a target table "Customer" with fields as "Customer ID", "Customer Name" and "Customer Address". Suppose we want to update "Customer Address" without an Update Strategy. Then we have to define "Customer ID" as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.