SCD Type 2 Implementation in Informatica Intelligent Cloud Services (Data Integration)

Q) How to implement slowly changing dimension (SCD) Type 2 Effective Date mapping in Informatica cloud data Integration?

Step1: Create 2 tables in oracle database as follows
--Source Table

Create Table SCD2_Source
(
  ID Number ,
  Name    Varchar2(30),
  Role    Varchar2(30),
  Sal    Varchar2(30)
);

--Target Dimension Table

Create Table SCD2_Dim
(
  S_key NUMBER PRIMARY KEY,
  ID NUMBER ,
  Name    Varchar2(30),
  Role    Varchar2(30),
  Sal    Varchar2(30),
  Start_Date DATE,
  End_Date DATE,
  Flag char(1),
  OPERATION Varchar2(100)

);

Step2: Login to Infa cloud and select Data Integration services

Step3: Click on New Asset-> Mappings-> Mapping

Drag below components from the toolbar and configure as suggested below

1.Drag source and configure it with source table

2. Drag a lookup transformation and configure it with the target dimension table

3. Connect the source to Lookup and include only the ID field and add a condition  Target_Id = Source_id

4. Drag an expression transformation and connect lookup and Source to it ( It may ask you  to resolve name conflicts, resolve them by using prefix or suffix)

5. Include only the fields that may change from the lookup (Ex., LKP_Role, LKP_Salary, and LKP_S_Key)

6. Add an output port o_SRC_Hashkey as type string(200) and configure it with the expression MD5(Role||To_Char(Sal)) this will be compared with the lookup HashKey to check if there is any change.

7. Add another output port o_LKP_Hashkey as type string(200) and configure it with the expression MD5(LKP_Role||To_Char(LKP_Sal))

8. Add Router and connect the Expression to Router.

9. Create two groups Insert and Update and configure them  INSERT when LKP_S_key is NULL -- (ISNULL(LKP_S_key ) and update when LKP_S_key is not NULL and o_SRC_Hashkey  is not equal to o_LKP_Hashkey --  (NOT ISNULL(LKP_S_key ) AND o_SRC_Hashkey  <> o_LKP_Hashkey

10. Drag an expression and connect the insert group to it and only include source fields in it and add 2 output fields.

     o_Start_Date -- sysdate

      o_flag -- "Y"

11. Drag target (INSERT)and connect the above expression transformation to it and create a sequence generator and also connect it with the target.

12. Map target fields (NEXTVAL from seq will be given to S_Key, o_Start_Date will be given to Start_Date and o_flag will be given to Flag and End_Date will be NULL fro insert Record)

13. Drag an expression and connect the output group of Router to it and include only LKP_S_KEY, Role, SRC. Add two output ports

      o_End_Date -- sysdate

      o_flag -- "N"

14. Drag Target(Existing_Update) and connect the above expression to it. The select operation to Data Driven and Data Driven Condition is DD_UPDATE also add the Surrogate key(S_KEY) as the update column

15. Map S_Key to LKP_S_KEY and o_End_Date to End_Date and o_flag to Flag

16. Add expression and connect the update group from the router to it.

17.Include only the source rows and create two output fields

      o_Start_Date -- sysdate

      o_flag -- "Y"

18. Add target (EXISTING_INSERT) and connect the sequence and the above expressions to it.

19. Map the fields like we did for insert and save the mapping.

Step4: Close the mapping and click on New Asset -> Tasks -> Mapping Task

Input Task Name and select Runtime Environment and Mapping which we created

Step5: Click Next ( Sequence) and Finish

Step6: The Mapping Task will be saved then run the task.