Create/Design/Implement SCD Type 3 Mapping in Informatica

  • Updated
  • Posted in Programming
  • 4 mins read


Q) How to create or implement or design a slowly altering dimension (SCD) Type 3 utilizing the informatica ETL device.

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.

The course of concerned in the implementation of SCD Type 3 in informatica is

  • 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 (
Customer_Id Number,
Location Varchar2(30)
)

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 (
Cust_Key Number,
Customer_Id Number,
Curent_Location Varchar2(30),
Previous_Location Varchar2(30)
)

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:
(*3*)
  • 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.

Recommended Reading

Learn find out how to Design Different Types of SCDs in informatica

SCD Type 1
SCD Type 3
SCD Type 2 version
SCD Type 2 Flag
SCD Type 2 Effective Date

Leave a Reply