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.