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

  • Updated
  • Posted in Programming
  • 6 mins read


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

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

We will see how one can implement the SCD Type 2 model in informatica. As an instance contemplate the client 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),
Version Number
);

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

  • Identifying the brand new data and inserting into the dimension desk with model quantity as one.
  • Identifying the modified report and inserting into the dimension desk by incrementing the model quantity.

Lets divide the steps to implement the SCD kind 2 model mapping into three components.

SCD Type 2 model implementation – Part 1

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

  • Create the supply and dimension tables in the database.
  • Open the mapping designer software, 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 client dimension desk and click on on OK.
  • Edit the lookup transformation, go to the ports tab and take away pointless ports. Just maintain solely Cust_key, customer_id, location ports and Version 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 order by clause.
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--
  • You have to make use of an order by clause in the above question. If you kind the model column in ascending order, then it’s important to specify “Use Last Value” in the “Lookup coverage on a number of match” property. If you’ve got sorted the model column in descending order then it’s important to specify the “Lookup coverage on a number of match” choice as “Use First Value”
  • 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 move is proven under.

(*2*)

SCD Type 2 model implementation – Part 2

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

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

SCD Type 2 Version implementation – Part 3

In this half, we’ll establish the modified data and insert them into the goal by incrementing the model quantity. The steps concerned are:

  • Create a filter transformation. This is used to seek out the modified report. Now drag the ports from expression transformation (changed_flag), supply qualifier transforamtion (customer_id, location) and LKP transformation (model) into the filter transformation.
  • Go to the filter transformation properties and enter the filter situation as changed_flag =1.
  • Create an expression transformation and drag the ports of filter transformation besides the changed_flag port into the expression transformation.
  • Go to the ports tab of expression transformation and create a brand new output port (O_Version) and assign the expression as (model+1).
  • Now create an replace technique transformation and drag the ports of expression transformation (customer_id, location,o_version) 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 port of expression transformation (Expr_Ver created in half 2) to the cust_key port of the goal definition. The full mapping diagram is proven in the under picture:

You can implement the SCD kind 2 model mapping in your personal means. Remember that SCD type2 model mapping isn’t used in actual time.

Recommended Reading

Learn how one can 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