Wednesday, June 26, 2013

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.
clip_image001
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.
clip_image003
clip_image005
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.
clip_image035
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:
clip_image002
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.

2 comments:

  1. Are you struggling with the Quickbooks issues? Do not fret! Just dial our Quickbooks Support Phone Number 800-986-4591.
    We provide support for all Quickbooks variants. Reach us anytime whenever needed. We have a panel of experts to fix all your technical glitches. Connect with us for additional information and any queries!

    ReplyDelete
  2. Nice Post ! Our Quickbooks Payroll Support provide you immediate assistance for your issues. Even you can track employee’s salary from Quickbooks payroll support. Get comprehensive service benefits from the support by dialing on our Quickbooks Proadvisor Support Phone Number 800-986-4607 anytime & from anywhere.

    ReplyDelete