Lookup Transformation in Informatica & Re-usable Transformation Example

What is Lookup Transformation?

Lookup transformation is a passive transformation used to look up a source, source qualifier, or target to get the relevant data. Basically, it's a kind of join operation in which one of the joining tables 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 –

Step1 – 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.