The Joiner transformation within Informatica Cloud (IICS) is a dynamic and linked transformation that facilitates merging data from two distinct data streams according to specified conditions and join types. One of its key advantages lies in its capability to merge data originating from diverse systems, making it particularly versatile for handling heterogeneous data sources.
Mapping- Joiner Transformation
If there are x sources, the number of Informatica Joiner transformations needed to combine the data is typically x-1
Properties of Joiner Transformation
In setting up a Joiner transformation, you specify Joiner properties across various tabs within the Properties panel.
- Incoming Fields tab: Set up the Master and Detail source fields, addressing any conflicts in field names if they arise.
- Join Condition tab: Set up the Join Type and Join Conditions to merge the data from the Master and Detail pipelines.
- Advanced tab: Adjust advanced settings such as Sorted Input from the Advanced tab.
Incoming Fields
The Joiner transformation comprises two default input groups – the Master group and the Detail group. When linking a transformation to the Joiner, you must connect it to either the Master or Detail group.
In execution, the Master source is cached in memory for joining. Hence, it’s advisable to choose the source with fewer records as the Master source. Subsequently, the Joiner transformation compares each row of the Master source from the cache with the Detail source.
If there are fields with identical names from both the Master and Detail groups, the cloud mapping designer will generate a Field Name Conflict error. You can resolve this conflict either by renaming the fields in the upstream transformation or by creating a separate field rule within the Joiner transformation. This rule allows you to Bulk Rename fields by appending a prefix or suffix to all incoming fields.
Types Of Join Conditions
The Joiner transformation facilitates four distinct types of joins.
- Normal Join: Retains only rows that meet the specified join conditions and discards those that do not match.
- Master Outer: Retains all rows from the Detail pipeline along with the corresponding matching rows from the Master pipeline, discarding any unmatched rows from the Master pipeline.
- Detail Outer: Retains all rows from the Master pipeline alongside the corresponding matching rows from the Detail pipeline, while discarding any unmatched rows from the Detail pipeline.
- Full Outer: Includes rows that meet the specified join conditions, while also incorporating all incoming data from both the Master and Detail pipelines.
The Joiner transformation does not consider null values when matching rows. To join rows containing null values, you can substitute null values with default values and then perform the join based on these default values.
Sorted Input
In the Advanced tab of the Joiner transformation, enabling the Sorted Input option can enhance performance. This setting indicates to the Joiner transformation that the data arriving from the Master and Detail pipelines is sorted. With sorted data, the Joiner doesn’t need to compare every Master row against all Detail source rows, reducing disk input and output.
Properties Of Joiner Transformation- Sorted Input enabled
Enabling the Sorted Input option in the Joiner transformation requires that the data passed to it is sorted. If the data provided to the Joiner transformation is not sorted, the mapping will throw an error.
How to improve the performance of the Joiner transformation?
By giving sorted data as input we can improve the performance of the Joiner transformation.
If data is unsorted, despite meeting the join condition in the initial rows, the Master row is compared against all Detail source rows because there may be additional rows in Detail that could fulfill the join condition.
When data is sorted, during comparison of a Master row with Detail rows, all Detail rows meeting the join condition are orderly arranged. After the join condition is satisfied, subsequent comparison of the same Master row with Detail rows not meeting the condition enables the Integration Service to recognize the absence of further matches. Consequently, it skips joining with subsequent rows and forwards the accumulated joined data to downstream transformations. This reduces disk I/O and enhances overall performance.
FAQ's
The Joiner transformation in Informatica IICS is a transformation used to merge data from two different data streams based on specified join conditions and types. It allows you to combine data from disparate sources within a mapping.
The Joiner transformation in IICS is specifically designed for merging data from different sources within a mapping, while other join types like database joins are used to combine data within the same source or database.
Yes, the Joiner transformation in IICS supports joining data from different sources, including databases, flat files, cloud applications, and more.
The Joiner transformation in IICS supports four types of joins: Inner join, Outer join, Full outer join, and Partial outer join.
Enabling the Sorted Input option in the Joiner transformation indicates that the incoming data from both the Master and Detail pipelines is sorted. This optimization reduces the need for exhaustive comparisons, resulting in improved performance.
If the data passed to the Joiner transformation is not sorted, the mapping will throw an error. Sorting the input data is crucial for the Joiner transformation to function correctly and efficiently.
The Joiner transformation in IICS does not match null values by default. To join rows containing null values, you can replace null values with default values and then perform the join based on these default values.
The Joiner transformation in IICS allows you to join data from different sources within the same mapping without the need for complex SQL queries or database operations. It offers flexibility, scalability, and ease of use for data integration tasks.
Field name conflicts in the Joiner transformation can be resolved by renaming the fields in the upstream transformation or by creating separate field rules within the Joiner transformation to rename fields with prefixes or suffixes.
Yes, you can use multiple Joiner transformations in a single mapping in IICS to merge data from multiple sources or perform multiple join operations within the same mapping.
