Update Strategy Transformation in Informatica

Update Strategy transformation is an active transformation that flags a row for insert, update, delete, or reject. The Update Strategy transformation tests each row to see if it meets a particular condition, and then flags the row accordingly.  You can configure the transformation to pass rows flagged for reject to the next transformation or to drop rows flagged for reject.

Lists of constants for each database operation and their numeric equivalent

You can disallow inserts or deletes for rows flagged for insert or delete. You can choose different ways to handle updates for all rows flagged for update. Define the update strategy options in the Advanced properties of a target data object in a mapping. You can set the following update strategy options
Insert Inserts all rows flagged for insert into a target. 

Delete Deletes all rows flagged for delete from a target.

Update Strategy Update strategy for existing rows. Select one of the following strategies:

Update as Update Updates all rows flagged for update. This is the default value.

Update as Insert Inserts all rows flagged for update.

Update else Insert Updates all rows flagged for update if they exist in the target and then inserts any remaining rows marked for insert.

Truncate Table Truncates the target before loading data.

Advanced Properties for Update Strategy Transformation

Forward Rejected Rows Determines whether the Update Strategy transformation passes rejected rows to the next transformation or drops rejected rows. The Data Integration Service flags the rows for reject and writes them to the reject file. If you do not select Forward Rejected Rows, the Data Integration Service drops rejected rows and writes them to the mapping log file.

Tracing Level Amount of detail that appears in the log for this transformation.

Scenario

Load the target table with employee data with salaries higher than 2000 otherwise reject them. 

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.

All About Informatica Lookup Transformation

Lookup Caches

Lookuptransformation with Dynamic Cache Scenario 
 
Unconnected Lookup Transformation with Role Playing Date Dimension Scenario

 Lookup transformation is a passive or active transformation that looks up data in a flat file, logical data object, reference table, or relational table. The Lookup transformation can return one row or multiple rows from a lookup. The Integration Service queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. It passes the result of the lookup to other transformations or a target.

Connected Lookups

In Connected lookup transformation, we connect input and output ports  to other transformation in the mapping.

• For each input row, the Integration Service queries the lookup source or cache based on the lookup ports and the condition in the transformation.
• If the transformation is uncached or uses a static cache, the Integration Service returns values from the lookup query.
• If the transformation uses a dynamic cache, the Integration Service inserts the row into the cache when it does not find the row in the cache. When the Integration Service finds the row in the cache, it updates the row in the cache or leaves it unchanged. It flags the row as insert, update, or no change.
• The Integration Service passes return values from the query to the next transformation. If the transformation uses a dynamic cache, you can pass rows to a Filter or Router transformation to filter new rows to the target.
• If there is no match for the lookup condition, the Integration Service returns the default value for all output ports.

Unconnected Lookups

Unconnected Lookup transformation  is not connected to other transformations in the mapping.

• It receives input from the result of a :LKP expression in a transformation such as an Expression transformation or Aggregator transformation.
• The Integration Service queries the lookup source or cache based on the lookup ports and condition in the transformation.
• The :LKP expression passes parameters to the Lookup transformation and receives a result back from the lookup.
• If there is no match for the lookup condition, the Integration Service returns NULL.
• You can call the Lookup transformation more than once in a mapping.

Lookup Query

The Integration Service queries the lookup based on the ports and properties you configure in the Lookup transformation. The Integration Service runs a default lookup query when the first row enters the Lookup transformation. If you use a lookup against a relational table, you can override the lookup query.

Lookup Filter

You can configure a Lookup source filter for a relational Lookup transformation that has caching enabled. If you configure a SQL override and a filter on the lookup query, the Integration Service ignores the filter.

Run-time Properties

Lookup Caching Enabled When It is enabled, the Integration Service queries the lookup source once, caches the values, and looks up values in the cache. When you disable caching, each time a row passes into the transformation, the Integration Service issues a select statement to the lookup source for lookup values. The Integration Service always caches flat file lookups.

Lookup Data Cache Size Maximum size the Integration Service allocates to the data cache in memory. Default is Auto.

Lookup Index Cache Size Maximum size the Integration Service allocates to the index in memory. Default is Auto.

Cache File Name Prefix Prefix for the cache file. You can specify the cache file name prefix for a persistent lookup cache.

Pre-build Lookup Cache Allows the Integration Service to build the lookup cache before the Lookup transformation receives the data.

Lookup Cache Directory Name Directory used to build the lookup cache files when you configure the Lookup transformation to cache the lookup source.

Re-cache from Lookup Source Rebuilds the lookup cache to synchronize the persistent cache with the lookup table.

Advanced Properties

Lookup Cache Persistent Indicates whether the Integration Service uses a persistent lookup cache.

Case Sensitive String Comparison The Integration Service uses case-sensitive string comparisons when performing lookups on string columns when the lookup source is a flatfile.

Null Ordering You can choose to sort null values high or low. By default, the Integration Service sorts null values high for flatfile. For relational lookups, null ordering is based on the database default value.

Tracing Level Sets the amount of detail that appears in the log for this transformation.

Update Else Insert Applies to dynamic lookup cache only. The Integration Service updates the row in the cache if the row type entering the Lookup transformation is update, the row exists in the index cache, and the cache data is different than the existing row. The Integration Service inserts the row in the cache if it is new.

