Router Transformation

Router Transformation:

• Active and connected transformation.

A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the rows of data that do not meet the Condition. However, a Router transformation tests data for one or more conditions And gives you the option to route rows of data that do not meet any of the conditions to a default output group.

Example: If we want to keep employees of France, India, US in 3 different tables, then we can use 3 Filter transformations or 1 Router transformation.

Mapping A uses three Filter transformations while Mapping B produces the same result with one Router transformation.

A Router transformation consists of input and output groups, input and output ports, group filter conditions, and properties that we configure in the Designer.

Working with Groups

A Router transformation has the following types of groups:

• Input: The Group that gets the input ports.
• Output: User Defined Groups and Default Group. We cannot modify or delete Output ports or their properties.

User-Defined Groups: We create a user-defined group to test a condition based on incoming data. A user-defined group consists of output ports and a group filter Condition. We can create and edit user-defined groups on the Groups tab with the Designer. Create one user-defined group for each condition that we want to specify.

The Default Group: The Designer creates the default group after we create one new user-defined group. The Designer does not allow us to edit or delete the default group. This group does not have a group filter condition associated with it. If all of the conditions evaluate to FALSE, the IS passes the row to the default group.

Example: Filtering employees of Department 10 to EMP_10, Department 20 to EMP_20 and rest to EMP_REST

• Source is EMP Table.
• Create 3 target tables EMP_10, EMP_20 and EMP_REST in shared folder. Structure should be same as EMP table.
• Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give mapping name. Ex: m_router_example

4. Drag EMP from source in mapping.

5. Click Transformation -> Create -> Select Router from list. Give name and

Click Create. Now click done.

6. Pass ports from SQ_EMP to Router Transformation.

7. Edit Router Transformation. Go to Groups Tab

8. Click the Groups tab, and then click the Add button to create a user-defined Group. The default group is created automatically.

9. Click the Group Filter Condition field to open the Expression Editor.

10. Enter a group filter condition. Ex: DEPTNO=10

11. Click Validate to check the syntax of the conditions you entered.

12. Create another group for EMP_20. Condition: DEPTNO=20

13. The rest of the records not matching the above two conditions will be passed to DEFAULT group. See sample mapping

14. Click OK -> Click Apply -> Click Ok.

15. Now connect the ports from router to target tables.

16. Click Mapping -> Validate

17. Repository -> Save

• Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
• Make sure to give connection information for all 3 target tables.

Sample Mapping:

Difference between Router and Filter :

We cannot pass rejected data forward in filter but we can pass it in router. Rejected data is in Default Group of router.

Questions:

1. What is a router transformation?

A router is used to filter the rows in a mapping. Unlike filter transformation, you can specify one or more conditions in a router transformation. Router is an active transformation.

2. How to improve the performance of a session using router transformation?

Use router transformation in a mapping instead of creating multiple filter transformations to perform the same task. The router transformation is more efficient in this case. When you use a router transformation in a mapping, the integration service processes the incoming data only once. When you use multiple filter transformations, the integration service processes the incoming data for each transformation.

3. What are the different groups in router transformation?

The router transformation has the following types of groups:

• Input
• Output

4. How many types of output groups are there?

There are two types of output groups:

• User-defined group
• Default group

5. Where you specify the filter conditions in the router transformation?

You can create the group filter conditions in the groups tab using the expression editor.

6. Can you connect ports of two output groups from router transformation to a single target?

 No. You cannot connect more than one output group to one target or a single input group   transformation.