Lookup Transformation in Informatica , is a connected/Unconnected and Passive (post 9 Active and Passive transformation)which let you look up data flat file/relation tables ,views or synonym.The Integration Service queries the lookup source based on the lookup ports in the transformation and a lookup condition. Lookup transformation returns the result of the lookup to the target or another transformation.
Example : You may lookup transformation on DEPT tables to get DEPT details based on DEPT_NO as input port. In below article we will go through the various type and properties of Lookup Transformation. We will also check some performance tuning related guidelines for Lookup Transformation.
Business Purpose of Lookup Transformation
You can use lookup Transformation in mapping for below type of tasks:
- Getting related value: Retrieve value from the lookup table based on a value in the source. For example, the source has an employee ID. Retrieve the employee name from the lookup table.
- Perform a calculation: Retrieve a value from a lookup table and use it in a calculation. For example, retrieve a sales tax percentage, calculate a tax, and return the tax to a target.
- Update slowly changing dimension tables: Determine whether rows exist in a target and accordingly you can create a new record or update the existing one.
Types of Lookup Transformation in Informatica:
A. Based on the Source Type:
Relational Lookup Transformation:
Relational Lookup Transformation use relation tables as a lookup source, you can connect to the lookup source using ODBC and import the table definition as the structure for the Lookup transformation
Flat File Lookup Transformation :
When you create a Lookup transformation using a flat file as a lookup source, select a flat file definition in the repository or import the source when you create the transformation. When you import a flat file lookup source, the Designer invokes the Flat File Wizard.
Check here for Relational vs flat file lookup transformation in informatica
B. Based on Connection Type:
Connected Lookup Transformation:
A connected Lookup transformation receives source data, performs a lookup, and returns data to the pipeline .Connected lookup is part of mapping and connected via port.
Unconnected Lookup Transformation
An unconnected Lookup is not connected to a source or target. A transformation in the pipeline calls the Lookup transformation with a :LKP expression. The unconnected Lookup returns one column to the calling transformation
3. Based on Cached Type
Cached Lookup Transformation:
You can configure the cache related information in the properties tab of Lookup Transformation. If you cache the lookup source, you can use a dynamic or static cache. By default, the lookup cache remains static and does not change during the session. With a dynamic cache, the Integration Service inserts or updates rows in the cache. When you cache the target table as the lookup source, you can look up values in the cache to determine if the values exist in the target. The Lookup transformation marks rows to insert or update the target.
UnCached Lookup Transformation:
You can keep Lookup Transformation as uncached by unchecking the Cache option in properties tab.
Component of Lookup Transformation:
Lets go through the components of Look up Transformation.
Lookup Transformation source:
While creating lookup Transformation , you can use a flat file, relational table, or source qualifier for a lookup source. When you create a Lookup transformation , you can create the lookup source from the following locations:
- Relational source or target definition in the repository
- Flat file source or target definition in the repository
- Table or file that the Integration Service and PowerCenter Client machine can connect to
- Source qualifier definition in a mapping
The lookup table can be a single table, or you can join multiple tables in the same database using a lookup SQL override. The Integration Service queries the lookup table or an in-memory cache of the table for all incoming rows into the Lookup. The Integration Service can connect to a lookup table using ODBC or native drivers. Configure native drivers for optimal performance.
Indexes and a Lookup Table:
If you have privileges to modify the database containing a lookup table, you can improve lookup initialization time by adding an index to the lookup table. You can improve performance for very large lookup tables. Since the Integration Service queries, sorts, and compares values in lookup columns, the index needs to include every column in a lookup condition.
You can improve performance by indexing the following types of lookup:
- Cached lookups : You can improve performance by indexing the columns in the lookup ORDER BY. The session log contains the ORDER BY clause.
- Uncached lookups : Because the Integration Service issues a SELECT statement for each row passing into the Lookup, you can improve performance by indexing the columns in the lookup condition
Lookup Transformation Ports:
Below type of ports in it.
|Port||Type of Lookup||Description|
|I||Connected / Unconnected||Input port. Create an input port for each lookup port you want to use in the lookup condition. You must have at least one input or input/output port in each Lookup.|
|O||Connected / Unconnected||Output port. Create an output port for each lookup port you want to link to another transformation. You can designate both input and lookup ports as output ports.For connected lookups, you must have at least one output port. For unconnected lookups, select a lookup port as a return port (R) to pass a return value.|
|L||Connected / Unconnected||Lookup port. The Designer designates each column in the lookup source as a lookup (L) and output port (O). Choose those port as Lookup which need to used in condition.|
|R||Unconnected||Return port. Use only in unconnected Lookup. Designates the column of data you want to return based on the lookup condition. You can designate one lookup port as the return port|
Note : We can delete lookup ports from a relational lookup if the mapping does not use the lookup ports which will give us performance gain. But if the lookup source is a flat file then deleting of lookup ports fails the session.
Check here for Properties of lookup transformation in informatica
Example of Lookup Transformation :
Check here for Connected lookup transformation in informatica
Check here for Unconnected lookup transformation in informatica
More about Lookup Transformation:
Lookup Transformation rules and Guidelines:
- In case of large table , don’t use cache if cache table contain more than 5,00,000 rows.
- Use equality (=) condition operator instead of other operator.
- Replace Lookup with IIF or DECODE functions when lookup returns small row sets.
- Avoid date comparisons in lookup, it’s more efficient to convert to string.
- A large lookup table may require more memory resources than available. SQL override (with where close) in the lookup transformation can be used to reduce the amount of memory used by the Lookup cache.
- The unconnected lookup function should normally be called only when a condition (IIF) is evaluated.
- An unconnected lookup transformation can return only one port, however multiple input ports may be passed to it.
- One expression transformer can do more than one lookups.
- Connected lookups use default values, where in unconnected lookups the default values are ignored.
- Lookup cache – cache if the number (and size) of records in the lookup table is relatively small comparing to the number of rows requiring a lookup.
- Lookup caching typically improves performance if the time taken to load the lookup cache is less than the time that would be taken to perform the external read requests. To reduce the amount of cache required: turn off or delete any unused output ports, index the lookup file to speed the retrieval time or use where clauses in the SQL override to minimize the amount of data written to cache.
- If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
- The reserved words file, reswords.txt, is a file that you create and maintain in the Integration Service installation directory. The Integration Service searches this file and places quotes around reserved words when it executes SQL against source, target, and lookup databases.
- You cannot use subqueries in the SQL override for uncached lookups.
- You can use a dynamic cache for relational or flat file lookups.
- Avoid syntax errors when you enter expressions by using the point-and-click method to select functions and ports.You can manually add the lookup ports instead of importing a definition. You can choose which lookup ports are also output ports.
So , in this article we have gone through properties of Lookup Transformation in informatica , component/ Properties of Lookup Transformation , scenarios where to use it , steps to create it and performance related guidelines. Please feel free to give your feedback.