Lookup and Re-usable transformation in Informatica

Lookup transformation provides the feature to lookup matching values in a table based on the values in source data. Basically, it's a kind of join operation in which one of the joining table is the source data, and the other joining table is the lookup table.

In previous topics, we used joiner transformation to join "emp" and "dept" table to bring department names.

In this section, we will implement the same using lookup transformation.

Step 1 – Create a new mapping with EMP as source and EMP_DEPTNAME as target

Step 2 – Create a new transformation using transformation menu then

1. Select lookup transformation as the transformation
2. Enter transformation name "lkp_dept"
3. Select create option

Step 3 – This will open lookup table window, in this window

1. Select source button
2. Select DEPT table
3. Select Ok Button

Step 4 - Lookup transformation will be created with the columns of DEPT table, now select done button

Step 5 - Drag and drop DEPTNO column from source qualifier to the lookup transformation, this will create a new column DEPTNO1 in lookup transformation. Then link the DNAME column from lookup transformation to the target table.

The lookup transformation will lookup and return department name based upon the DEPTNO1 value.

Step 6 – Double click on the lookup transformation. Then in the edit transformation window

1. Select condition tab
2. Set the condition column to DEPTNO = DEPTNO1
3. Select Ok Button

Step 7 – Link rest of the columns from source qualifier to the target table

Now, save the mapping and execute it after creating the session and workflow. This mapping will fetch the department names using lookup transformation.

The lookup transformation is set to lookup on dept table. And the joining condition is set based on dept number.

Reusable Transformation

A normal transformation is an object that belongs to a mapping and can be used inside that mapping only. However, by making a transformation reusable it can be re-used inside several mappings.

For example, a lookup transformation which fetches employee details based on employee number can be used at multiple mappings wherever employee details are required.

By using reusable transformation, it reduces the overwork of creating same functionality again.

To make a transformation reusable –

Step 1 – Open the mapping which is having the transformation, here we are making the rank transformation reusable.

Step 2 – Double click on the transformation to open edit transformation window. Then

1. Select Transformation tab in the window
2. Select the check box to make transformation reusable
3. Select yes in the confirmation window
4. Select OK in the transformation properties window.

This will make the transformation reusable.