Router transformation is a lively and connected transformation, and it’s almost like the filter transformation, which is employed to filter the source data.

In a Router transformation, Data Integration is employed as a filter condition to filter each row of incoming data. It checks the conditions of every user-defined group before processing the default group.

If a row connects quite one group filter condition, Data Integration passes the row multiple times. we will drop rows that don’t meet any of the conditions to a default output group.

If we’d like to see an equivalent input file supported multiple conditions, then we use a Router transformation during a mapping rather than creating multiple Filter transformations.

The following table compares the Router transformation to the Filter transformation:

Options	Router	Filter
Conditions	Test for multiple conditions in a single Router transformation	Check for one condition per Filter transformation
Handle	rows that don’t meet the condition	Route rows to the default output group or drop rows that do not meet the condition	Drop rows that do not meet the condition
Incoming data	The process once with one Router transformation	Process in each Filter transformation

For example, when filtering the dataform rollno =20, we will also get those records where rollno isn’t adequate to 20. So, router transformation gives multiple output groups, and every output group can have its filter condition.

Also, there’s a default group, and this default group has record sets that do not satisfy any of the group conditions.

For example, if we’ve created two groups for the filter conditions rollno=20 & rollno=30 respectively, then those records which aren’t having rollno 20 and 30 are going to be passed into this default group.

The data which is rejected by the filter groups are going to be collected by this default group, and sometimes there are often a requirement to store these rejected data. during this way, the default output group are often useful.

To allow multiple filter conditions, the router transformation provides a gaggle option.

There may be a default input group that takes input file .
There is additionally a default output group that contains all those data which aren’t gone by any filter condition.
For every filter condition, an output group is made in router transformation. we will connect different targets to those different groups.

Creating Router Transformation

Follows the subsequent steps to make the router transformation, such as:

Step 1: Create a mapping having source “STUD” and target “STUD_TARGET.”

Step 2: Then within the mapping

Select Transformation menu.
Click on the Create

Step 3: within the create transformation window

Select router transformation
Enter a reputation for the transformation “rtr_rollno_20”
and click on on the Create

Step 4: The router transformation are going to be created within the mapping, select done option within the window.

Step 5: Drag and drop all the columns from Source qualifier to router transformation.

Step 6: Double click on the router transformation, then within the transformation property of it

Select the group tab.
Enter the group name rollno_30.
Click on the group filter condition.

Step 7: within the expression editor, enter the filter condition rollno=30 and choose the OK button.

Step 8: Click on the OK button within the group window.

Step 9: Connect the ports from the group rollno_30 of router transformation to focus on table ports.

Now, once we execute this mapping, the filtered records will get loaded into the target table.

Joiner Transformation

Joiner transformation is a lively and connected transformation. It provides the choice of making joins within the Informatica. By using the joiner transformation, the created joins are almost like the joins in databases.

The joiner transformation is employed to hitch two heterogeneous sources. The joiner transformation joins sources on the idea of a condition that matches one or more pairs of columns between the 2 sources.

The two input pipelines include a master and a detail pipeline. we’d like to hitch the output of the joiner transformation with another source to hitch quite two sources. And to hitch n number of sources in mapping, we’d like n-1 joiner transformations.

In joiner transformation, there are two sources which we are using for joins, such as:

Master Source
Detail Source

In the properties of joiner transformation, we will select which data source are often a Master source and which source are often a detail source.

During execution, the master source is cached into the memory for joining purpose. So it’s necessary to pick the source with less number of records because the master source.

Configuring Joiner Transformation

In Informatica, we configure the subsequent properties of joiner transformation, such as:

Case-Sensitive String Comparison: the mixing service uses this feature once we are performing joins on string columns. By default, the case sensitive string comparison option is checked.
Cache Directory: Directory want to cache the master or detail rows. The default directory path is $PMCacheDir. we will override this value also .
Join Type: the sort of join to be performed as Master Outer Join, Detail Outer Join, Normal Join, or Full Outer Join.
Tracing Level: it’s wont to track the extent of tracing within the session log file.
Joiner Data Cache Size: It tells the dimensions of the data cache. And Auto is that the default value of the data cache size.
Joiner Index Cache Size: It tells the dimensions of the index cache. And Auto is that the default value of the index cache size.
Sorted Input: this feature is employed when the input file is in sorted order. And it gives better performance.
Master Sort Order: It gives the type order of the master source data. If the master source data is sorted in ascending order, then we elect Ascending. we’ve to enable the Sorted Input option if we elect Ascending. And Auto is that the default value for this feature .
Transformation Scope: we will select the transformation scope as All Input or Row.

Types of Joins

In Informatica, the subsequent joins are often created using joiner transformation, such as:

Master outer join

In Master outer join, all records from the Detail source are returned by the join, and only matching rows from the master source are returned.

Detail outer join

In detail outer join, only matching rows are returned from the detail source, and every one rows from the master source are returned.

Full outer join.

In full outer join, all records from both the sources are returned. Master outer and Detail outer joins are like left outer joins in SQL.

Normal join

In normal join, only matching rows are returned from both the sources.

Example

In the following example, we’ll join emp and dept tables using joiner transformation within the following steps:

Step 1: Create a replacement target table EMP_DEPTNAME within the database using the below script and import the table in Informatica targets.

Step 2: Create a replacement mapping and import source tables “EMP” and “DEPT” and target table, which we created within the previous step.

Step 3: From the transformation menu, select create option and,

Select joiner transformation
Enter transformation name “jnr_emp_dept”
Select create option

Step 4: Drag and drop all the columns from both the source qualifiers to the joiner transformation.

Step 5: Double click on the joiner transformation, then within the edit transformation window:

Select the condition tab.
Click on the add new condition icon.
Select dept no in master and detail columns list.

