Router transformation is an active and connected transformation. It is similar to the filter transformation used to test a condition and filter the data. In a filter transformation, you can specify only one condition and drops the rows that do not satisfy the condition. Where as in a routertransformation, you can specify more than one condition and provides the ability for route the data that meet the test condition. Use router transformation if you need to test the same input data on multiple conditions.
Creating Router Transformation
Follow the below steps to create a router transformation In the mapping designer, create a new mapping or open an existing mapping Go the toolbar->Click on Transformation->Create Select the Router Transformation, enter the name, click on create and then click on Done. Select the ports from the upstream transformation and drag them to the router transformation. You can also create input ports manually on the ports tab.
Configuring Router Transformation
The router transformation has input and output groups. You need to configure these groups. Input groups: The designer copies the input ports properties to create a set of output ports for each output group. Output groups: Router transformation has two output groups. They are user-defined groups and default group.
User-defined groups: Create a user-defined group to test a condition based on the incoming data. Each user-defined group consists of output ports and a group filter condition. You can create or modify the user-defined groups on the groups tab. Create one user-defined group for each condition you want to specify.
Default group: The designer creates only one default group when you create one new user-defined group. You cannot edit or delete the default group. The default group does not have a group filter condition. If all the conditions evaluate to FALSE, the integration service passes the row to the default group.
Specifying Group Filter Condition
Specify the group filter condition on the groups tab using the expression editor. You can enter any expression that returns a single value. The group filter condition returns TRUE or FALSE for each row that passes through the transformation.
Advantages of Using Router over Filter Transformation
Use router transformation to test multiple conditions on the same input data. If you use more than one filter transformation, the integration service needs to process the input for each filter transformation. In case of router transformation, the integration service processes the input data only once and thereby improving the performance.
Router Transformation Examples
1. Create the employees data into two target tables. The first target table should contain employees with department_id 10 and second target table should contain employees with department_id 20?
Solution: connect the source qualifier transformation to the router transformation. In the router transformation, create two output groups. Enter the below filter conditions.
In the first group filter condition, department_id=10 In the second group filter condition, department_id=20
Now connect the output groups of router transformation to the targets
2. The router transformation has the following group filter conditions.
In the first group filter condition, department_id=30 In the second group filter condition, department_id<=30
What data will be loaded into the first and second target tables?
Solution: The first target table will have employees from department 30. The second table will have employees whose department ids are less than or equal to 30.