Tutorial corner

Informatica,ETL,oracle,sql/plsql

Example of update strategy transformation in informatica

Spread the love

Example of update strategy transformation in informatica : Earlier we talked about the Update Strategy transformation in Informatica , steps to create it , configuring the port and properties. We also discussed the business scenario of Update Strategy Transformation in Informatica. Now this is the time to apply Update Strategy Transformation in a Informatica mapping. Its a very simple but detailed example of Update Strategy Transformation in Informatica.

Example of Update Strategy Transformation in Informatica

Create a mapping to update final EMP_ALL  table with the help of  input EMP_DELTA table with the help of  Update Strategy Transformation .To simplify the process , Change type flag is added.

Check here for filter transformation example in informatica

Source Data:

EMP_DELTA Table Data:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT CHANGE_TYPE
14 ROOSEVELT CPA

9

10/12/1995

35000

2000

1

U

5

LINCOLN TECH

6

6/23/1994

22500

1400

4

D

9

JACKSON CEO

1/1/1990

75000

4

I

8

GRANT ENGINEER

10

3/30/1997

32000

3

U

2

JEORGE TECH

8

10/12/1995

50000

2

I

EMP_ALL  Orignal Table Data:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT

14

ROOSEVELT CPA

9

10/12/1995

35000

1

5

LINCOLN TECH

6

6/23/1994

22500

1400

4

8

GRANT ENGINEER

10

3/30/1997

32000

5

Target Data:

EMP_ALL  Resultant Table Data:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT

14

ROOSEVELT CPA

9

10/12/1995

35000

2000

1

8

GRANT ENGINEER

10

3/30/1997

32000

3

9

JACKSON CEO

1/1/1990

75000

4

2

JEORGE TECH

8

10/12/1995

50000

2

Check here for Aggregator transformation example in informatica

Solution:

Below are the steps to create full mapping.

  • Create a new mapping m_Update Strategy_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP_DELTA) and Target (EMP_ALL) to the mapping.
  • Add Update Strategy Transformation by “Go to Toolbar->click Transformation -> Create. Select the Update Strategy transformation”
  • Enter name “updt_EMP_ALL” and click Done.
  • Drag all required port  from SQ_EMP_DELTA to updt_EMP_ALL Transformation .
    Update Strategy Transformation step 4

    Update Strategy Transformation step 4

  • Click the Transformation tab and configure transformation properties.
Update Strategy Transformation step 5

Update Strategy Transformation step 5

  • Click on Expression editor for “update strategy expression” and below condition there

DECODE(CHANGE_TYPE,’I’,DD_INSERT,’U’,DD_UPDATE,’D’,DD_DELETE)

Update Strategy Transformation step 6

Update Strategy Transformation step 6

  • Note: See that ‘Forward Rejected Rows’ is checked. So that Forward Rejected Rows to Next Transformation/Written to Rejected/Bad File. If not it will write it to session log file.
  • Click OK to close updt_EMP_ALL update Strategy Transformation
  • Connect output ports of updt_EMP_ALL  Update Strategy Transformation to  target EMP_ALL
  • 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)
  • Generate the workflow and run it

Over all Mapping:

Example of update strategy transformation in informatica

Update Strategy-Transformation Full Mapping

So, in this article we have gone through step by step example of Update Strategy Transformation in Informatica mapping,  learnt the  step to add it , configure the port , properties , adding update strategy expression ports. Hope you enjoyed the article. Please feel free to give your feedback.

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