The Lookup transformation is used to look up a source, source qualifier, or target to get the relevant data. You can look up flat file and relational tables. The Lookup transformation works on similar lines as the joiner, with a few differences. For example, lookup does not require two sources. Lookup transformations can be connected and unconnected. They extract the data from the lookup table or file based on the lookup condition.
Perform the following steps to create and configure a Lookup transformation:
If the required source or target is not available in the repository, you can import it before you use it to look up. Click on the Import button to import the structure, as shown in the preceding screenshot.
The Lookup transformation with the same structure as the source, target, or source qualifier appears in the workspace.
We have learned how to create a Lookup transformation in the previous section. Now we will implement a mapping similar to the one shown in the Joiner transformation using the Lookup transformation. We will use the EMPLOYEE
Oracle table as the source and will look up the EMP_FILE
flat file. Perform the following steps to implement the mapping:
EMPLOYEE
table as the source in Mapping Designer and drag the target.EMPLOYEE
table and the EMP_FILE
file, drag the EMPLOYEE_ID
column from the EMPLOYEE
table to the Lookup transformation. Drag the corresponding columns from the EMPLOYEE
source qualifier and Lookup transformation to the target, as shown in the following screenshot:When you create the Lookup transformation, you can configure it to cache the data. Caching the data makes the processing faster, as the data is stored internally after the cache is created. Once you choose to cache the data, the Lookup transformation caches the data from the file or table once. Then, based on the condition defined, the lookup sends the output value. As the data gets stored internally, processing becomes faster as it does not need to check the lookup condition in the file or database. Integration Service queries the cache memory instead of checking the file or table to fetch the required data.
When you choose to create the cache, Integration Service creates cache files in the $PMCacheDir
default directory. The cache is created automatically and is also deleted automatically once the processing is complete. We will discuss what cache is later in the chapter.
A Lookup transformation has four different types of ports. To view the ports of the Lookup transformation, click on its Ports tab.
The ports are as follows:
Similar to the Source Qualifier transformation, which generates a default query when you use the source as a relational database table, the Lookup transformation also generates a default query based on the ports used in the Lookup transformation. To check the default query generated by the Lookup transformation, click on the Properties tab and open Lookup Sql Override, as shown in the following screenshot:
Similar to overriding the default SQL in the Source Qualifier transformation, you can override the default query generated by the Lookup transformation. If you override the default query generated by the Lookup transformation, it is referred to as a lookup SQL override.
As mentioned, unconnected transformations are not connected to any other transformation, source, or target by any links. An unconnected Lookup transformation is called by another transformation with the :LKP
function. Using the :LKP
function, you can pass the required value to the input port of the Lookup transformation, and the return port passes the output value back to the transformation from which the lookup was called. A mapping using an unconnected Lookup transformation is as follows:
In the preceding mapping, we are implementing the same scenario that we implement using a connected Lookup transformation. Perform the following steps to implement this scenario:
EMPLOYEE_ID
input port in the Lookup transformation that will accept the value of EMPLOYEE_ID
using the :LKP
function.AGE
output port in the Expression transformation that is used to call an unconnected Lookup transformation using the :LKP
function. Link the AGE
port to the target. Write the :LKP.LKP_FILE(EMPLOYEE_ID)
function in the expression editor of the AGE
column, as shown in the following screenshot:AGE
the return port, as shown in the following screenshot:When you execute the mapping, row-wise data will flow from the source to the Expression transformation. The :LKP
function passes the data to the Lookup transformation, which compares the data based on the condition defined in the Condition tab, which in turn returns the data from the Return port to the Expression transformation, from where the data is passed further in the mapping.
LOCATION
output port to the Expression transformation. Then, create another Lookup transformation. We cannot use the same Lookup transformation, as the unconnected Lookup transformation can return only one port. Follow the process described here to look up for the AGE
port to complete the mapping.We have seen the implementation of connected and unconnected Lookup transformations.
Let's discuss the properties of the Lookup transformation:
We have now seen all the details of Lookup transformations.