Home

Joiner Transformation in Informatica IICS – IDMC

Active and Connected

Last Updated: 06 November 2023

By: Nataraj Virupaksham (Raj)

A Joiner transformation in IICS-IDMC can join data from two related Heterogeneous sources. Also, we can join homogeneous source tables.

Usage:

▹ You can use the Joiner transformation to join a flat file with a Relational table.
▹ To Join two heterogeneous sources using Joiner transformation, at least one common with common data required between two sources.
▹ You can create multiple join conditions.
▹ Joiner will support only equality operator (=) in join condition.
▹ When you link a transformation to the Joiner transformation, you connect it to the Master or Detail group. To improve job performance, connect the transformation that represents the smaller data set to the Master group.

▹ You can use the following join types in Joiner Transformation as shown below.

  1. Normal Join – Matching Records between Master and Detail source
  2. Master Outer Join – All Records from Detail source, Matching records from Master source
  3. Detail Outer Join – All records from Master source, Matching records from Detail source
  4. Full Outer Join – All records from Master source and Detail source.

▹ Master outer join in Joiner transformation is Equivalent to Right outer join in oracle.
▹ Detail outer join in Joiner transformation is Equivalent to Left outer join in oracle.
▹ Field name conflicts can occur when you join sources with matching field names. You can resolve the conflict in one of the following ways:

  • Create a field name conflict resolution.
  • Rename matching fields in an upstream transformation.
  • Pass data through an Expression transformation to rename fields.

Improve the performance of joiner Transformation:
▹ Take the source which has less numbers of records as master source in joiner TR in the mapping.
▹ Use sorter Input option and send the Sorted data to joiner to improve the performance.
▹ Join based on numerical columns
▹ Use Normal Join whenever possible.
▹ When sources are homogeneous tables, instead of using joiner, Use Source Transformation will improve the performance.

Join more than 2 sources:
▹ To join more than two sources in a mapping, you can use multiple Joiner transformations. You can join the output from the Joiner transformation with another source pipeline.
▹ To join N heterogeneous sources, N-1 Joiner Transformations are required.

Joiner transformation Cache:
There are two types of cache in Joiner Transformation

JOINER INDEX CACHE — it will store the ports in the joining condition
JOINER DATA CACHE — it will store the ports other than joining condition

Difference between Joiner transformation and Source Transformation in IICS -IMDC

Source Transformation Joiner Transformation
To join homogeneous tables only To join heterogeneous sources
Source transformation will support all types of joins which supported by source database Joiner will support only four type of joins
To join “n” source tables, only one source transformation is required To join “n” sources, “n-1” joiner transformations required
We can write user defined SQL queries There is no SQL query option in joiner
Source transformation have filter, sort and select distinct properties None of the properties available
Source query can support any operator Joiner will support only equality operator
Use source transformation to join whenever possible Join only when sources are heterogeneous
Source transformation is not having any cache Joiner is having index and data cache
Source transformation does not have sorter input option Joiner is having sorted input option

Leave a comment

Subscribe for material notification

Fill this form to get notify when we post new study materials on Raj Informatica.

Post Notification
Back to Top
Product has been added to your cart

Continue Reading Joiner Transformation in Informatica IICS – IDMC

Fill out this form to explore all our interview question sets.

Question-Popup