Step 6: Then, within the same window:

Select the properties tab.
Select normal Join as join type.
Click on the OK button.

For performance optimization, we assign the master source to the source table pipeline, which has less number of records. To perform this task:

Step 7: Double click on the joiner transformation to open the edit properties window, and then

Select the ports tab.
Select any column of a specific source that you simply want to form a master.
Click on the OK button.

Step 8: Link the relevant columns from the joiner transformation to the target table.

Now save the mapping and execute it after creating a session and workflow for it. The join are going to be created using Informatica joiner, and relevant details are going to be fetched from both the tables.

Sorted Input

When both the Master and detail source are sorted on the ports laid out in the join condition, then use the sorted input option within the joiner properties tab.

We can improve the performance by using the sorted input option because the integration service performs the join by minimizing the amount of disk IOs. It gives excellent performance once we are working with large data sets.

Here are some steps to configuring the sorted input option, such as:

Sort the master and detail source either by using the source qualifier transformation or sorter transformation.
Sort both the source on the ports to be utilized in join conditions either in ascending or descending order.
Specify the Sorted Input option within the joiner transformation properties tab.

Blocking Transformation

The joiner Transformation is named because the blocking transformation. the mixing service blocks and unblocks the source data counting on whether the joiner transformation is configured for sorted input or not.

Unsorted Joiner Transformation

In the case of unsorted joiner transformation, the mixing service first reads all the master rows before it reads the detail rows.

The integration service blocks the detail source while it caches all the master rows. Once it reads all the master rows, then it unblocks the detail source and understands the small print rows.

Sorted Joiner Transformation

The blocking logic may or might not possible just in case of sorted joiner transformation. the mixing service uses blocking logic if it can do so without blocking all sources within the target load order group. Otherwise, it doesn’t use blocking logic.

How to Improve Joiner Transformation Performance?

Below are some details to enhance the performance of a joiner transformation, such as:

If possible, perform joins during a database. Performing joins during a database is quicker than performing joins during a session.
We can improve the session performance by configuring the Sorted Input option within the joiner transformation properties tab.
Specify the source with fewer rows and with fewer duplicate keys because the Master and therefore the other source as detail.

Limitations of Joiner Transformation

Here are the subsequent limitations of joiner transformation, such as:

We cannot use joiner transformation when the input pipeline contains an update strategy transformation.
We cannot connect a sequence generator transformation on to the joiner transformation.

So, this brings us to the end of blog. This Tecklearn ‘Router and Joiner Transformation in Informatica’ blog helps you with commonly asked questions if you are looking out for a job in Informatica. If you wish to learn Informatica and build a career in Datawarehouse and ETL domain, then check out our interactive, Informatica Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/informatica-training-and-certification/

Informatica Training

About the Course

Tecklearn’s Informatica Training will help you master Data Integration concepts such as ETL and Data Mining using Informatica PowerCenter. It will also make you proficient in Advanced Transformations, Informatica Architecture, Data Migration, Performance Tuning, Installation & Configuration of Informatica PowerCenter. You will get trained in Workflow Informatica, data warehousing, Repository Management and other processes.

Why Should you take Informatica Training?

Informatica professionals earn up to $130,000 per year – Indeed.com
GE, eBay, PayPal, FedEx, EMC, Siemens, BNY Mellon & other top Fortune 500 companies use Informatica.
Key advantages of Informatica PowerCenter: Excellent GUI interfaces for Administration, ETL Design, Job Scheduling, Session monitoring, Debugging, etc.

What you will Learn in this Course?

Informatica PowerCenter 10 – An Overview

Informatica & Informatica Product Suite
Informatica PowerCenter as ETL Tool
Informatica PowerCenter Architecture
Component-based development techniques

Data Integration and Data Warehousing Fundamentals

Data Integration Concepts
Data Profile and Data Quality Management
ETL and ETL architecture
Brief on Data Warehousing

Informatica Installation and Configuration

Configuring the Informatica tool
How to install the Informatica operational administration activities and integration services

Informatica PowerCenter Transformations

Visualize PowerCenter Client Tools
Data Flow
Create and Execute Mapping
Transformations and their usage
Hands On

Informatica PowerCenter Tasks & Workflows

Informatica PowerCenter Workflow Manager
Reusability and Scheduling in Workflow Manager
Workflow Task and job handling
Flow within a Workflow
Components of Workflow Monitor

Advanced Transformations

Look Up Transformation
Active and Passive Transformation
Joiner Transformation
Types of Caches
Hands On

More Advanced Transformations – SQL (Pre-SQL and Post-SQL)

Load Types – Bulk, Normal
Reusable and Non-Reusable Sessions
Categories for Transformation
Various Types of Transformation – Filter, Expression, Update Strategy, Sorter, Router, XML, HTTP, Transaction Control

Various Types of Transformation – Rank, Union, Stored Procedure

Error Handling and Recovery in Informatica
High Availability and Failover in Informatica
Best Practices in Informatica
Debugger
Performance Tuning

Performance Tuning, Design Principles & Caches

Performance Tuning Methodology
Mapping design tips & tricks
Caching & Memory Optimization
Partition & Pushdown Optimization
Design Principles & Best Practices

Informatica PowerCenter Repository Management

Repository Manager tool (functionalities, create and delete, migrate components)
PowerCenter Repository Maintenance

Informatica Administration & Security

Features of PowerCenter 10
Overview of the PowerCenter Administration Console
Integration and repository service properties
Services in the Administration Console (services, handle locks)
Users and groups

Command Line Utilities

Infacmd, infasetup, pmcmd, pmrep
Automate tasks via command-line programs

More Advanced Transformations – XML

Java Transformation
HTTP Transformation

Got a question for us? Please mention it in the comments section and we will get back to you.

484