Update Strategy Transformation in Informatica

Update Strategy Transformation is an active and connected transformation, which is used for Inserting, Updating, Deleting and rejecting the records in the target table. The target table should contain Primary key.

Update strategy transformation is used mostly with lookup transformation, the row from the source qualifier is compared with row from lookup transformation and then the row is flagged to Insert (or) update a record in target table. To maintain history or a source table, then for every change in the source records we must insert a new record in the target table using update strategy transformation.

Example :- Our target table, T_CUSTOMERS contains customer data and when a customer address is changed we can update the new address in the table but if the requirement is to maintain both address (last and updated address) then we have to create a new row containing the updated address and preserve the original address with the old customer address.

In Informatica transformations, we can set the Update strategy at two different levels.

Session level : Configuring at session level instructs the integration service to treat all rows in the same way Insert (or) Update (or) Delete (or) use instructions coded in the session mapping to flag for different database operations.

Mapping Level : Use update strategy transformation to flag rows for Insert, update, delete or  reject records.

Flagging Rows in Mapping with Update strategy Transformation.

We have to flag each row for inserting, updating, deleting or rejecting a record in target table. The constants and  their numeric equivalents for each data base operation are given below.

DD-INSERTNumeric value is 0 and it is used for flagging the row as insert.
DD-UPDATENumeric value is 1 and used for flagging the rows as update.
DD-DELETENumeric value is 2 and used for flagging the row as delete.
DD-REJECTNumeric value is 3 and used for flagging the row as reject.

Update Strategy Expression.

Update strategy expression is used to flag a row by assigning constant numeric values like 0, 1, 2, 3 . To check update strategy expression, navigate to properties tab in the Update Strategy transformation. Update strategy expression uses IIF or DECODES function.

Example : IIF (department-id = 10, DD-UPDATE, DD-INSERT).

How to create Update Strategy Transformation

In this Informatica tutorial, we are going to learn about creating update strategy transformation. Before creating transformation, let us create a target table with name SCD1. Go to SQL Plus / SQL developer create target table as shown below.

</>
Copy
CREATE TABLE SCD1
(
EmpKey NUMBER(5) PRIMARY KEY,
EmpNo NUMBER(5),
Ename VARCHAR2(30),
Job VARCHAR2(30),
Sal NUMBER(5),
Deptno Number
) ;
  • Import the target table in to Informatica by select the Target Designer from tools in PowerCenter Designer.
  • Select your target table and click on Ok button.
Update Strategy Transformation in Informatica

Creating mapping for update strategy transformation

  1. To create mapping, Go to mappings menu | Click on Create | Enter the name as m_update_strategy.
  2. Click on Ok, drag and drop source EMP table and target table into the mapping window workspace.

Lookup Transformations

In update strategy transformation, we have to create Lookup transformation, Expression Transformation and Router Transformation.

  • To create, Go to Transformations menu | click on Create | select the transformation type as LookUp and name it as LKP_RECORD_AVAILABILITY.
  • Select target SCD1 and click on OK.
Update Strategy Transformation in Informatica
  • Drag and drop EmpNo port form SQ_EMP to LookUp transformation table.
  • Rename EMPNO1 to IN_EMPNO
Update Strategy Transformation in Informatica

Double click on Lookup transformation table and click on Conditions tab. To add New Condition, click on new condition icon and enter the Condition as shown below.

Update Strategy Transformation in Informatica
  • Click on Ok.

Creating Expression Transformation

Now we have to create expression transformation, to create Go to Transformations menu | click on Create | select type as Expression and enter name for expression transformation as EXP_Calculate_new_Changed.

  • Drag and drop Empno, Ename, Job, Sal,Deptno from Source Qualifier Transformation to Expression Transformation  and from the LookUp transformation , drag and drop EmpKey , Job, Sal to Expression

    transformation.
  • Rename Job1 as prev_job and sal1 as prev_sal by editing expression transformation as shown below.
  • Click on Ok.
Update Strategy Transformation in Informatica

Double click on Expression Transformation  | click on Ports tab | click on Add a new Port icon twice to create two new ports. Rename the new two ports as NEW_RECORD and CHANGED_RECORD and the two must be only output port only.

  • For NEW_RECORD add IIF(ISNULL(EMPKEY) ‘TRUE’, ‘FALSE’).
  • For CHANGED_RECORD add IIF((NOT ISNULL(EMPKEY) AND (JOB != PREV_JOB) OR

    (SAL != PREV_SAL)),‘TRUE’,’FALSE’)
  • Click on OK button.
Update Strategy Transformation in Informatica

Router Transformation

Router Transformation must be added to implement update strategy transformation. To add click on transformation | Create and enter name for the transformation.

  • Drag and drop all the ports from Expression Transformation to Router Transformation.

Double click on Router transformation to add new groups. To create new groups, click on Groups tab | click on Add a New Group icon twice to create two groups.

  • For NEW_RECORD enter NEW_RECORD=’TRUE’.
  • For CHANGED_RECORD enter CHANGED_RECORD=’TRUE’.
  • Click on OK.

Drag and drop ports of EMPNO1, ENAME1, JOB1, SAL1 ,DEPTNO1 from
NEW_RECORD group into Expression Transformation and Drag and drop all the above Five ports from Expression Transformation to Update Strategy transformation and the mapping will be appeared as shown below.

Update Strategy Transformation in Informatica

Double click on Update Strategy Transformation to add Strategy expression in properties.  To add click on Properties tab | Enter Update Strategy expression as 0 or DD_INSERT.

  • Add Sequence generator transformation to connect NEXTVAL port from Sequence Generator Transformation to EMPKEY of target table.
  • Drop Update Strategy, Expression transformations into the above Mapping Workspace and from the Router Transformation , drag and drop the ports EMPKEY3, EMPNO3, ENAME3, JOB3, SAL3 ,DEPTNO3 of Changed_RECORD group into Expression Transformation.
  • Drag and drop all the ports from Expression Transformation to Update Strategy Transformation.

Add new update strategy transformation and name the transformation as UPD_CHANGED_RECORD and double click on Update Strategy Transformation to add strategy expression. To add click on Properties tab | set the “Update Strategy Expression” value as 1 or DD_UPDATE.

  • Click on Ok.

Now add second target table in mapping workspace by copy and paste the target table into the mapping workspace, for the purpose of Updated Record flow and connect all the Ports from Update Strategy Transformation to another Target. Finally the mapping will be appeared as shown below.

Update Strategy Transformation in Informatica
  • Save the Transformation.

Creating Workflow

Workflow in Informatica can be created using Informatica PowerCenter Workflow manager. To create, go to Workflow Manager tool | create a Workflow with the name of w_SCD1 and create a Session with the name of s_m_SCD1_JOB_SAL and map this task to ourabove Mapping m_SCD1_JOB_SAL.

  •  Click on Ok.
  • Connect Task and Workflow using Link Task.
Update Strategy Transformation in Informatica
  • Double click on Session, click on Mapping tab | select SQ_EMP and set that to SOURCE connection.
  • Select Target and set that to TARGET connection.
  • Select Lookup transformation and set that to TARGET connection.
  • Click on Properties tab and set the Treat Source Rows as Data Driven .
  • click on Ok.

Finally save the workflow and click on Start workflow. To monitor workflow, open Informatica PowerCenter Workflow monitor.