Looking up data in Informatica
Purpose and overview
Lookup transformation - returns values from a database table or a flat file associated with a given input value.
In SQL teminology a lookup may be considered as a sub-query.
There are the following Lookup transformation types:
- Dynamic Lookup (connected) - when the lookup table is also the target, the data may go out of sync with the target table loaded in memory. The Dynamic Lookup transformation allows for the synchronization of the target lookup in-memory table with its physical table in a database.
Dynamic Cache properties worth looking at: Dynamic Lookup Cache (boolean, use onlywith the lookup cache enabled), 'Insert Else Update' or 'Update Else Insert'.
- Unconnected lookup - used when a lookup is not needed for each record, the lookup data is fetched at the point the mappings requires it. It doesn't contain links from and to other transformation. Data lookup is performed only for those rows which require it, which can significantly improve performance of the ETL process.
Unconnected lookup function can be invoked within any transformation that supports expressions.
Example - dynamic lookup
Let's consider the daily updated master table with products. Within a day a product may change its status or an error may be corrected. A new product record may be added first and a change to that record may be added later. The change (insert followed by an update) needs to be detected automatically.
Example - unconnected lookup
Lookup table is usually used within a conditional statement in an Expression. For instance, to look up a product color only for rows which don't have it filled, use the following :
IIF ( ISNULL(colorproduct_id),:lkp.COLORLOOKUP(product_id),colorproduct_id)
Basically there are two types of cache memory: index and data cache. All port values from the lookup table where the port is part of the lookup condition are loaded into index cache.
The index cache contains all port values from the lookup table where the port is specified in the lookup condition.
The data cache contains all port values from the lookup table that are not in the lookup condition and that are specified as "output" ports.
After the cache is loaded, values from the Lookup input port(s) that are part of the lookup condition are compared to the index cache.
Upon a match the rows from the cache are included in the stream.
Key cache related properties in the Lookup transform:
Useful tips / best practices