Insert Else Update   Applies to dynamic lookup cache only. The Integration Service inserts the row in the cache if the row type entering the Lookup transformation is insert and it is new. If the row exists in the index cache but the data cache is different than the current row, the Integration Service updates the row in the data cache.

Output Old Value on Update The Integration Service outputs the value that existed in the cache before it updated the row. Otherwise, the Integration Service outputs the updated value that it writes in the cache.

Update Dynamic Cache Condition Applies to dynamic lookup cache only. An expression that indicates whether to update the dynamic cache. The Integration Service updates the cache when the condition is true and the data exists in the cache. Default is true.

Connection Connection to the relational database that contains the relational lookup source.

Sorted Input Indicates that input data is presorted by groups.

Datetime Format Define a datetime format and field width.

Thousand Separator Value is None. This field is read-only.

Decimal Separator Value is a period. This field is read-only.

*The last four properties are applicable only for flatfiles.

Informatica Dynamic Lookup Cache with Scenario

Dynamic lookup cache changes while the Integration Service processes the lookup. The Integration Service builds the dynamic lookup cache when it processes the first lookup request. It queries the cache based on the lookup condition for each row that passes into the transformation. The Integration Service updates the lookup cache when it processes each row.

Based on the results, the Integration Service performs one of the following actions on the dynamic lookup cache when it reads a row from the source:

Inserts the row into the cache The Integration Service inserts the row when the row is not in the cache and you configured the Lookup transformation to insert rows into the cache. The Integration Service flags the row as insert.

Updates the row in the cache The Integration Service updates the row when the row exists in the cache and you configured the Lookup transformation to update rows in the cache. The Integration Service flags the row as an update row.

Makes no change to the cache The Integration Service makes no change when the row exists in the cache and you configured the Lookup transformation to insert new rows only. Or, the row is not in the cache and you specified to update existing rows only. Or, the row is in the cache, but based on the lookup condition, nothing changes. The Integration Service flags the row as unchanged.

As soon as you create a dynamic lookup, Informatica adds one extra port to the lookup. The New Port is 

NewLookupRow  The NewLookupRow property can contain one of the following values: 

0 = No update to the cache.

1 = Insert row into cache.

2 = Update row in cache. 

Scenario

Lookup and insert the new Customer records

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.

Informatica Powercenter Express - All About Lookup Caches

The Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service creates caches as the source rows enter the Lookup transformation. It allocates memory for the cache based on the amount you configure in the transformation. The Integration Service stores condition values in the index cache and output values in the data cache.

Types of lookup caches

Static Cache

A static cache does not change while the Integration Service processes the lookup. The Integration Service rebuilds a static cache each time it processes the lookup. It looks up values in the cache for each row that comes into the Lookup transformation. When the lookup condition is true, the Integration Service returns a value from the lookup cache. Use a static cache

• If the lookup source does not change while the mapping runs.
• You must use a static cache for an unconnected lookup.
• To increase performance. The Integration Service processes a Lookup transformation with a static cache faster than a lookup transformation with a dynamic cache.

Persistent Cache

A persistent cache does not change each time the Integration Service processes the lookup. The Integration Service saves the lookup cache files and reuses them the next time it processes a Lookup transformation configured to use the cache. Use a persistent cache when the lookup source does not change. You can configure the Lookup transformation to rebuild a persistent lookup cache if necessary.

Dynamic Cache

A dynamic lookup cache changes while the Integration Service processes the lookup. The Integration Service builds a dynamic lookup cache when it processes the first lookup request. When it processes each row, the Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is synchronized with the target. Use a dynamic cache when you want to update a target based on new and changed records.

Shared Cache

A shared cache can be used by multiple Lookup transformations in the same mapping. Use a shared cache to increase mapping performance. Instead of generating a separate lookup cache for each Lookup transformation, the Integration Service generates one cache.

Informatica Powercenter Express - Normalizer Transformation

Normalizer transformation is an active transformation that transforms one source row into multiple target rows. The transformation can pass source data from one source row to multiple targets to reduce target file size and to decrease data redundancy. When you define the Normalizer transformation, you configure an input row hierarchy that describes the source data structure.

Generated Column ID

The Normalizer transformation returns a Generated Column ID  output port for each instance of a multiple-occurring field. For example, if a field occurs four times in a source record, the Developer tool returns a value of 1, 2, 3, or 4 in the generated column ID port based on which instance of the multiple-occurring data occurs in the row.

Input Hierarchy Definition

When we create a Normalizer transformation, we define an input hierarchy that describes records and fields in the source.The Developer tool creates the transformation input ports based on our definition.

Occurs column in the Normalizer view identifies the number of instances of a field or record in a source row.

Level column in the Normalizer view indicates where a field or record appears in the input hierarchy.

Output Groups and Ports

The output groups and ports are defined in the Overview view of the Normalizer transformation. You can create the output ports after you define the transformation input hierarchy. The Developer tool generates the first level output groups by default. The Developer tool does not generate an output group for any record. You must manually configure how you want to return the multiple-occurring groups of fields.

Advanced properties for normalizer transformation

Automatically generate first-level output groups Automatically generates the output groups for level 1 fields.

Tracing Level Amount of detail that appears in the log for this transformation.

Scenario

Normalize multiple-occurring quarterly sales data 

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.