Working with Lookup

Lookup is a passive transformation which accept parameters and returns one ore more columns in result. There are different types of lookup transformations like
1. Connected Cached Lookup ( Also referred as Static lookup)
2. Connected Uncached Lookup
3. Unconnected Cached Lookup
4. Unconnected Uncached Lookup
5. Dynamic Lookup

The same lookup transformation can be changed to Cached/uncached/dynamic based on the property selected.




Connected Cached Lookup

As name suggest, this transformation is in the flow of each row and input and output port is connected to other transformation in the mapping. This lookup can return more than one column in the result.
Characteristics of Connected Lookup

1. A connected Lookup transformation receives input values directly from another transformation in the pipeline.
2. For each input row, the PowerCenter Server queries the lookup source or cache based on the lookup ports and the condition in the transformation.
3. If the transformation is uncached or uses a static cache, the PowerCenter Server returns values from the lookup query.
4. The PowerCenter Server passes return values from the query to the next transformation.

Characteristics of Unconnected Lookup

An unconnected Lookup transformation receives input values from the result of a :LKP expression in another transformation. Unconnected Lookup transformation can be called more than once in a mapping.

1. An unconnected Lookup transformation receives input values from the result of a :LKP expression in another transformation, such as an Update Strategy transformation.
2. The PowerCenter Server queries the lookup source or cache based on the lookup ports and condition in the transformation.
3. The PowerCenter Server returns one value into the return port of the Lookup transformation.
4. The Lookup transformation passes the return value into the :LKP expression.

Informaitca Lookups

Many people has asked the difference between Dynamic and Static lookups.



1. As the name suggest, dynamic lookup changes in between the process and Static remain the same for the process.

2. Dynamic and Static name are with respect to the Cache. In dynamic lookup, lookup Cache are updated in between the process.

3. Static lookup can be uncached. But Dynamic lookup has to cached lookup.

4. Since Cache is updated in dynamic lookup, there is performance impact in case of Dynamic lookup.



Then there is usual question, can we use uncached static lookup as dynamic lookup? Reason this question comes up because in case of static uncached lookup, informatica will perform select statement from database directly for each row. But still this can not replace dynamic lookup. You can think over this and let me know if you can figure it out.

Error Handling Approach

Error Handling is one of the most important component in any Data warehouse or data integration project. If process is not able to handle and manage ERROR effeciently, then it is very rare that the project will succeed.



Following steps and questions should be answered

1. Who is the business owner of the process/project

2. Who will be responsible for Error correction

3. What will the interface to present the ERROR to business community

4. Error correction will be at source or IT will be responsibile to re-run error correction

5. Error retention period in ERROR table



Above are few points to consider before deciding on the ERROR management process. Any ETL will provide feature to implement ERROR management. And sometime we go outside the ETL tool for presentation and correction of ERROR.



Informatica version 7.x onward provide feature where you can capture Error information at transformation level and Source record in ERROR table or ERROR File. These tables or files can be re-processed to present the error records to business community. But in my opinion it is always better to have a common ERROR management process of all data integration project ( independent of tool). Any ETL tool can support that Error management process.