Update Strategy transformation

Update Strategy transformation Active and Connected Transformation. The Update Strategy transformation to update, delete or reject rows coming from source based on some condition.

For 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 Data warehouse, 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 PowerCenter, we set the update strategy at two different levels:

1. Within a session
2. Within a Mapping

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:

1. Insert: Select this option to insert a row into a target table.
2. Delete: Select this option to delete a row from a table.
3. Update: We have the following options in this situation:
1. Update as Update. Update each row flagged for update if it exists in the target table.
2. Update as Insert. Inset each row flagged for update.
3. Update else Insert. Update the row if it exists. Otherwise, insert it.
4. Truncate: Select this option to truncate the target table before loading data.

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
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:

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. You can write these expression in Properties Tab of Update Strategy transformation.

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

Or

IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

Note: We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. To do, see the Properties Tab for the Option.