Tutorial corner

Informatica,ETL,oracle,sql/plsql

SCD type 6 in informatica mapping with example

Spread the love

SCD type 6 in informatica : In last tutorial , we talked about  Slowly Changing  Dimension Type 6  in Dataware house . We have gone through the several examples of SCD Type 6 their advantages and disadvantages as well. In below tutorial ,we will implement SCD Type 6 (Slowly Changing Dimension Type 6) in Informatica mapping. In below example we will talk about  SCD Type 6 with Effective Date implementation. EMP_SCD6 is our SCD type -6 table and we are going to compare Dept name for record comparison. EMPNO is the natural key for SRG_KEY is Surrogate KEY. So lets start with our Informatica mapping for SCD Type-6

Check here for SCD type 2 in informatica mapping

SCD type 6 in informatica mapping

Create an informatica mapping to implement SCD Type 6  mapping with EMP Table. DEPT is the field to be checked and Effective_date and current_DEPT need to updated in SCD6 table

ORIGINAL :

EMP_INP

EMPNO NAME EFFECTIVE_DATE DEPT

1

JOHNSON 12/17/1990 OPERATIONS

2

HARDING 2/2/1998 ACCOUNTING

3

TAFT 1/2/1996 RESEARCH

4

HOOVER 4/2/1990 SALES

EMP_SCD6

SRG_KEY EMPNO NAME OLD_DEPT CURRENT_DEPT CREATE_DATE END_DATE CURRENT_FLAG

99

1

JOHNSON RESEARCH OPERATIONS 11/20/1989 11/25/1989 N

100

1

JOHNSON SALES OPERATIONS 11/25/1989 12/17/1990 N

101

1

JOHNSON OPERATIONS OPERATIONS 12/17/1990   Y

102

2

HARDING ACCOUNTING ACCOUNTING 2/2/1998   Y

103

3

TAFT RESEARCH RESEARCH 1/2/1996   Y

104

4

HOOVER SALES SALES 4/2/1990   Y

After Change :

EMP_INP

EMPNO NAME EFFECTIVE_DATE DEPT

1

JOHNSON 4/13/1992 ACCOUNTING

2

HARDING 9/17/1998 SALES

3

TAFT 1/2/1996 3RESEARCH

4

HOOVER 7/11/1993 OPERATIONS

5

LINCOLN 6/23/1994 OPERATIONS

EMP_SCD6

SRG_KEY EMPNO NAME OLD_DEPT CURRENT_DEPT CREATE_DATE END_DATE FLAG

99

1

JOHNSON RESEARCH ACCOUNTING 11/20/1989 11/25/1989 N

100

1

JOHNSON SALES ACCOUNTING 11/25/1989 12/17/1990 N

101

1

JOHNSON OPERATIONS ACCOUNTING 12/17/1990 4/13/1992 N

105

1

JOHNSON ACCOUNTING ACCOUNTING 4/13/1992   Y

102

2

HARDING ACCOUNTING SALES 2/2/1998 9/17/1998 N

106

2

HARDING SALES SALES 9/17/1998   Y

103

3

TAFT RESEARCH RESEARCH 1/2/1996   Y

104

4

HOOVER SALES OPERATIONS 4/2/1990 7/11/1993 N

107

4

HOOVER OPERATIONS OPERATIONS 7/11/1993   Y

108

5

LINCOLN OPERATIONS OPERATIONS 6/23/1994   Y

Check here for SCD type 3 in informatica mapping

Solution:

Design Approach :

  • First of all we need to check if incoming record already exist in the output table, so we will need to join EMP_SCD6 with EMP_INP table.
  • Since we need to update the latest records only , so we need to  pick latest records from EMP_SCD6 table for updation
  • For new records , will just need to insert record in EMP_SCD6 table (1 copy of Target table EMP_SCD6)
  • For Existing records ,if there is change in any data , then we need to
  • insert newer version of records ( 1 copy of Target table EMP_SCD6 )
  • Update END_DATE and Current_FLAG for the last active record based on Surrogate key  (Update transformation + 1 copy Target table EMP_SCD6)
  • Also update Current_DEPT field , in all previous records based on EMPNO , Natural Key  (Update transformation + 1 copy Target table EMP_SCD6)

Steps to create mapping for SCD Type 6

  • Create a new mapping m_SCD6_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP_INP  and  EMP_SCD6 ) and add port from both source to common Source Qualifier (SQ_EMP_INP)

Overwrite source qualifier query as below

 

SELECT EMP_INP.EMPNO, EMP_INP.NAME, EMP_INP.EFFECTIVE_DATE, EMP_INP.DEPT, EMP_SCD6.SRG_KEY, EMP_SCD6.SRG_KEY, EMP_SCD6.NAME, EMP_SCD6.OLD_DEPT, EMP_SCD6.CURRENT_DEPT, EMP_SCD6.CREATE_DATE, EMP_SCD6.CREATE_DATE, EMP_SCD6.CURRENT_FLAG
FROM
EMP_INP, EMP_SCD6
where EMP_INP.empno = EMP_SCD6.empno(+)
and EMP_SCD6.CURRENT_FLAG(+) = ‘Y’

  • Add an Expression EXP_CHECK_DATA and drag required port ( EMPNO ,NAME ,EFFECTIVE_DATE,DEPT , EXIST_SRG_KEY,EXIST_EMPNO1 ,EXIST_NAME ,EXIST_OLD_DEPT,EXIST_CURRENT_DEPT, EXIST_CREATE_DATE ,EXIST_UPDATE_DATE)  from SQ_EMP_INP into it.
  • For easy understanding , we have added prefix “EXIST_” for the port being coming from EMP_SCD6 and “NEW_” for the next calculation.
  • Add an extra output port CHECK_FLAG with expression as

