[Informatica] Joiner Transformation

Joiner Transformation
:: Transformation type ::

Joiner transformation can be used to join source data from two same or related heterogeneous sources having a common joining key port.
The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources. You can add more Joiner transformation to the mapping if you need to join more than two sources. The joining ports must have same datatype.

:: Joining Condition ::
The join condition contains ports from both input sources. Joiner produces result sets based on the join type, condition and input data sources.
During a session, the PowerCenter Server compares each row of the master source against the detail source. The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process. To improve performance, designate the source with the smallest count of distinct values as the master.
You can use one or more ports from the input sources of a Joiner transformation in the join condition. However, the performance decreases with the increase in number of Joining ports. Also, the PowerCenter Server compares the ports in the joining condition in the order they appear in the Joiner. Therefore, the port order is critical while performing Joins.

:: Sorted Input ::
While working with large data sets, one can greatly improve the performance by sorting the input before the Joiner. This greatly reduces the disk input output. Also, remember to check the Sorted Input Check Box in the Joiner transformation.

:: Performance Improvement and Troubleshooting ::
- The Joiner transformation DOES NOT match NULL values. PowerCenter Server does not consider NULL values in comparison of ANY type and does not join the two rows. In case you still want to join rows will NULL values, you must default the NULL values to some other non-NULL value.
- Performing joins at database level should always be preferred as its cheaper from memory point of view and improves performance.
- By default, when you add ports to a Joiner transformation, the ports from the first source display as detail sources. Adding the ports from the second transformation sets them as master sources. You can change this in Properties tab by clicking on Edit transformation.
The join types are explained in detail in my previous post (link here).

1. The following example shows the Master and Detail table in Joiner Transformation.

2. The follwoing example shows the use of Sorted Input in Joiner Transformation

3. The following example shows Joining Condition for the Joiner.