Design/Implement/Create SCD Type 2 Effective Date Mapping in Informatica

  • Updated
  • Posted in Programming
  • 6 mins read


Q) How to create or implement slowly altering dimension (SCD) Type 2 Effective Date mapping in informatica?

SCD kind 2 will retailer your complete historical past in the dimension desk. In SCD kind 2 efficient date, the dimension desk could have Start_Date (Begin_Date) and End_Date because the fields. If the End_Date is Null, then it signifies the present row. Know extra about SCDs at Slowly Changing Dimensions Concepts.

We will see the best way to implement the SCD Type 2 Effective Date in informatica. As an instance think about the shopper dimension. The supply and goal desk constructions are proven under:

--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),
Begin_Date Date,
End_Date Date
);

The primary steps concerned in making a SCD Type 2 Effective Date mapping are

  • Identifying the brand new information and inserting into the dimension desk with Begin_Date because the Current date (SYSDATE) and End_Date as NULL.
  • Identifying the modified file and inserting into the dimension desk with Begin_Date because the Current date (SYSDATE) and End_Date as NULL.
  • Identify the modified file and replace the present file in dimension desk with End_Date as Curren date.

We will divide the steps to implement the SCD kind 2 Effective Date mapping into 4 components.

SCD Type 2 Effective Date implementation – Part 1

Here we are going to see the essential arrange and mapping circulation require for SCD kind 2 Effective Date. The steps concerned are:

  • 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 lookup transformation, go to the ports tab and take away pointless ports. Just preserve solely Cust_key, customer_id and site ports in the lookup transformation. Create a brand new port (IN_Customer_Id) in the lookup transformation. This new port must be linked to the customer_id port of the supply qualifier transformation.
  • Go to the circumstances tab of the lookup transformation and enter the situation as Customer_Id = IN_Customer_Id
  • Go to the properties tab of the LKP transformation and enter the under question in Lookup SQL Override. Alternatively you possibly can generate the SQL question by connecting the database in the Lookup SQL Override expression editor after which add the WHERE clause.
SELECT  Customers_Dim.Cust_Key as Cust_Key,
Customers_Dim.Location as Location,
Customers_Dim.Customer_Id as Customer_Id
FROM Customers_Dim
WHERE Customers_Dim.End_Date IS NULL
  • Click on Ok in the lookup transformation. Connect the customer_id port of supply qualifier transformation to the In_Customer_Id port of the LKP transformation.
  • Create an expression transformation with enter/output ports as Cust_Key, LKP_Location, Src_Location and output ports as New_Flag, Changed_Flag. Enter the under expressions for output ports.
New_Flag = IIF(ISNULL(Cust_Key), 1,0)
Changed_Flag = IIF( NOT ISNULL(Cust_Key) AND
LKP_Location != SRC_Location, 1, 0)
  • The a part of the mapping circulation is proven under.
(*2*)

SCD Type 2 Effective Date implementation – Part 2

In this half, we are going to determine the brand new information and insert them into the goal with Begin Date as the present date. The steps concerned are:

  • Now create a filter transformation to determine and insert new file in to the dimension desk. Drag the ports of expression transformation (New_Flag) and supply qualifier transformation (Customer_Id, Location) into the filter transformation.
  • Go the properties tab of filter transformation and enter the filter situation as New_Flag=1
  • Now create a replace technique transformation and join the ports of filter transformation (Customer_Id, Location). Go to the properties tab and enter the replace technique expression as DD_INSERT.
  • Now drag the goal definition into the mapping and join the suitable ports of replace technique transformation to the goal definition.
  • Create a sequence generator and an expression transformation. Call this expression transformation as “Expr_Date”.
  • Drag and join the NextVal port of sequence generator to the Expression transformation. In the expression transformation create a brand new output port (Begin_Date with date/time information kind) and assign worth SYSDATE to it.
  • Now join the ports of expression transformation (Nextval, Begin_Date) to the Target definition ports (Cust_Key, Begin_Date). The a part of the mapping circulation is proven in the under picture.

SCD Type 2 Effective Date implementation – Part 3

In this half, we are going to determine the modified information and insert them into the goal with Begin Date as the present date. The steps concerned are:

  • Create a filter transformation. Call this filter transformation as FIL_Changed. This is used to search out the modified information. Now drag the ports from expression transformation (changed_flag), supply qualifier transformation (customer_id, location), LKP transformation (Cust_Key) into the filter transformation.
  • Go to the filter transformation properties and enter the filter situation as changed_flag =1.
  • Now create an replace technique transformation and drag the ports of Filter transformation (customer_id, location) into the replace technique transformation. Go to the properties tab and enter the replace technique expression as DD_INSERT.
  • Now drag the goal definition into the mapping and join the suitable ports of replace technique transformation to the goal definition.
  • Now join the Next_Val, Begin_Date ports of expression transformation (Expr_Date created in half 2) to the cust_key, Begin_Date ports of the goal definition respectively. The a part of the mapping diagram is proven under.

SCD Type 2 Effective Date implementation – Part 4

In this half, we are going to replace the modified information in the dimension desk with End Date as present date.

  • Create an expression transformation and drag the Cust_Key port of filter transformation (FIL_Changed created in half 3) into the expression transformation.
  • Go to the ports tab of expression transformation and create a brand new output port (End_Date with date/time information kind). Assign a worth SYSDATE to this port.
  • Now create an replace technique transformation and drag the ports of the expression transformation into it. Go to the properties tab and enter the replace technique expression as DD_UPDATE.
  • Drag the goal definition into the mapping and join the suitable ports of replace technique to it. The full mapping picture is proven under.

Recommended Reading

Learn the best way 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