IIF(ISNULL(EXIST_EMPNO1),’I‘, IIF(NEW_DEPT !=EXIST_CURRENT_DEPT,’U‘,’R‘))

  • Add a Router rtr_CHANGED_REC and drag all required port (EMPNO ,NAME ,EFFECTIVE_DATE,NEW_DEPT,EXIST_SRG_KEY, EXIST_CURRENT_DEPT, EXIST_CREATE_DATE , EXIST_END_DATE,CHECK_FLAG,EXIST_CURRENT_FLAG) 
  • Add two group in Router with condition as given below
    • INSERT = CHECK_FLAG=’I’
    • UPDATE = CHECK_FLAG=’U’
SCD Type 6 update

SCD Type 6 update

Note : Rest of the records need to be ignored

  • For Records from group =”INSERT”,
    • Add an Expression “exp_NEW_RECORDS” and drag  (EMPNO1,NAME1,NEW_DEPT1, EFFECTIVE_DATE1)   from INSERT group to  (EMPNO,NAME ,CURRENT_DEPT, CREATE_DATE) of exp_NEW_RECORDS.
    • Also add CURRENT_FLAG=’Y’ in the expression.
    • Add copy of Target EMP_SCD6  and connect  required port from exp_NEW_RECORDS   to EMP_SCD6
    • Since these are new records , we need to insert sequence for SRG_KEY with the help of resuable Sequence Generator  seq_EMP_KEY.
SCD Type 6 in Informatica mapping Insert Part

SCD Type 6 in Informatica mapping Insert Part

  • For Records from group =”UPDATE”,
    • Add an Expression “exp_UPDATE_RECORDS” and drag  (EMPNO1,NAME1,NEW_DEPT1, EFFECTIVE_DATE1 , EXIST_SRG_KEY )   from UPDATE group to  (EMPNO,NAME ,CURRENT_DEPT, CREATE_DATE) of exp_UPDATE_RECORDS.
    • Also add CURRENT_FLAG=’Y’ in the expression (for Inserting new version of record)
    • Add OLD_FLAG=’N’ in the expression for updation of old records
  • Insert New Version Records:
    • Add copy of Target EMP_SCD6  and connect  required port from exp_UPDATE_RECORDS
      to EMP_SCD6 (as shown in below figure)
    • Since these are new records , we need to insert sequence for SRG_KEY with the help of resuable Sequence Generator  seq_EMP_KEY.
  • Updating END_DATE and FLAG in Previous records :
    • We need to update previous records , so add update strategy upd_Previous_record and drag (SRG_KEY3  ,NEW_DEPT3 ,OLD_FLAG ,EFECTIVE_DATE ) from exp_UPDATE_RECORDS  to  (SRG_KEY1 ,NEW_DEPT, OLD_FLAG , NEw_END_DATE,)  upd_Previous_record
    • Now connect   (SRG_KEY1 ,NEW_DEPT, OLD_FLAG , NEW_END_DATE)  from upd_Previous_record Update Strategy to (SRG_KEY ,CURREMT_DEPT,CURRENT_FLAG, END_DATE )  of 2nd copy of Target i.e. EMP_SCD62,
    • Dont forget to add “DD_UPDATE” in “Update Strategy Expression “
  • Updating CURRENT_DEPT in all Previous records :
    • We need to  CURRENT_DEPT in all previous records , so we need update based on EMPNO (Natural Keys) as shown in below figure
SCD Type 6 in Informatica mapping Update Part

SCD Type 6 in Informatica mapping Update Part

  • Click on Mapping ( from tool bar) -> then Validate ( to validate the mapping)
  • Now save the mapping ( by clicking on Repository-> Save or you can also press Ctrl+S)

Key Points of SCD Type 6 Informatica mapping

  • Sequence generator seq_EMP_KEY is reusable one
  • SRG_KEY , EMPNO is Marked as “Primary Key” in EMP_SCD6 Target. (so that update Strategy work)

Over all SCD Type 6 Informatica mapping

 

SCD Type 6 in Informatica mapping PreRouter

SCD Type 6 in Informatica mapping PreRouter

SCD Type 6 in Informatica mapping Insert Part

SCD Type 6 in Informatica mapping Insert Part

SCD Type 6 in Informatica mapping Update Part

SCD Type 6 in Informatica mapping Update Part

So in above tutorial , we have gone through the detailed example of Slowly changing Dimension  (SCD ) Type -6 in Informatica mapping , learnt the step to add various transformation , configuring properties and some key point to be taken care while implementing  SCD Type 6 in Informatica.

Updated: December 25, 2015 — 4:04 pm

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme