Tutorial corner

Informatica,ETL,oracle,sql/plsql

SCD type 3 in informatica mapping with example

Spread the love

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

SCD type 3 in informatica mapping

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

Check here for SCD type 2 in informatica mapping

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_SCD3

SRG_KEY EMPNO NAME OLD_DEPT DEPT CREATE_DATE UPDATE_DATE

101

1

JOHNSON NULL OPERATIONS 12/17/1990

102

2

HARDING NULL ACCOUNTING 2/2/1998

103

3

TAFT NULL RESEARCH 1/2/1996

104

4

HOOVER NULL SALES 4/2/1990

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_SCD3

SRG_KEY EMPNO NAME OLD_DEPT DEPT CREATE_DATE UPDATE_DATE

101

1

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

102

2

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

103

3

TAFT RESEARCH 1/2/1996

104

4

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

105

5

LINCOLN OPERATIONS 6/23/1994

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_SCD3 with EMP_INP table.
  • Since we need to update the latest records only , so we need to  pick latest records from EMP_SCD3 table for updation
  • For new records , will just need to insert record in EMP_SCD3 table (1 copy of Target table EMP_SCD3)
  • For Existing records ,if there is change in any data , then we need to updated  records (Update transformation + 1 copy Target table EMP_SCD3)

Steps to create mapping for SCD Type -3

  • Create a new mapping m_SCD3_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP_INP  and  EMP_SCD3 ) 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_SCD3.SRG_KEY, EMP_SCD3.EMPNO, EMP_SCD3.NAME, EMP_SCD3.OLD_DEPT, EMP_SCD3.CURRENT_DEPT, EMP_SCD3.CREATE_DATE, EMP_SCD3.UPDATE_DATE
FROM
EMP_INP, EMP_SCD3
where EMP_INP.empno = EMP_SCD3.empno(+)

  • Add an Expression EXP_CHECK_DATA and drag required port ( EMPNO ,NAME ,EFFECTIVE_DATE,DEPT ,SRG_KEY,EMPNO1 ,NAME ,OLD_DEPT,CURRENT_DEPT, CREATE_DATE ,UPDATE_DATE)  from SQ_EMP_INP into it,
  • Add an extra output port CHECK_FLAG with expression as
  • IIF(ISNULL(EMPNO1),’I‘, IIF(NEW_DEPT != OLD_DEPT,’U‘,’R‘))
  • Add a Router rtr_CHANGED_REC and drag all required port (EMPNO ,NAME ,EFFECTIVE_DATE,NEW_DEPT,SRG_KEY, CURRENT_DEPT, CREATE_DATE CHECK_FLAG) 
  • Add two group in Router with condition as given below
    • INSERT = CHECK_FLAG=’I’
    • UPDATE = CHECK_FLAG=’U’
SCD Type 3

SCD Type 3 update

Note : Rest of the records need to be ignored

  • For Records from group =”INSERT”,
    • Add copy of Target EMP_SCD3  and connect  (EMPNO1,NAME1,NEW_DEPT1, EFFECTIVE_DATE1) from INSERT group to  (EMPNO,NAME,CURRENT_DEPT, CREATE_DATE ) of EMP_SCD31.
    • Since these are new records , we need to insert sequence forSRG_KEY with the help of resuable Sequence Generator  seq_EMP_KEY.
  • For Records from group =”UPDATE”,
    • We need to update previous records , so add update strategy upd_Previous_record and drag (SRG_KEY3 ,EFECTIVE_DATE3, CURRENT_DEPT3,NEW_DEPT3 ) from UPDATE group to  upd_Previous_record
    • Now connect (SRG_KEY3 ,EFECTIVE_DATE3, NEW_DEPT3, CURRENT_DEPT3)  from upd_Previous_record Update Strategy to (SRG_KEY ,UPDATE_DATE, OLD_DEPT, CURRENT_DEPT3)  of 2nd copy of Target i.e. EMP_SCD32,
    • Dont forget to add “DD_UPDATE” in “Update Strategy Expression “
  • 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)

Check here for SCD type 6 in informatica mapping

Key Points of SCD Type 3 Informatica mapping

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

Over all SCD Type 3 Informatica mapping

SCD type 3 in informatica mapping Part 1

SCD type 3 in informatica mapping Part 1

SCD type 3 in informatica mapping Part 2

SCD type 3 in informatica mapping Part 2

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

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