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.
How to Create Lookup Transformation
Following are the steps to create Lookup Transformation in Informatica 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
Select lookup transformation as the transformation
Enter transformation name “lkp_dept”
Select create option
Step 3 – This will open lookup table window, in this window
Select source button
Select DEPT table
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
Select condition tab
Set the condition column to DEPTNO = DEPTNO1
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.
How to Create Reusable Transformation
Following are the steps to create Reusable Transformation 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
Select Transformation tab in the window Select the check box to make transformation reusable Select yes in the confirmation window Select OK in the transformation properties window.
This will make the transformation reusable.
