Router Transformation in Informatica
Router transformation is an active and connected transformation its a single input multiple output and one default group transformation used to route data from a single source to multiple targets based on specified conditions. It’s active because the row count is increased. It allows for more complex data routing scenarios by supporting multiple conditions and output groups.
Here’s how it works:
Key Features:
1. Multiple Output Groups:
– Input Group: All incoming data passes through this group.
– User-Defined Groups: Each group has a specified condition. Rows that meet the condition are routed to this group.
– Default Group: Rows that do not meet any of the user-defined conditions are routed here.
2. Condition-Based Routing:
– Each user-defined group can have its condition, allowing for complex data distribution logic.
Use Cases:
– Data Segmentation: Segmenting data based on specific business logic or conditions.
– Data Cleansing: Filtering out rows that meet certain criteria for cleansing or further processing.
– Data Transformation: Applying different transformations to different sets of data within a single mapping.
Example Scenario:
Suppose you have a source table with sales data, and you want to route records to different targets based on the region:
1. Input Data:
– Sales data with columns such as SaleID, Region, Amount, Date.
2. Router Conditions:
– Group 1 (North America): Region = ‘America’
– Group 2 (Europe): Region = ‘Europ’
– Group 3 (Asia): Region = ‘Asia e’
3. Output Targets:
– Records where Region = ‘America’ are routed to the North America target.
– Records where Region = ‘Europ’ are routed to the Europe target.
– Records where Region = ‘Asia e’ are routed to the Asia target.
– Records not meeting any condition (e.g., Region = ‘West America’) go to the Default group.
Steps to Configure a Router Transformation:
1. Add the Router Transformation:
– In the mapping designer, add a Router transformation to your mapping.
2. Define Groups and Conditions:
– Double-click the Router transformation to open the properties window.
– Go to the Groups tab and add new groups for each condition.
– Specify the conditions for each group.
3. Connect to Targets:
– Connect the appropriate output groups to their respective target tables or further transformations.
4. Map Fields:
– Ensure that fields are correctly mapped from the source to the Router transformation and from the Router to the targets.
Example Mapping:
Source —-> Router —-> Target_NA
|
—-> Target_Europe_T
|
—-> Target_Asia_T
|
—-> Default_Target_T
By using the Router transformation, you can effectively manage and route data based on complex conditions, enabling more flexible and efficient data processing workflows in Informatica.
Sure, let’s consider a more detailed scenario to demonstrate the use of Router transformations in Informatica. We’ll create a scenario where we need to process employee data and route it to different targets based on the department.
Scenario:
You have an employee source table with the following columns:
– EmployeeID_E
– Name_N
– Department_D
– Salary_S
You want to route employees to different target tables based on their department:
– Employees in the HR department go to the HR_Employee target table.
– Employees in the Finance department go to the Finance_Employee target table.
– Employees in the IT department go to the IT_Employee target table.
– All other employees go to a Miscellaneous_Employees target table.
Steps to Implement the Router Transformation:
1. Create Source and Target Definitions:
– Define the source table with columns: EmployeeID, Name, Department, Salary.
– Define the target tables: HR_Employee, Finance_Employee, IT_Employee, and Miscellaneous_Employee with the same structure.
2. Add a Source Qualifier:
– Drag the source definition into the mapping designer.
– Add a Source Qualifier transformation to read data from the source table.
3. Add a Router Transformation:
– Drag a Router transformation into the mapping designer.
– Connect the Source Qualifier to the Router transformation.
4. Define Groups and Conditions in the Router Transformation:
– Double-click the Router transformation to open the properties window.
– Go to the Groups tab.
– Add new groups for each department:
– Group HR: Department = ‘HR’
– Group Finance: Department = ‘Finance’
– Group IT: Department = ‘IT’
– Default Group: For all other employees.
5. Connect Router Groups to Target Definitions:
– Connect the HR group to the HR_Employees target table.
– Connect the Finance group to the Finance_Employees target table.
– Connect the IT group to the IT_Employees target table.
– Connect the Default group to the Miscellaneous_Employees target table.
6. Map Fields:
– Ensure that all fields from the Router transformation are mapped correctly to each target table.
Example Mapping:
Source —-> Source Qualifier —-> Router —-> HR_Employee
|
—-> Finance_Employee
|
—-> IT_Employee
|
—-> Miscellaneous_Employee
Detailed Example with Data:
1. Source Data:
| EmployeeID | Name | Department | Salary |
| 1 | John Doe | HR | 50000 |
| 2 | Jane Roe | Finance | 60000 |
| 3 | Sam Smith | IT | 70000 |
| 4 | Alice Lee | Marketing | 55000 |
| 5 | Bob Brown | HR | 52000 |
2. Router Conditions:
– HR Group: `Department = ‘HR’
– Finance Group: `Department = ‘Finance’
– IT Group: Department = ‘IT’
– Default Group: For all other departments.
3. Output Data:
– HR_Employees:
| EmployeeID | Name | Department | Salary |
| 1 | John Doe | HR | 50000 |
| 5 | Bob Brown | HR | 52000 |
– Finance_Employees:
| EmployeeID | Name | Department | Salary |
| 2 | Jane Roe | Finance | 60000 |
– IT_Employees
| EmployeeID | Name | Department | Salary |
| 3 | Sam Smith | IT | 70000 |
– Miscellaneous_Employees
| EmployeeID | Name | Department | Salary |
| 4 | Alice Lee | Marketing | 55000 |
By using the Router transformation, you efficiently route employee data based on department, simplifying the mapping and ensuring that each target table receives the appropriate records.
Summary: In this blog, you will be able to understand the concept of router transformation and how it will work in Informatica PowerCenter. If you have doubts please comment below.
