1. Lookup Transformation
Lookup transformation in Informatica Cloud(IICS) is used to look up a database table or a flat file based on a condition and retrieve value from the object used as a lookup. The Lookup transformation can be used as a both connected and unconnected transformation.
1.1 Connected Lookup Transformation
A Connected Lookup is an inline transformation which comes in the flow of the mapping which receives incoming fields from an upstream transformation, perform a lookup on the object selected based on the lookup condition defined and returns data to the downstream transformation.
1.2 Unconnected Lookup Transformation
An Unconnected Lookup transformation is not connected to any transformation in the mapping. The incoming fields to an unconnected lookup needs to be manually configured. The unconnected lookup can be called from multiple places in a mapping where ever an expression can be used and the values of lookup incoming fields should be passed in the lookup expression.
Unconnected look up returns only one output field to the calling transformation whereas connected look up can return multiple output fields.
2. Lookup Transformation Properties
When you configure a Lookup transformation, you define Lookup properties on the following tabs of the Properties panel
- General tab: By default, the Lookup transformation is connected lookup. The option to convert the lookup into unconnected lookup can be configured.
- Incoming Fields tab: Forconnected lookup the incoming fields comes from the upstream transformation. For unconnected lookup, the incoming fields should be manually defined.
- Lookup Object tab: Select the lookup object connection, source type, Lookup object name and Multiple Matches policy details.
- Lookup Condition tab: Define the condition upon which the lookup return values.
- Return Fields tab: Configurethe fields expected from lookup object as output.
- Advanced tab: Advanced properties like Lookup SQL Override, Lookup Source Filter and various lookup cache options can be selected.
3. Multiple Match Policy in Lookup transformation
When there are multiple matches based on the lookup condition defined, you can configure how the lookup transformation should return the values under Lookup object tab.
The options provided for Multiple Matches are
- Return first row
- Return last row
- Return any row
- Return all rows
- Report error
Is Lookup active or passive transformation?
When the Lookup transformation is configured to return a single row, the Lookup transformation is a passive transformation. When the Lookup transformation is configured to return multiple rows, the Lookup transformation is an active transformation.
4. Lookup Condition
The condition upon which the lookup object returns the values from the lookup object is configured under the Lookup condition tab.
A lookup condition includes an incoming field from the data flow, a field from the lookup object, and an operator. The following operators can be configured in a lookup condition
- = (Equal to)
- < (Less than)
- > (Greater than)
- <= (Less than or equal to)
- >= (Greater than or equal to)
- != (Not equal to)
The lookup condition matches null values i.e. when an input field value is NULL, the mapping task evaluates the NULL equal to null values in the lookup.
5. Lookup Return Fields
By default, the Return Fields tab displays all fields from the selected lookup object. Remove fields that you do not want to use.
The connected lookup transformation returns all the fields available under Return Fields to the downstream transformation. As the unconnected lookup transformation returns only one output field, you can select the field that you want as output by selecting the radio button present against each field.
6. Lookup SQL Override
You can specify the SQL statement you want to use for querying the lookup object. This will override the default SQL statement to query the lookup table.
The default lookup statement prepared by Informatica contains a SELECT statement with all lookup fields and an ORDER BY clause that orders all columns in the same order in which they appear in the Lookup transformation.
Use SQL override if you want to change the ORDER BY clause, add a WHERE clause, transform data by using database functions or use multiple tables as lookup.
How to get more than one output from Unconnected Lookup Transformation?
In order to retrieve more than one output from Unconnected Lookup Transformation, use Lookup SQL Override with multiple fields concatenated which are expected as output. In the expression transformation, split the output of unconnected lookup expression and assign the values to the fields created in expression.
7. Lookup Source Filter
Configure a lookup source filter for a relational Lookup transformation that has caching enabled. It adds a where clause to the default SQL statement. Used to filter the data of the lookup object before it is cached. Do not include the WHERE keyword in the filter condition.
Lookup source Filter is not considered if Lookup SQL Override is used.
8. Lookup Cache
Configure a Lookup transformation to cache the lookup object to increase lookup performance. When you do not configure the Lookup transformation for caching, the Integration Service queries the lookup source for each input row.
When you configure a lookup cache, you can configure the following cache settings
8.1 Static Cache
By default, the Integration Service creates a static cache when you enable caching for a Lookup transformation. The Integration Service 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. A static cache does not change while the Integration Service processes the lookup.
8.2 Dynamic Cache
A Dynamic lookup cache data changes while the Integration Service processes the lookup unlike static cache. When the Integration Service processes each row, it dynamically inserts or updates data in the lookup cache. Use a dynamic lookup cache to keep the lookup cache synchronized with the target.
Related Article: Dynamic Lookup Transformation in Informatica Cloud
8.3 Persistent Cache
When Lookup transformation is configured to have Persistent lookup cache, 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.
Lookup transformation can be configured to rebuild a persistent lookup cache if necessary by selecting Re-cache from lookup source option.
Related Article: SCD Type-2 implementation using Dynamic Lookup in Informatica Cloud
