The Joiner Transformation in Informatica is used to perform SQL Joins. There are four types of Joins in Informatica: 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 Transformation in Informatica is exactly similar to SQL Inner. 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.
In this article, we will 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 will perform Normal Join.
The employees Table inside the Database is:
Department Table inside the
Joiner Transformation in Informatica Example
Before we start doing anything, First connect to the Informatica repository service. To connect with the Repository service, we have to provide the Admin Console credentials, so, Please provide the appropriate Username and Password and click on Connect button as shown below.
TIP: Here, you must provide the Admin Username and password specified while installing the Server.
Step 1: Create Source Definition
Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Joiner Transformation example, we use Department and Employee tables from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition
Step 2: Creating a Target Definition for Joiner
Please navigate to Target Designer and define the Target. In this example, we use the already created SQL table (Joiner Transformation in Informatica) as our target definition. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition
Step 3: Create Joiner Transformation in Informatica Mapping
In order to create a new mapping for Informatica Joiner Transformation, Please navigate to the Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below. Here, you must write a unique name for this mapping (m_Joiner_transformation) and click the OK button.
TIP: Please refer Mapping article to understand the steps involved in creating Mapping.
Next, Drag and drop the Employee and Department source definitions from the 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
To create Informatica Joiner Transformation, Please navigate to the Transformation menu in Menu Bar and select the Create.. option, as we have shown below.
Once you click on the Create.. option, Create Transformation window will open, as shown below. Please click on the arrow to select the required one from the drop-down list. Let us choose the Informatica Joiner transformation.
Next, Please specify the unique name for this Informatica Joiner Transformation (JNR_NORMAL) and click on Create button
Once you click the Create button, the Informatica Joiner transformation will add to the mapping designer. To perform Normal Join, the Joiner Transformation requires some data. So, we have to connect the Source definition with the transformation using Autolink.. option (or select and Drag the required fields).
Double click on the Informatica Joiner transformation to configure the Join conditions. From 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 it to a more meaningful name.
- Make Reusable: If you check mark this option, it will become a reusable transformation.
- Description: Please provide a valid description.
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 the scissors button deletes the unwanted columns.
- I: Columns that are checkmark under this section are the Input columns.
- O: The columns checkmark under this section are the Joiner transformation Output columns. If you are unchecked any column, then that column will not be available to load in a target table.
- M: Please checkmark the Column(s) you want to use as the Master table. It is always good practice to select the table holding the least records as the Master table. Here, we are selecting a Department table as our Master table.
NOTE: You don’t have to select every column in the Master table. Suppose you select One column from Employee. It will automatically the remaining columns.
The below screenshot will show you the list of available options in the Informatica Joiner Transformation Properties tab:
- Join Type: In this example, we perform Normal Join, selecting the Normal Join from the drop-down list.
- Sorted Input: If we are using the sorted data, then check mark this option.
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 names of the columns from the Master and detail list
Once you finish configuring the Informatica Joiner Transformation properties, Click OK to close the window.
Next, Drag and drop the target definition from the Targets folder to the mapping designer and connect the Informatica Joiner Transformation with the target definition. Please use the Autolink.. option to connect them.
Before we close the Mapping, Let us validate the Informatica Joiner Transformation mapping by going to the Mapping Menu bar and selecting the Validate option.
Step 4: Creating a Workflow
Once we finish creating the Mapping, we have to create the workflow. The PowerCenter Workflow Manager provides two approaches to creating a workflow.
In this Informatica Joiner Transformation example, we will create the Workflow manually. To create a new Workflow, Please navigate to Workflows Menu and select the Create option. It will open Create Workflow window as shown below. Please provide the unique name (wf_joiner_transformation) and leave the default settings.
Once we created the workflow, our next step is to create a session for joiner transformation in Informatica.
NOTE: We strictly recommend referring to the Workflow article to understand the steps involved in creating Workflow manually.
Step 4(a): Creating Session
There are two types of sessions:
- Non-reusable Session Task: Please refer to the Session article to understand the steps involved in it.
- Reusable Session Task: Please refer to the Reusable Session article to understand the steps involved in it.
For this Informatica Joiner Transformation example, we are going to create Non-reusable Session. In order to create Non-reusable Session, Please navigate to the 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 open. Here you have to select the mapping you want to associate with this session. For example, from the screenshot below, you can observe that we are selecting the mapping we created earlier (in Step 3).
Double click on the Session Task to configure it. Although we have to configure some common properties, we explain only Sources and target properties. We strictly recommend visiting the Session article to understand the remaining properties.
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 Source. We are using the Connection variable we created in our previous step, i.e., $Source.
Department Source for. Informatica Joiner Transformation
Within the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser, as shown below. Here, Our Department table comes from the Source, so we are using the Connection variable we created in our previous step, i.e., $Source.
From the below screenshot, you can observe that we assigned the $target variable to Connection Value. In addition, we changed the Target Load Type option from Bulk to Normal mode and checkmark the Truncate target table option to truncate the existing data from the destination table.
From the screenshot below, you can observe that the Informatica Joiner Transformation workflow is valid. Let’s start the Workflow by navigating to the Workflows menu and selecting the Start Workflow option.
Once you select the Start Workflow option, the PowerCenter Workflow monitor will open to monitor the workflow. For example, from the below screenshot, you can observe that our Informatica Joiner Transformation workflow executes without any errors.
Let us open the SQL Server Management Studio to check whether we successfully performed the Normal Join using the Joiner Transformation in Informatica.
