Router Transformation in Informatica

Router Transformation:

• It is an Active and Connected Transformation.
• It is used to apply multiple conditions and to load the data into multiple targets. We can define any number of conditions here. There is no limit for this.
• We know that Filter Transformations acts as filter in SQL but Router Transformation acts as IIF in Informatica case .. when statement in the databases.
• 'Router Transformation' receives single input data set(records) and provides multiple outputs data sets.
• Router Transformation is created with the following 2 types of groups:

Input Group: It receives the data record by record.
Output Group: It sends the data record by record either to the target or to any other downstream transformation for the further processing.

Again Output Groups are categorized into 2 types:

1. User Defined Output Group and
2. Default Group

User Defined Output Group:

It allows the user to define the filter condition to restrict or to limit the data. It is almost as similar as filter transformation and the Default Group captures all the rejected records where it does not satisfies any of the given conditions.

Scenarios where we can use Router Transformation:

1. To design a mapping to load all department number 10 employees into one target and the department number 20 employees into another target and the rejected records into another target.

Procedure to work with Router Transformation:

1. Create 3 targets with the names as Emp_Dept10, Emp_Dept20 and Emp_rejects with the same structure as the emp using Target Designer and Save the meta data in the repository.
2. Create a mapping with the name m_router
3. From the repository drag the source(emp) from the repository database and in the same way also drag all the three targets into the workspace.
4. Go to transformations menu and then create a transformation of type router with the name Rout_deptno
5. Connect the ports from the source qualifier to Router Transformation
6. Open the header of Router Transformation and select 'Groups Tab'
7. Add the following output groups on the option as shown below. Group name:Group Filter Condition Emp_dept10 as Deptno=10 and Emp_dept20 as Deptno=20
8. Apply and then ok.
9. Connect the ports from the corresponding output groups of router to the corresponding target and save the mapping.
10. Follow the naming conventions and create session, workflow and execute it.

Note:

1. Router Transformation checks all the given conditions defined inside the filter conditions for each and every input record and then gives the output.
2. To make the performance more better during the design of thee mappings, use Router Transformation in place of many filter transformations. It is one of the performance tuning method in Informatica. More transformations will make the mapping to run very slow. So try to minimize the use of transformations as much as you can and see for the best performance tuning methods.