The Detail Outer Join in Informatica is used to return all the existing records from the Master table and only matching records from the Detail table.
For better understanding, let us see the visual representation of the Detail Outer Join in Informatica.
In this article, we will perform Informatica Informatica Detail Outer Join on two SQL tables using Joiner Transformation. Before we start designing the Mapping, let us look at our two source tables on which we will perform Detail Outer Join in Informatica.
The Employees Table inside the Database is:
Department Table inside the Database is:
Detail Outer Join in Informatica Example
Before we start doing anything, First connect to the repository service with your Admin Console credentials.
TIP: Please refer to Normal Join, Master Outer Join, and Full Outer Join articles to understand the remaining Joins in Informatica.
Step 1: Creating Source Definition
Once you connect successfully, navigate to Source Analyzer and define your Sources. In this Informatica detail outer join example, we use Department and Employee tables from the SQL Server database as our source definitions. Please refer to Database Source for the steps in creating a source definition.
Step 2: Creating a Target Definition
Please navigate to Target Designer and define the Target. We use the already created SQL table (Detail Outer Join in Informatica) as our target definition in this example. Please refer Create Target table using Source Definition to understand the steps in creating a target definition.
Step 3: Creating Informatica Detail Outer Join Mapping
For this Informatica detail outer join example, we created the m_Detail_Outer mapping. Please refer Mapping article to understand 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 Detail Outer Join in Informatica
In order to create a Detail Outer Join transformation in Informatica, Please navigate to the Transformation menu in Menu Bar and select the Create.. option as shown below.
Once you click on the Create.. option, the Create Transformation window will open, as shown below. Please select the Joiner transformation from a drop-down list, specify the unique name (JNR_DETAIL) for this transformation, and click on Create button.
Once you click the Create button, the Joiner transformation will be added to the mapping designer. In order to perform Detail Outer Join, Joiner Transformation requires some data, so we have to connect the Source definition with the Informatica transformation using Autolink.. option (or select and Drag required fields).
Double-click on the Joiner transformation to configure the join conditions. From the below screenshot you can see the list of available options in the Ports tab:
- M: Please checkmark the Column(s) you want to use as the Master table. It is always good practice to select the table with 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 each and every column present in the Master table. If you select One column from Employee, Informatica will automatically add the remaining columns.
The below screenshot will show you the list of available options in the Properties tab:
- Join Type: In this example, we perform Informatica Detail Outer Join. So, we are selecting the Detail Outer Join from the drop-down list.
- Sorted Input: If we use the sorted data, check mark this option.
Within the Condition tab, we have to specify the condition. In this example, we will perform Informatica Detail Outer 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 Joiner Transformation properties, Click OK to close the transformation window.
Next, Drag and drop the target definition (Detail Outer Join in Informatica) from the Targets folder to the mapping designer and connect the Joiner Transformation with the target definition. Please use the Autolink.. option to connect them.
Before we close the Mapping, Let us validate the mapping by going to the Mapping Menu bar and selecting the Validate option.
Step 4: Creating a Workflow for Detail Outer Join in Informatica
Once we finish creating the Mapping, we must create the workflow. PowerCenter Workflow manager provides two approaches to creating a workflow.
In this Informatica Detail Outer Join example, we will create the Workflow manually. In order to create a new 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_Detail_Outer) and leave the default settings.
Once we have created the workflow, our next step is to create a session task for our mapping.
NOTE: We strictly recommend to refer Workflow article to understand the steps involved in creating a Workflow manually.
Step 4(a): Creating Session for Informatica Detail Outer Join
There are two types of sessions:
For this Informatica Detail Outer Join example, we will 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 (S_Detail_Outer) for the session.
Once you click the Create button, a new Mappings window will be opened. You must select the mapping you want to associate with this session. From the screenshot below, you can observe that we are selecting the mapping (m_Detail_Outer) we created earlier (in Step 3).
Double-click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we are explaining only a few properties. We strictly recommend visiting the Session article to understand the remaining properties.
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.
From the below screenshot, you can observe that the Informatica Detail Outer Join workflow is a valid one. Let us 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 be opened to monitor the workflow. From the screenshot below, you can observe that our Informatica Detail Outer Join workflow is executed without errors.
Let us open the SQL Server Management Studio to check whether we successfully performed the Detail Outer Join in Informatica using the Joiner Transformation.
