Create a Source Definition based on the database table used for the lookup
Add this Source Definition to the mapping
Connect this Source Definition to the Joiner Transformation as the master source
Connect the original source table to the Joiner as the detail source
Change the Join Type to Master Outer Join
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.
A Joiner can be used to emulate the lookup, but with certain limitations:
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.
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.
What can we do to improve this information (2000 or fewer characters)