It is advanced to filter T/r. Router T/r returns both True and False Values, but the true values are returned to  User defined Group whereas False Values are returned to Default group. We can create any number of user defined groups. The data which is not satisfying any user group will go default group.

Router Transformation in Informatica is an ‘active transformation’ and it is advanced to filter transformation, Router transformation takes single input data (source) into multiple targets based on data condition applied for each group. Router Transformation returns both True and False Values, but the true values are returned to  User defined Group whereas False Values are returned to Default group. We can create any number of user defined groups. The data which is not satisfying any user group will go default group.

Router Transformation can be created by 2 types of groups :

  • Input group : Input group receives the data from source pipeline and have only one i/p group.
  • Output group : User defined and default are the two types of O/P group.

Router Transformation in Informatica

Router Transformation has an advantage over using multiple filter condition because in Router transformation, a row is read once into the input group but evaluated multiple times based on the number of groups.

FilterRouter
Single ConditionMultiple Conditions
Single Targetmultiple targets
Rejected record can’t be captured.Default group caches the rejected records.

Route Transformation mapping diagram.

ADVERTISEMENT
Route Transformation mapping diagram.

In this Informatica tutorial, we will learn about router transformation. Here we are going to create a mapping, where a row can be sent to one of the three different target tables ().

  • Source table : EMPLOYEES.
  • Target tables : TUTORIALKART_EMPLOYEES_SALES, TUTORIALKART_EMPLOYEES_NON_SALES, TUTORIALKART_EMPLOYEES_ROOKIE.

Router condition.

  • Sales group : JOB_ID = ‘ST_CLERK’.
  • Non Sales group : JOB_ID = ‘FI_ACCOUNT’ OR JOB_ID = ‘IT_PROG’ OR JOB_ID = ‘FI_MGR’
  • Rookie group : DATE_DIFF (SESSSTARTTIME, HIRE_DATE, ‘dd’) > 730

Creating Router transformation.

To create router transformation in Informatica, we must create ‘Mapping’. Before creating mapping, we must import source and target tables using source and target Analyzer.

Creating Source Definition.

Connect to Informatica Repository service by proving Informatica admin console credentials. Source definition  (source table) can be created using Source analyzer. Open Informatica PowerCenter Designer | Tools  | Source Analyzer and import source table into Workspace.

Creating Target definition.

Target definition(target table) can be created using Target Designer. Open target designer and create target table TUTORIALKART_EMPLOYEES_SALES, TUTORIALKART_EMPLOYEES_NON_SALES, TUTORIALKART_EMPLOYEES_ROOKIE.

Route Transformation

Creating Mapping for Router transformation.

To create new mapping in Informatica PowerCenter, navigate to Mapping designer available in tools and click on create option. A new window will be opened, enter mapping name and click on OK button.

Route Transformation

Now, drag and drop source definition from source folder to Mapping workspace. Source qualifier is created automatically by designer.

Creating Router Transformation in Informatica 10.0.1.

To create router transformation in Informatica, go to transformation tab and select create option. Now a new window will be opened, enter unique name and click on OK button.

  • Select required ports in source qualifier and drop those ports in Router transformation, connect the source definition with transformation using link column icon in the tool bar.
Route Transformation

To add groups in router transformation, double click on header and select groups tab to add new groups.

Route Transformation
  • Ignore default group and add three new groups and add filter condition as shown below.
Route Transformation
  • Click on validate button to check entered condition is valid or not.
Route Transformation
  • Enter group filter condition for sales, non sales and rookie groups as shown below.
Route Transformation
  • Click on Apply button and Ok.

Now add all three target definition tables to the mapping and link the target definition from the corresponding groups in router transformation as shown below.

Route Transformation in Informatica
  • Save the Mapping.

Create and run Workflow

After creating Mapping Successfully, we have to create and run workflow. Open Informatica PowerCenter Workflow manager and create new workflow.

Creating Session.

A session task must be created for our mapping, go to tasks tab and select create option. Enter name and click on Done.

  • Select a mapping to associate with the session, here we selected M_TUTORIALKART_EMPLOYEES .
Route Transformation in Informatica 10.0.1

Next, we must configure session connections by editing session. Double click on session, navigate to Mapping tab and select connections.

  • Click on down arrow and select relational database connections for sources and targets as shown below.
Route Transformation in Informatica 10.0.1
  • Under Properties, the Target load type should be defaulted to Normal.
router transformation in Informatica
  • Click OK to close the Edit Tasks dialog box.
  • Link Start to session as shown below.
Route Transformation
  • Save the workflow and click on run workflow.

Monitoring Workflow.

To monitor the status of the workflow, open Informatica PowerCenter workflow monitor and the status.

Route Transformation