Joiner Transformation in Informatica

The Joiner Transformation in Informatica is used to perform SQL Joins. There are four types of Joins in Informatica, and they Inner Join (Normal Join), Master Outer Join, Detail Outer Join and Full Outer Join. Joiner Transformation in Informatica is very useful to load data into the Dimension tables in Data Warehouse.

Normal Join: The Normal Join in Informatica is exactly similar to SQL Inner Join. It returns the records (or rows) present in both tables If there is at least one match between columns. Let us see the visual representation of the Normal Join for better understanding.

Normal Join in Informatica

In this article, we are going to perform Normal Join on two SQL tables using Joiner Transformation in Informatica. Before we start designing the Mapping let us look at our two source tables on which we are going to perform Normal Join using Joiner Transformation.

Employees Table inside the [Informatica Source] Database is:

Joiner Transformation in Informatica Source 1

Department Table inside the

Joiner Transformation in Informatica Source 2

Joiner Transformation in Informatica Example

Before we start doing anything, First connect to Informatica repository service. In order to connect with Repository service we have to provide the Informatica Admin Console credentials so, Please provide the appropriate Username and Password and click on Connect button as shown below.

TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.

Joiner Transformation in Informatica

Step 1: Create Source Definition for Joiner Transformation in Informatica

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we are using Department and Employee tables from SQL Server database as our source definitions. Please refer Database Source in Informatica to understand the steps involved in creating source definition

Joiner Transformation in Informatica 1

Step 2: Creating a Target Definition for Joiner Transformation in Informatica

Please navigate to Target Designer and define the Target. In this example, we are using the already created SQL table (Joiner Transformation in Informatica) as our target definition. Please refer Create Informatica Target table using Source Definition to understand the steps involved in creating a target definition

Joiner Transformation in Informatica 2

Step 3: Create Joiner Transformation in Informatica Mapping

In order to create a new mapping, Please navigate to Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below. Here, you have to write a unique name for this mapping (m_Joiner_transformation) and click OK button.

TIP: Please refer Informatica Mapping article to understand the steps involved in creating Mapping.

Joiner Transformation in Informatica 3

Next, Drag and drop the Employee and Department source definitions from Sources folder to the mapping designer. Once you drag the source, PowerCenter Designer will automatically create the default transformation called source qualifier.

Step 3(a): Creating Joiner Transformation in Informatica

In order to create Joiner transformation in Informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below.

Joiner Transformation in Informatica 4

Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please click on the arrow to select the required transformation from drop-down list. Let us select the Informatica Joiner transformation

Joiner Transformation in Informatica 6

Next, Please specify the unique name for this transformation (JNR_NORMAL) and click on Create button

Joiner Transformation in Informatica 7

Once you click on the Create button, the Informatica Joiner transformation will be added to the mapping designer. In order to perform Normal Join, Informatica Joiner Transformation requires some data. So, we have to connect the Source definition with the transformation using Autolink.. option (or select and Drag required fields).

Joiner Transformation in Informatica 8

Double click on the Informatica Joiner transformation to configure the Join conditions. From the below screenshot you can see the list of available properties in the Transformation tab:

  • Select Transformation: By default, it will select the transformation you clicked on.
  • Rename: This button will help you to rename the joiner transformation to a more meaningful name.
  • Make Reusable: If you check mark this option then, this transformation will become reusable transformation.
  • Description: Please provide a valid description of this transformation.
Joiner Transformation in Informatica 9

Below screenshot will show you the list of available options in the Informatica Joiner Transformation Ports tab:

  • Port Name: List of available column names. By clicking the New column button you can add new columns and by clicking the scissors button you can delete the unwanted columns.
  • I: Columns that are check-marked under this section are the Joiner transformation Input columns.
  • O: Columns that are check-marked under this section are the Joiner transformation Output columns. If you unchecked any column then, that column will not be available to load in a target table.
  • M: Please checkmark the Column(s) that you want to use as Master table. It is always good practice to select the table holding the least records as Master table. Here, we are selecting a Department table as our Master table.

NOTE: You don’t have to select each and every column present in the Master table. If you select One column from Employee then Informatica will automatically the remaining columns.

Joiner Transformation in Informatica 10

Below screenshot will show you the list of available options in the Properties tab:

  • Join Type: In this example, we are performing Normal Join so, we are selecting the Normal Join from the drop-down list.
  • Sorted Input: If we are using the sorted data then check mark this option.
Joiner Transformation in Informatica 11

Within the Condition tab, we have to specify the condition. In this example, we are going to perform Normal Join on Department and Employee table based on the condition Department. id = Employee.Depart Id. So, click on the New button (beside scissors) to create a new condition and select the columns names from Master and detail list

Joiner Transformation in Informatica 12

Once you finish configuring the Transformation properties, Click OK to close the transformation window.

Next, Drag and drop the target definition from Targets folder to the mapping designer and connect the Joiner Transformation with the target definition. Please use the Autolink.. option to connect them.

Joiner Transformation in Informatica 13

Before we close the Mapping, Let us validate the Informatica Joiner Transformation mapping by going to Mapping Menu bar and selecting the Validate option.

Step 4: Creating a Workflow for Joiner Transformation in Informatica

Once we finish creating the Mapping we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.

In this Informatica Joiner Transformation example, we will create the Workflow manually. In order to create new Informatica Workflow, Please navigate to Workflows Menu and select the Create option. This will open Create Workflow window as shown below. Please provide the unique name (wf_joiner_transformation) and leave the default settings.

Joiner Transformation in Informatica 14

Once we created the workflow, our next step is to create a session for joiner transformation in informatica.

NOTE: We strictly recommend to refer Informatica Workflow article to understand the steps involved in creating Workflow manually.

Step 4(a): Creating Informatica Joiner Transformation Session

There are two types of sessions in Informatica:

For this Informatica Joiner Transformation example, we are going to create Non-reusable Session. In order to create Non-reusable Session in Informatica, Please navigate to Tasks Menu and select the Create option to open the Create Task window. Here you have to select the Session as Task type (default) and enter a unique name for the session.

Once you click on the Create button, a new window called Mappings will be opened. Here you have to select the mapping you want to associate with this session. From the below screenshot you can observe that we are selecting the mapping that we created earlier (in Step 3).

Joiner Transformation in Informatica 15

Double click on the Session Task to configure it. Although we have to configure some common properties, we are explaining only Sources, targets properties. We strictly recommend visiting Session in Informatica article to understand the remaining properties.

Informatica Joiner Transformation Employee Source

Within the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser as shown below. Here, Our Employee table is coming from the Informatica Source, we are using the Connection variable that we created in our previous step i.e., $Source

Joiner Transformation in Informatica 16
Informatica Joiner Transformation Department Source

Within the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser as shown below. Here, Our Department table is also coming from the Informatica Source so we are using the Connection variable that we created in our previous step i.e., $Source

Joiner Transformation in Informatica 17

From the below screenshot you can observe that we assigned the $target variable to Connection Value, we changed the Target Load Type option from Bulk to Normal mode and check marked the Truncate target table option to truncate the existing data from the destination table.

Joiner Transformation in Informatica 18

From the below screenshot you can observe that the Informatica Joiner Transformation workflow is a valid one. Now, Let us start the Workflow by navigating to Workflows menu and selecting the Start Workflow option.

Joiner Transformation in Informatica 19

Once you select the Start Workflow option, Informatica PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our Informatica Joiner Transformation workflow is executed without any errors.

Joiner Transformation in Informatica 20

Let us open the SQL Server Management Studio to check whether we successfully performed the Normal Join using the Joiner Transformation in Informatica.

Joiner Transformation in Informatica 21