Design/Implement/Create SCD Type 2 Flag Mapping in Informatica

  • Updated
  • Posted in Programming
  • 6 mins read


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

SCD kind 2 will retailer your complete historical past in the dimension desk. Know extra about SCDs at Slowly Changing Dimensions Concepts.

We will see easy methods to implement the SCD Type 2 Flag in informatica. As an instance think about the shopper dimension. The supply and goal desk constructions are proven beneath:

--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),
Flag Number
);

The fundamental steps concerned in making a SCD Type 2 Flagging mapping are

  • Identifying the brand new information and inserting into the dimension desk with flag column worth as one.
  • Identifying the modified report and inserting into the dimension desk with flag worth as one.
  • Identify the modified report and replace the present report in dimension desk with flag worth as zero.

We will divide the steps to implement the SCD kind 2 flagging mapping into 4 elements.

SCD Type 2 Flag implementation – Part 1

Here we’ll see the essential arrange and mapping circulation require for SCD kind 2 Flagging. The steps concerned are:

  • Create the supply and dimension tables in the database.
  • Open the mapping designer instrument, 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 beneath 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 placement ports in the lookup transformation. Create a brand new port (IN_Customer_Id) in the lookup transformation. This new port must be related to the customer_id port of the supply qualifier transformation.
  • Go to the situations 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 beneath 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.Flag = 1
  • 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 beneath 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 beneath.
(*2*)

SCD Type 2 Flag implementation – Part 2

In this half, we’ll determine the brand new information and insert them into the goal with flag worth as 1. The steps concerned are:

  • Now create a filter transformation to determine and insert new report 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_Flag”.
  • Drag and join the NextVal port of sequence generator to the Expression transformation. In the expression transformation create a brand new output port (Flag) and assign worth 1 to it.
  • Now join the ports of expression transformation (Nextval, Flag) to the Target definition ports (Cust_Key, Flag). The a part of the mapping circulation is proven in the beneath picture.

SCD Type 2 Flag implementation – Part 3

In this half, we’ll determine the modified information and insert them into the goal with flag worth as 1. The steps concerned are:

  • Create a filter transformation. Call this filter transformation as FIL_Changed. This is used to seek 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, Flag ports of expression transformation (Expr_Flag created in half 2) to the cust_key, Flag ports of the goal definition respectively. The a part of the mapping diagram is proven beneath.

SCD Type 2 Flag implementation – Part 4

In this half, we’ll replace the modified information in the dimension desk with flag worth as 0.

  • 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 (Flag). Assign a worth “0” to this Flag 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 beneath.

Recommended Reading

Learn easy methods 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