Lookup Transformation in Informatica (Example)

By : David Carter David Carter
Hours Updated

⚡ Smart Summary

Lookup transformation in Informatica reads a relational table, flat file, view or synonym and returns the matching values for each incoming row, working as a join in which one side is the lookup source.

  • 🔎 Condition drives the match: The Condition tab holds the comparison, such as DEPTNO = DEPTNO1, that decides which lookup row is returned.
  • 🧪 Worked example: Seven Mapping Designer steps fetch department names from DEPT into an EMP_DEPTNAME target without a Joiner.
  • 🔌 Connected or unconnected: A connected lookup sits in the pipeline, while an unconnected one is called from an expression with the :LKP qualifier.
  • ⚖️ Passive by default: Returning a single row keeps the transformation passive, and returning all matching rows makes it active.
  • ♻️ Reusable objects: Ticking Make Reusable turns one transformation into an object several mappings can share.
  • ⚠️ One-way promotion: A transformation promoted to reusable cannot be demoted, so the confirmation prompt deserves a careful read.
Read More

Lookup Transformation in Informatica

What is Lookup Transformation?

Lookup transformation is a transformation used to look up a source, Source Qualifier, or target to get the relevant data. Basically, it is 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. The lookup source itself can be a relational table, a view, a synonym or a flat file.

The transformation is passive while it returns a single row for each incoming row. From PowerCenter 9.x onward it can also be created to return every row that matches the condition, and in that mode it is an active transformation. The choice is made when the transformation is created and cannot be switched afterwards.

There are two ways to place it in a mapping, and the difference decides how the returned value reaches the rest of the pipeline.

Aspect Connected lookup Unconnected lookup
Position Sits inside the pipeline and receives values from the previous transformation Sits outside the pipeline and is called from an expression
How it is called Rows flow into its input ports Called with the :LKP reference qualifier
Values returned Several output ports can be linked downstream One return value goes back to the calling expression
Typical use Every row needs the looked-up columns The lookup is only needed for some rows, or the same lookup is called from several places

In previous topics, we used the Joiner transformation to join the “emp” and “dept” tables 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. The Mapping Designer canvas then holds the source, its Source Qualifier and the target definition.

Mapping Designer canvas with the EMP source, its Source Qualifier and the EMP_DEPTNAME target

Step 2) Create a new transformation using the Transformation menu, then in the Create Transformation window:

  1. Select Lookup transformation as the transformation
  2. Enter transformation name “lkp_dept”
  3. Select create option

Create Transformation window with Lookup Transformation selected and the name lkp_dept entered

Step 3) This will open the lookup table window. In this window:

  1. Select source button
  2. Select DEPT table
  3. Select OK button

Select Lookup Table dialog with the Source button chosen and the DEPT table highlighted

Step 4) Lookup transformation will be created with the columns of DEPT table, now select the done button. The lkp_dept object now carries DEPTNO, DNAME and LOC as lookup ports.

Lookup transformation lkp_dept created with the DEPT table columns as lookup ports

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 look up and return department name based upon the DEPTNO1 value. DEPTNO1 is the input port carrying the employee department number, while DEPTNO remains the port read from the DEPT lookup table.

DEPTNO linked from the Source Qualifier into lkp_dept and DNAME linked to the EMP_DEPTNAME target

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

Condition tab of the Edit Transformations window with the condition DEPTNO = DEPTNO1 entered

Step 7) Link rest of the columns from Source Qualifier to the target table.

Remaining Source Qualifier columns linked to the EMP_DEPTNAME 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 look up on the DEPT table, and the joining condition is set based on department number. If more than one DEPT row could satisfy the condition, the Lookup Policy on Multiple Match property on the Properties tab decides which value is returned.

RELATED ARTICLES

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 reused inside several mappings.

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

By using a reusable transformation, it reduces the overwork of creating the same functionality again. Every mapping holds an instance of the one stored object, so a change made to the reusable transformation reaches all of them.

How to Create Reusable Transformation

Following are the steps to create a reusable transformation.

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

Mapping opened in the Designer with the Rank transformation that is about to be made reusable

Step 2) Double click on the transformation to open the 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.

Transformation tab of the Edit Transformations window with the Make Reusable check box selected

This will make the transformation reusable. The confirmation window in the third item is worth reading before it is accepted: promoting a transformation to reusable is a one-way change in the Designer, and the object cannot be demoted afterwards. Where a single mapping needs its own copy, drag the reusable object onto the canvas while holding the Ctrl key to create a non-reusable instance instead.

FAQs

A cache loads the lookup source into memory once so each row is matched locally instead of querying the database. Caching suits small or repeatedly read lookup sources; an uncached lookup queries the source for every incoming row.

Use a Lookup when one side is a small reference table and only a few columns are needed, especially when the same reference is read from several places. A Joiner suits a genuine join of two large heterogeneous pipelines.

The Lookup Policy on Multiple Match property decides. It can report an error, or return the first, last or any matching value. Choosing to return all matching values makes the transformation active and must be set at creation time.

Usually because the lookup source is large. An uncached lookup queries the database once per row, while a cached one pays the build cost up front. Removing unused lookup ports and narrowing the lookup query both help.

Machine learning is used in modern data integration tooling to suggest column matches between a source and a reference table and to flag lookups that dominate session runtime. Every suggestion still needs checking against the real data.

Copilot assists with the text around the mapping rather than the Designer canvas: lookup SQL overrides, parameter files, and scripts that run workflows. It cannot see the repository, so port names and conditions must be verified by hand.

A reusable transformation is a single stored object shared by several mappings. A mapplet is a reusable set of transformations, with input and output groups, that packages a whole piece of logic rather than one step.

No row in the lookup source satisfied the condition for those input rows. Check for trailing spaces, case differences and data type mismatches between the two ports, since the comparison is exact.

Summarize this post with: