Informatica Tips

HOW TO Use a Joiner transformation instead of Lookup Transformation

To configure a Joiner transformation to emulate a Lookup transformation in a PowerCenter mapping do the following:

1.Create a Source Definition based on the database table used for the lookup.

2.Add this Source Definition to the mapping.

3.Connect this Source Definition to the Joiner transformation as the master source

4.Connect the original source table to the Joiner as the detail source.

5.Change the Join Type to Master Outer Join .

6.Configure the join condition.
The join condition must use the same ports as the lookup condition.

Limitations

A Joiner can be used to emulate the lookup, but with certain limitations:

  • Only equi- conditions are supported.
  • Duplicate matching is not supported.
  • Joiner will always cache, Non-Cached is not an option.

The Lookup will always return one value when it can find data and you can choose to get the first value or last value using the lookup property Lookup Policy on Multiple Match .  However, as the Joiner does not have that property, you will have to ensure that that all key values only occur once. If this is not ensured then you will be allowing duplicates of your input data flow into the mapping.

Scenario

Very often you might have to compare incoming data to previous loaded data in the warehouse. Often the target table is loaded into a lookup, comparisons are made and actions are taken. This is not always the most efficient way.  Searching for a value in a cache with millions of rows will take longer than searching for a value in a cache with a few hundred rows which can result in poor performance.  You might want to keep the caches small, however, a data warehouse only gets bigger and performance goes down. The input data often has the same size for each delivery as deltas are often created by some smart mechanism and how would you cache your input data instead of target data and make comparisons that way? How will you make the cache to be smaller and yet increase the performance?
You can put your input data in a lookup cache and read all rows from the warehouse table as a source, then make comparisons and act. However, this cannot detect new data because the new data will never be looked up because it does not exist in the warehouse table.
One way to address this issue is to use the Joiner transformation. The Lookup transformation is a specialized Joiner transformation and when you use the outer join option the Joiner can operate similar to a Lookup and if you use the sorted input option a very small cache is created.