The SCD Type 3 methodology is used to retailer partial historic information in the Dimension desk. The dimension desk accommodates the present and former information.
- Identifying the brand new document and insert it in to the dimension desk.
- Identifying the modified document and replace the prevailing document in the dimension desk.
We will see the implementation of SCD kind 3 through the use of the shopper dimension desk for instance. The supply desk appears to be like as
CREATE TABLE Customers (
Now I’ve to load the info of the supply into the shopper dimension desk utilizing SCD Type 3. The Dimension desk construction is proven under.
CREATE TABLE Customers_Dim (
Steps to Create SCD Type 3 Mapping
Follow the under steps to create SCD Type 3 mapping in informatica
- Create the supply and dimension tables in the database.
- Open the mapping designer device, supply analyzer and both create or import the supply definition.
- Go to the Warehouse designer or Target designer and import the goal definition.
- Go to the mapping designer tab and create new mapping.
- Drag the supply into the mapping.
- Go to the toolbar, Transformation after which Create.
- Select the lookup Transformation, enter a reputation and click on on create. You will get a window as proven in the under picture.
- Select the shopper dimension desk and click on on OK.
- Edit the LKP transformation, go to the properties tab, take away the Previous_Location port and add a brand new port In_Customer_Id. This new port must be related to the Customer_Id port of supply qualifier transformation.
- Go to the situation tab of LKP transformation and enter the lookup situation as Customer_Id = IN_Customer_Id. Then click on on OK.
- Connect the customer_id port of supply qualifier transformation to the IN_Customer_Id port of LKP transformation.
- Create the expression transformation with enter ports as Cust_Key, Prev_Location, Curr_Location and output ports as New_Flag, Changed_Flag
- For the output ports of expression transformation enter the under expressions and click on on okay
New_Flag = IIF(ISNULL(Cust_Key),1,0)
Changed_Flag = IIF(NOT ISNULL(Cust_Key)
AND Prev_Location != Curr_Location,
1, 0 )
- Now join the ports of LKP transformation (Cust_Key, Curent_Location) to the expression transformaiton ports (Cust_Key, Prev_Location) and ports of supply qualifier transformation (Location) to the expression transformation ports (Curr_Location) respectively.
- The mapping diagram up to now created is proven in the under picture.
- Create a filter transformation and drag the ports of supply qualifier transformation into it. Also drag the New_Flag port from the expression transformation into it.
- Edit the filter transformation, go to the properties tab and enter the Filter Condition as New_Flag=1. Then click on on okay.
- Now create an replace technique transformation and join all of the ports of the filter transformation (besides the New_Flag port) to the replace technique. Go to the properties tab of replace technique and enter the replace technique expression as DD_INSERT
- Now drag the goal definition into the mapping and join the suitable ports from replace technique to the goal definition. Connect Location port of replace technique to the Current_Location port of the goal definition.
- Create a sequence generator transformation and join the NEXTVAL port to the goal surrogate key (cust_key) port.
- The a part of the mapping diagram for inserting a brand new row is proven under:
- Now create one other filter transformation, Go to the ports tab and create the ports Cust_Key, Curr_Location, Prev_Location, Changed_Flag. Connect the ports LKP Transformation (Cust_Key, Current_Location) to the filter transformation ports (Cust_Key, Prev_Location), supply qualifier transformation ports (Location) to the filter transformation port (Curr_Location) and expression transformation port(changed_flag) to the changed_flag port of the filter transformation.
- Edit the filter transformation, go to the properties tab and enter the Filter Condition as Changed_Flag=1. Then click on on okay.
- Now create an replace technique transformation and join the ports of the filter transformation (Cust_Key, Curr_Location, Prev_location) to the replace technique. Go to the properties tab of replace technique and enter the replace technique expression as DD_Update
- Now drag the goal definition into the mapping and join the suitable ports from replace technique to the goal definition.
- The full mapping diagram is proven in the under picture.
Learn find out how to Design Different Types of SCDs in informatica