Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(7 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Use a Joiner Transformation instead of Lookup Transformation
Solution
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.

If the data in the sources are sorted then use Sorted Input option.

More Information
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.

Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Developer
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:8/18/2008 11:20 PMID:19304
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)