Joiner Transformations

Joiner transformations connected and active transformation.

Joiner transformation is an example for horizontal merging of tables..

Used to join source data from two related heterogeneous sources residing in different locations or file systems. Or, we can join data from the same source.

If we need to join 'n' tables, then we need 'n-1' Joiner transformations.

The Joiner transformation requires atleast two sources with at least one matching port. The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources.

Joiner can't be used in following conditions:

• Either input pipeline contains an Update Strategy transformation.
• We connect a Sequence Generator transformation directly before the Joiner transformation.

Workig with the Joiner transformation:

1. In the Mapping, import create Joiner Transformation.
2. Edit Joiner Transformation -> Condition Tab
3. Add condition
1. In Joiner, one table is called as MASTER and other as DETAIL.
2. MASTER table is always cached. We can make any table as MASTER.
3. Edit Joiner Transformation -> Ports Tab -> Select M for Master table.
4. We can add as many conditions as needed.
5. Only = operator is allowed.
6. Set Master in Ports tab.
7. Mapping -> Validate
8. Repository -> Save.
9. Create Session and Workflow. Run the workflow and see the data in the target table.

Points to remember:

• Table with less number of rows should be made MASTER to improve performance. Because
• When the PowerCenter Server processes a Joiner transformation, it reads rows from both sources concurrently and builds the index and data cache based on the master rows. So table with fewer rows will be read fast and cache can be made as table with more rows is still being read.
• The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.
• If we join Char and Varchar datatypes, the PowerCenter Server counts any spaces that pad Char values as part of the string. So if you try to join the following:
Char (40) = “abcd” and Varchar (40) = “abcd”

Then the Char value is “abcd” padded with 36 blank spaces, and the PowerCenter Server does not join the two fields because the Char field contains trailing spaces.


The Joiner transformation does not match null values.
Any one of the pipeline contains Sequential transformation, then the Joiner transformation rejects the records.