Usage of ical lookup

Lookup is a common operation in ETL, such as product key-to-proxy key conversion and ID-to-name conversion. In addition to this normal conversion, The lookup transformation component in Informatica can also be used to update slowly changing dimensions, which is powerful. Based on the online document of Informatcia8.1, This article briefly introduces the lookup transformation of informatica.
English and Chinese terms:
• Transformation: Conversion
• Connected: connected
• Unconnected: unconnected
• Cache: cache

I. lookup Functions
• Get related values: for example, search for the name by ID
• Perform calculation: for example, obtain a calculation formula to obtain a result.
• Slow update and change dimension: determines whether to insert or update records based on lookup conditions.

Ii. rational lookups vs flat file lookups
The lookup source can be a table in a relational database or a flat file. You can select a relational table from source or target, or use the import Wizard like a file.

Iii. connected lookups vs unconnected lookups
The transformation of informatica can be divided into two types: connected and non-connected.
The so-called connection conversion means that the conversion is in the entire ETL data stream, and its input ort is directly converted from another, rather than connected, it is independent from the main data stream, use other expressions in the conversion to obtain the input data.
The connection lookup conversion processes each piece of data in the data stream. If the lookup condition is not met, the predefined default value is output and the dynamic cache can be updated. The output value is all output/lookup ports. You can use static or dynamic caching.
The unconnected lookup conversion only processes data that meets the lookup condition and returns only one value. If the condition is not met, the output is NULL. An unconnected lookup conversion can be called multiple times. The unique return port of the output value. Only static cache can be used.

Iv. cache
Informatica uses the cache mechanism for lookup. The server processes the cache as follows:
When the first data is processed, the server creates a cache in the memory. The cache size is determined by the attributes of lookup conversion. For the lookup condition, an index cache is created, and for the output value, it is placed in the data cache.
If the memory cache size is not enough, the overflow cache will be placed in the file. After the session ends, the cache is cleared unless the lookup cache is set to permanent.
For static cache, lookup conversion is not allowed to update it. Dynamic cache, if any value that does not meet the conditions is found in lookup, can insert or update the cache.
You can also choose not to use any cache.

V. lookup transmation Components
Lookup has five components, that is, right-click lookup conversion and select the five tabs displayed after editing. In fact, basically all the transformation of informatica is about five components.
The first transformation tab and the second ports tab are similar to the fifth metadata extensions tab. Except for the general I (input) and O (output) ports, the lookup ports include L (lookup) and R (return ). The return port can only have one, and cannot be directly connected to other transformations. It can only be obtained through the LKP: expression.
The fourth condition tab specifies the lookup condition, which is to set the association conditions for two tables.
The third property is the most important. Here, you can rewrite SQL to customize lookup, set the processing method for returning multiple records that meet the conditions, and set whether to use dynamic cache, and the cache size.

6. lookup tips
• Create an index in the lookup condition column
• Use = conditions whenever possible. If there are multiple conditions, try to put the = condition at the beginning.
• Use cache whenever possible for small tables and set the cache size so that the entire table can be cached in the memory.
• If the lookup table and source table are in the same database and the cache is not large enough, try to use join instead of lookup.
• For static lookup, use the persistent cache whenever possible, so that multiple sessions can be reused.