Router transformation is an active and connected transformation which is similar to filter transformation, used to filter the source data.
The additional functionality provided beside filtering is that the discarded data (filtered out data) can also be collected in the mapping, as well as the multiple filter conditions can be applied to get multiple sets of data.
For example, when filtering the data form deptno =10, we can also get those records where deptno is not equal to 10. So, router transformation gives multiple output groups, and each output group can have its own filter condition.
In addition there is also a default group, this default group has those record sets which doesn't satisfy any of the group conditions. For example, if you have created two groups for the filter conditions deptno=10 & dept=20 respectively, then those records which are not having deptno 10 and 20 will be passed into this default group. In short the data which is rejected by the filter groups will be collected by this default group and sometimes there can be a requirement to store these rejected data. In such scenarios, default output group can be useful.
To allow multiple filter condition, the router transformation provides group option.
- There is a default input group which takes input data
- There is also a default output group which provides all those data which is not passed by any filter condition
- For every filter condition, an output group is created in router transformation. You can connect different targets to these different groups.
Creating Router Transformation
Step 1 – Create a mapping having source "EMP" and target "EMP_TARGET."
Step 2 – Then in the mapping
- Select Transformation menu
- Select create option
Step 3 – In the create transformation window
- Select router transformation
- Enter a name for the transformation "rtr_deptno_10"
- Select Create option
Step 4 – The router transformation will be created in the mapping, select done option in 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 in the transformation property of it
- Select group tab
- Enter group name "deptno_20"
- Click on the group filter condition
Step 7 – In the expression editor, enter filter condition deptno=20 and select OK button.
Step 8 – Select OK button in the group window
Step 9 – Connect the ports from the group deptno_20 of router transformation to target table ports
Now, when you execute this mapping, the filtered records will get loaded into the target table.
