2

Update Strategy Transformation In Informatica

Update Strategy Transformation in Informatica

The Update Strategy Transformation in Informatica is an active, connected transformation. Its purpose is to control how data is inserted, updated, deleted, and rejected from a given target table. It is vital in the data integration/warehousing world to have this control as it allows us to store data in a manner that fits our business need.

Configuring the Update Strategy Transformation

The update strategy transformation can be configured from within the session or the mapping itself. Lets take a look at these options one at a time.

Configuring the Mapping Session

Lets take a look at a quick example. To configure the update strategy from within the session, start in the workflow manager and double click the mapping session named s_UPD_DEMO below.

Update Transformation In Informatica Workflow Example

Update Transformation In Informatica Workflow Example

After double clicking the session, click on the properties tab.

Update Transformation In Informatica Example Session Properties Tab

Update Transformation In Informatica Example Session Properties Tab

This tab will show us a “treat source rows as” drop down with four different options. This attribute allows us to control, at a session level, if rows are inserted, updated, or deleted from our target table(s). If we select insert, our mapping will attempt to insert each record directed to our target table. We need to ensure our data includes a primary key mapped to our target table’s primary key. If for some reason we attempt to insert a record with a primary key that already exists in our target table, this record will be rejected. These same rules apply to update and delete options. The difference being, our mapping records will attempt to be updated or deleted in our target table instead of inserted.

In addition to selecting the correct “treat source rows as” attribute option, we must set target table level attributes on the mapping tab of our session. If for example we have selected the insert option for our “treat source rows as” option, we need to click on our target table, then check the insert attribute checkbox. Make sure to uncheck all other database operation checkboxes.

Update Transformation In Informatica Example Session Mapping Tab

Update Transformation In Informatica Example Session Mapping Tab

If we want to update records and have selected update as our “treat source rows as” attribute option, then we have three options at the target table level to choose from: Update as Update, Update as Insert, and Update else Insert.

Attribute Value
Update as Update Update each row in the target table if it exists
Update as Insert Insert each row in the target table
Update else Insert First try to update a row in the target table if it exist, otherwise insert it.

Similarly to our “treat source rows as” insert example, if we want to delete rows, we should select delete as our “treat source rows as” attribute option and check the delete attribute checkbox for each target table on the mapping tab.

Our last table attribute option is the truncate target table option. This will truncate all data within the target table prior running any records through our mapping.

Our final “treat source rows as” attribute option is data driven. This is the default option when we add an update strategy transformation in our mapping. This option tells our mapping to use the logic within our update strategy transformation when determining whether to insert, update, delete or reject records. This finer control is very nice to have when building a data warehouse and a best practice when flagging records for the same target table with different database operations.

Configuring the Mapping

Lets take a look at how to configure our update strategy transformation in an Informatica mapping. Below we have an example mapping M_Sales.

Update Transformation In Informatica Mapping Example

Update Transformation In Informatica Mapping Example

Notice we have a single source of sales agent data coming from a flat file. Our data is being routed through a router transformation, then to 3 different update strategy transformations (UPD_INSERT_HIGH, UPD_UPDATE_MEDIUM, and UPD_DELETE_LOW), all flagging our records for different database operations. Finally we are sending our sales agent records to the same target table, SALES.

Lets take a quick look at our group router criteria…

Sales Router Transformation Group Filter Conditions

Sales Router Transformation Group Filter Conditions

Notice how our filter condition separates agents with high, medium, and low total sales amounts. We are routing agents with high sales to UPD_INSERT_HIGH update strategy transformation, medium sales to UPD_UPDATE_MEDIUM update strategy transformation, and low sales to UPD_DELETE_LOW update strategy transformation. Lets take a look at each of these transformations in more detail.

In the mapping, double clicking on the UPD_INSERT_HIGH update strategy transformation and clicking on the properties tab, we get the below view.

Insert Update Strategy Transformation Example

Insert Update Strategy Transformation Example

Notice how I have programmed DD_INSERT into the update strategy expression transformation attribute. This tells the transformation to flag all records passed through it, for insert into the target table. We can also use numeric values here, but I would recommend using the constants as a best practice since the operation is much more intuitive. Below are all of our options for this attribute along with their corresponding operations.

Operation Constant Numeric Value
Insert DD_INSERT 0
Update DD_UPDATE 1
Delete DD_DELETE 2
Reject DD_REJECT 3

Lets quickly review our two additional update strategy transformation in this mapping.

UPD_UPDATE_MEDIUM is set to update rows it matches on by primary key in our target SALES table.

Update Update Strategy Transformation Example

Update Update Strategy Transformation Example

UPD_DELETE_LOW is set to delete rows it matches on by primary key in our target SALES table.

Delete Update Strategy Transformation Example

Delete Update Strategy Transformation Example

Forward Rejected Rows

Notice how the forward rejected rows transformation attribute is checked. This is the default setting for a new update strategy transformation. This really didn’t come into play in our example, but if we were to set some conditional logic within our update strategy expression, we might reject some rows and decide we do not want them to pass to our next transformation. For example, we could put a statement like the below in our UPD_UPDATE_MEDIUM update strategy transformation:

IIF ( TOTAL_SALES <= 10000 AND TOTAL_SALES > 6000, DD_UPDATE, DD_REJECT)

This statement would instruct the transformation to flag rows for update if TOTAL_SALES was less than our equal to 10000 and greater than 6000. However, if TOTAL_SALES was less or equal to 6000, then we would reject the update. This actual logic may not actual be something we would do in real life, but I think you get the point.

Back to our forward rejected rows attribute, if we leave the checkbox unchecked, these records would not pass to our target table and would be dropped by the Integration Service. Additionally, they would get written the session log file.

If keep the forward rejected rows attribute checked, not much would change. The records would get passed to the target table, but would still be rejected and dropped. However the records would be written to the session reject file instead of the session log file.

Velocity Naming Standard

Velocity recommends the below format when naming a Update Strategy Transformation in Informatica:

UPD_{DESCRIPTOR}
Ex: UPD_SALES

Summary

The Update Strategy Transformation in Informatica is a great tool to control how data passed through a mapping is flagged for insert, update, delete, and reject in the target database table. We can control this at either a mapping session level or a mapping level through the transformation itself. If your in the business intelligence and data warehouse world, you will definitely want a deep understanding of how the Update Strategy Transformation in Informatica works. Happy integrating…

Aaron Gendle

Aaron Gendle

Senior Consultant & Data Architect at Eccella
As a data architect, I have the privilege of solving challenging data problems on a daily basis.

I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.

Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.

Find Aaron Gendle on LinkedIn -
Aaron Gendle

Comments 2

    1. Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *