Update Strategy Transformation
Update Strategy Transformation in Informatica
Update strategy transformation is an active and connected transformation. Update strategy transformation is used to insert, update, and delete records in the target table. It can also reject the records without reaching the target table. When you design a target table, you need to decide what data should be stored in the target.
When you want to maintain a history or source in the target table, then for every change in the source record you want to insert a new record in the target table.
When you want an exact copy of source data to be maintained in the target table, then if the source data changes you have to update the corresponding records in the target.
When you want to maintain a history or source in the target table, then for every change in the source record you want to insert a new record in the target table.
When you want an exact copy of source data to be maintained in the target table, then if the source data changes you have to update the corresponding records in the target.
Example: If Address of a CUSTOMER changes, we can update the old address or keep both old and new address. One row is for old and one for new. This way we maintain the historical data.
Update Strategy is used with Lookup Transformation. In DWH, we create a Lookup on target table to determine whether a row already exists or not. Then we insert, update, delete or reject the source record as per business need.
In Power Center, we set the update strategy at two different levels:
- Within a session
- Within a Mapping
1. Update Strategy within a session:
When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.
Session Configuration:
Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default. Specifying Operations for Individual Target Tables:
You can set the following update strategy options:
Insert: Select this option to insert a row into a target table.
Delete: Select this option to delete a row from a table.
Update: We have the following options in this situation:
- 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.
2. Flagging Rows within a Mapping
Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.
Operation
|
Constant
|
Numeric Value
|
INSERT
| DD_INSERT | 0 |
UPDATE
| DD_UPDATE | 1 |
DELETE
| DD_DELETE | 2 |
REJECT
| DD_REJECT | 3 |
Update Strategy Expression:
You have to flag rows by assigning the constant numeric values using the update strategy expression. The update strategy expression property is available in the properties tab of the update strategy transformation.
Each row is tested against the condition specified in the update strategy expression and a constant value is assigned to it. A sample expression is show below:
IIF(department_id=10, DD_UPDATE, DD_INSERT)
Mostly IIF and DECODE functions are used to test for a condition in update strategy transformation.
Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition.
IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )
Or
IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )
- The above expression is written in Properties Tab of Update Strategy T/f.
- DD means DATA DRIVEN
Update Strategy and Lookup Transformations:
Update strategy transformation is used mostly with lookup transformation. The row from the source qualifier is compared with row from lookup transformation to determine whether it is already exists or a new record. Based on this comparison, the row is flagged to insert or update using the update strategy transformation.
Update Strategy and Aggregator Transformations:
If you place an update strategy before an aggregator transformation, the way the aggregator transformation performs aggregate calculations depends on the flagging of the row. For example, if you flag a row for delete and then later use the row to calculate the sum, then the integration service subtracts the value appearing in this row. If it’s flagged for insert, then the aggregator adds its value to the sum.
Forwarding Rejected Rows:
We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them.
Steps:
- Create Update Strategy Transformation
- Pass all ports needed to it.
- Set the Expression in Properties Tab.
- Connect to other transformations or target.
Performance tuning:
- Use Update Strategy transformation as less as possible in the mapping.
- Do not use update strategy transformation if we just want to insert into target table, instead use direct mapping, direct filtering etc.
- For updating or deleting rows from the target table we can use Update Strategy transformation itself.
Important Note:
Update strategy works only when we have a primary key on the target table. If there is no primary key available on the target table, then you have to specify a primary key in the target definition in the mapping for update strategy transformation to work.
Share This:
Tharun Katanguru
I'm Tharun Katanguru. A full time web designer and Data Engineer. I enjoy to make modern template. I love create blogger template and write about Web Design, Data Warehousing and Data Management. Now I'm working as a Jr Etl Developer.
No Comment to " Update Strategy Transformation "