We will see how to implement the SCD Type 2 version in informatica. As an example consider the customer dimension. The source and target table structures are shown below:
--Source Table Create Table Customers ( Customer_Id Number Primary Key, Location Varchar2(30) ); --Target Dimension Table Create Table Customers_Dim ( Cust_Key Number Primary Key, Customer_Id Number, Location Varchar2(30), Version Number );
The basic steps involved in creating a SCD Type 2 version mapping are
Lets divide the steps to implement the SCD type 2 version mapping into three parts.
SCD Type 2 version implementation - Part 1
Here we will see the basic set up and mapping flow require for SCD type 2 version. The steps involved are:
SELECT Customers_Dim.Cust_Key as Cust_Key, Customers_Dim.Location as Location, Customers_Dim.Version as Version, Customers_Dim.Customer_Id as Customer_Id FROM Customers_Dim ORDER BY Customers_Dim.Customer_Id, Customers_Dim.Version--
New_Flag = IIF(ISNULL(Cust_Key), 1,0) Changed_Flag = IIF( NOT ISNULL(Cust_Key) AND LKP_Location != SRC_Location, 1, 0)
The part of the mapping flow is shown below.
SCD Type 2 version implementation - Part 2
In this part, we will identify the new records and insert them into the target with version value as 1. The steps involved are:
SCD Type 2 Version implementation - Part 3
In this part, we will identify the changed records and insert them into the target by incrementing the version number. The steps involved are:
You can implement the SCD type 2 version mapping in your own way. Remember that SCD type2 version mapping is rarely used in real time.