Update Strategy Transformation

Active and Connected Transformation till now, we have only inserted rows in our target tables. What if we want to update, delete or reject rows coming from source based on some condition.

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:

1. Within a session

2. 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 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.

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

Forwarding Rejected Rows:

We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them.

Steps:

1. Create Update Strategy Transformation

2. Pass all ports needed to it.

3. Set the Expression in Properties Tab.

4. Connect to other transformations or target.

Performance tuning:

1. Use Update Strategy transformation as less as possible in the mapping.

2. Do not use update strategy transformation if we just want to insert into target table, instead use direct mapping, direct filtering etc.

3. For updating or deleting rows from the target table we can use Update Strategy transformation itself.