Saturday, November 17, 2012

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.

3 comments:

  1. What happens if a user defined group in a Router Transformation is not connected to any upstream Transformation/Target?

    ReplyDelete
  2. What happens if a user defined group in a Router Transformation is not connected to any upstream Transformation/Target?

    ReplyDelete
  3. We are Urgently looking for Tutors for All Suject and All Grade Teaching USA , UK and Indian Students.


    Salary:
    Payments from USA Students: Rs1200 to Rs2500 Per Hour
    Payments from UK Students: Rs1200 to Rs2500 Per Hour
    Payments from INDIAN Students: Rs500 to Rs1500 Per Hour

    Job Type: Home Tuition, Online Tuition, Home work help, Part time tuition, Teaching.

    Please register your profile to Tutors99.com and get the classes for free. Student will call you immediately after registration.

    please complete registration as soon as possible. http://tutors99.com/en/auth/create_user/Tutor

    Regards
    Tutors99.com

    ReplyDelete

Thank you :
- kareem