IICS Tutorial: February 2020

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