05.

Router Transformation

In this example, we implement following scenarios:
a. How to use Router Transformation?
b. Generating the Target File with Timestamp?Source table: – ODS_EMPLOYEETarget table: – ODS_EMPLOYEE_SALES ODS_EMPLOYEE_NON_SALESODS_EMPLOYEE_ROOKIE
Transformations:-
Router Transformation:- Router Transformation divides the incoming records into multiple groups based on some condition. Such groups can be mutually inclusive (Different groups may contain the same record).
Router transformation itself does not block any record. If a certain record does not match any of the routing conditions the record is routed to default group.
Scenario A:-
1. Importing the Source Definition and Target Definitations
2. Create a Mapping “m_ods_employee_types” by using the mapping designer. Click Tools > Mapping Designer to open the Mapping Designer.
Menu->Tools->Mapping Designer
Mapping Designer->Menu->Mappings->Create
3. Drag theODS_EMPLOYEE Source Definition the mapping designer workspace.
4. Click Transformation > Create to create Router transformation. Select Router, enter name as RTR_ODS_EMPLOYEE_TYPES
5. Drag all the columns from Source Qualifier to Router Transformation RTR_ODS_EMPLOYEE_TYPES
6. Edit the Router Transformation, add three different groups Condition as follows:
For SALES Group: TYPE_CODE=’SALES’
For NON_SALES Group:TYPE_CODE=’ADMIN’ OR TYPE_CODE=’MGR’ OR TYPE_CODE=’FIN’
For ROOKIE:DATE_DIFF(SYSDATE,DATE_HIRED,’YY’) <=2
7. Drag and drop the three target definition ODS_EMPLOYEE to workspace, link columns to Targets.

8. Click Repository > Save to save the new mapping to the repository. Validate the mapping.
9. In the Workflow Manager Navigator, create the session “s_m_ods_employee_types” that uses mapping “m_ods_employee_types” using the Task Developer.
Menu->Tools->Task Developer
Task Developer ->Menu->Tasks->Create
10. Provide the connection or Path for Integration service to communicate with your source and Target system
Session–>edit–>mapping ->Source (LS) -> OLAP_TDBU
Session—>edit–>mapping -> Target (LS) ->OLAP_TDBU (For all three targets)
11. Choose the Target load Normal Mode and check “truncate target table” option
12. Create the workflow “w_ods_employee_types” using the workflow designer. Assign the appropriate INT service.
13. Click Tasks > Link Task. Drag from the Start task to the Session task. Click Repository > Save to save the workflow in the repository.
14. Execute the workflow and verify the data in Targets.