Update Strategy Transformation in Informatica with Example
Update Strategy Transformation in Informatica , is a connected and active transformation. Till now you have seen that, most of the transformation in Informatica were use to insert the records. Update Strategy transformation let you insert, update , delete data from target Data also. It can also reject the incoming data depending upon your condition.For example : when a customer is changing address , you can use this update strategy transformation to update data in your Customer Tables. In below article we will go through the properties of Update Strategy Transformation. We will also discuss the steps of adding /configuring Update Strategy transformation in Informatica Mapping. We will also check some performance tuning related guidelines for Update Strategy Transformation.
Business Purpose of Update Strategy Transformation:
Update Strategy Transformation is mainly used in Datawarehouse project to update the Target Database with the help of some Delta records (Changed data which need to be updated).
Properties of Update Strategy Transformation :
- Active /Passive : Update Strategy Transformation is an Active transformation as it can also reject rows.
- Connected/Unconnected Transformation : Update Strategy Transformation is a connected Transformation.
- Types of ports in Update Strategy Transformation:
- Input : For reading input Data
- Output : For providing output Data
Updating Rows in Informatica:
You can define update operation on below two levels:
Update Strategy on Session level:
We can instruct Informatica Service to treat all rows in the same way or use same instruction by configuring Target mapping at session level. You can edit the update strategy of Target on session level by following below steps
- Create a session for the current mapping
- Now Edit the session by clicking on Session -> edit
- Go to Properties tab and set the properties of Target as mentioned below
Set below option as per your need.
- Insert: Select this option to insert all rows into a target table.
- Delete: Select this option to delete a rows from a table.
- Update: Under Update set below sub -option
- Update as Update. Update each row flagged for update if it exists in the target table.
- Update as Insert. Inset each row flagged for update.
- Update else Insert. Update the row if it exists. Otherwise, insert it.
- Truncate table: Select this option to truncate the target table before loading data.
Update Strategy on Mapping level:
You can flag a row (for insert , update, delete or Reject depending upon some condition) by adding “Update strategy Expression” in the properties tab of Update Strategy Transformation in a Mapping. Below Flags can be set in Update strategy Expression.
|Flag||Numeric Value||Type Operation|
We normally define some condition in update strategy expression to perform any of above action ,
Example : IIF(EMP_FOUND =1, DD_UPDATE,DD_INSERT)
Update Strategy Transformation Components
When you configure a Update Strategy transformation, define the following components:
- Transformation tab: You can rename the transformation and add a description.
- Properties tab: You can specify the tracing level, Forward Rejected Rows, and Most Importantly Update Strategy Expression (Type of Action to be taken)
- Ports tab: Once port under Update Strategy is created , you will see input and output port for Update Strategy.
Configuring Update Strategy Transformation Ports:
You can create input ports by copying them from another transformation or by manually creating them in Ports tab.
- Port name: Add the name of the port.
- Datatype, precision, and scale: Configure the datatype and set the precision and scale for each port.
- Default Value: In case port data is null , you can define its default value..
Configuring Update Strategy Transformation Properties:
Modify the Update Strategy Transformation properties by clicking on the Properties tab.
|Update Strategy Expressions||Type of Operation(Insert, Update, Delete) need to be done. Normally we add IIF , DECODE function to figure out the operation to be done.|
|Forwarding Rejected Rows||You can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. By default, the Integration Service forwards rejected rows to the next transformation|
|Tracing Level||Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.|
Must Read : Learn more about Router Transformation als0
How to use Update Strategy transformation in Informatica:
Use the following procedure to create a Update Strategy transformation in Informatica mapping.
Steps to create an Update Strategy transformation:
- In the Mapping Designer, open a Mapping.
- Click Transformation > Create. Select Update Strategy transformation.
Enter a name and click Done.
- You can create ports in Update Strategy transformation by selecting and dragging it from input transformation to Update Strategy transformation.You can manually create input ports on the Update Strategy Ports tab by double clicking on it.
- Click the Transformation tab and configure transformation properties.
- Enter an update strategy expression to flag rows as inserts, deletes, updates, or rejects
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.
- Connect output ports of Update Strategy Transformation to target
Example of Update Strategy Transformation:
A well detailed example of Update Strategy Transformation can be found here.
More about Update Strategy Transformation:
Update Strategy Transformation rules and Guidelines:
- Update strategy need primary key on the target table to work.You should also check if primary key is defined in Target Definition or not.
- Try to use Update strategy as less as possible because of it slow performance
- If you are just inserting records , then update strategy transformation should be skipped. You can us direct mapping.
- To check existing records , you can use Lookup and set the Operation Flag accordingly in Update strategy Expression.
So , in this article we have gone through properties of Update Strategy Transformation ,component/Properties of Update Strategy Transformation , scenarios where to use it , steps to create it and performance related guidelines. Hope you enjoyed the article. Please feel free to give your feedback.