Tutorial corner

Informatica,ETL,oracle,sql/plsql

Router Transformation in Informatica with Example

Spread the love

Router Transformation in Informatica, is a connected and active transformation which let you to transfer data to multiple target depending upon the different condition. Filter transformation rejects the row which don’t satisfy its condition whereas Router Transformation allows you to pass the failed data to some default target as well. In below article we will go through the properties of Router Transformation. We will also discuss the steps of adding /configuring Router transformation in Informatica Mapping. We will also check  some performance tuning related guidelines for Router Transformation.

Router Transformation informatica

Router Transformation informatica

Business scenarios of Router Transformation:

Unlike Filter Transformation, Router Transformation provide you the flexibility of populating multiple target depending upon the different type of conditions. It also allow you to add multiple condition for each target. Failed records can also be extracted from the default group.

Is Router Transformation Active or Passive ?

Router Transformation in Informatica is an active transformation a its Routes data into multiple groups based on group conditions.

Is Router Transformation Connected or Unconnected ?

Router Transformation is a Connected Transformation.

Properties of Router Transformation :

  • Active /Passive: Router Transformation is a Active  transformation as  it also enable filter rows for each target based on condition.
  • Connected/Unconnected Transformation: Router Transformation is a connected Transformation.
  • Types of ports in Router Transformation:
    • Input : For reading input Data.
    • Output : For providing output Data.
  • Types of Group in Router Transformation:
    • Input: For reading input Data.
    • Output: For providing output Data to multiple Target.

Groups in Router Transformation:

There basically two type of groups in Router Transformation as shown in below figure

Router Transformation groups

Router Transformation groups

Input Group

Input group is to take input data from source. Designer copies port of the input group to create a set of output ports for each output group.

Output Group

Output group is there to transfer data to multiple target from each output group. Output port of these groups can’t be altered. Port information is taken from Input group itself. Output group can be further classified as :

User-Defined Groups

You can create a user-defined group to filter out  incoming data based on a condition .A user-defined group consists of output ports and a group filter condition. You can create and edit user-defined groups on the Groups tab with the Designer. Create one user-defined group for each condition that you want to specify. Example you can create DEPT1 group to filter the data for  DEPT=1 only.

The Integration Service uses the condition to evaluate each row of incoming data. It tests the conditions of each user-defined group before processing the default group. The Integration Service determines the order of  evaluation for each condition based on the order of the connected output groups. The Integration Service  processes user-defined groups that are connected to a transformation or a target in a mapping. The Integration Service only processes user-defined groups that are not connected in a mapping if the default group is connected to a transformation or a target.

NOTE: If a row meets more than one group filter condition, the Integration Service passes this row multiple times to each group output.

Default Group

The Designer creates the default group after you create one new user-defined group.This group will contains those data which don’t satisfy filter condition of any user defined group. You cant 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 Integration Service passes the row to the default group. If you do not want to use this data , don’t link these group port to any of the target in a mapping.

NOTE: Informatica Designer deletes the default group when you delete the last user-defined group from the list.

Configuring Router Transformation Ports:

You can create input ports by copying them from another transformation or by manually creating them on the Ports tab.The Designer creates output ports by copying the following properties from the input ports.

  • Port name: Add the name of the port.
  • Datatype, precision, and scale:  Configure the datatype and set the precision and scale for each port.
  • Default Value: In case port data is null, you can define its default value.

Configuring Router Transformation Properties:

Modify the Router Transformation properties by clicking on the Properties tab.

Property Description
Tracing Level Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.

Check here for filter transformation example in informatica.

How to use Router transformation in Informatica:

Use the following procedure to create an Router transformation in Informatica mapping.

Steps to create an Router transformation:

  • In the Mapping Designer, open a Mapping.
  • Click Transformation > Create. Select Router transformation.
Router Transformation step 2

Router Transformation step 2

  • You can also select Transformation by clicking  function  button on Informatica Designer.

    Router Transformation shortcut

    Router Transformation shortcut

  • Enter a name and click Done.
  • You can create ports in Router transformation  by selecting and dragging it from input transformation to router transformation.You can manually create input ports on the Ports tab by double clicking on it.
  • Double-click the title bar of the Router to open the transformation.
  • Click the Groups tab, and then click the Add button to create a user-defined group.
    Router Transformation in Informatica step 3

    Router Transformation step 3

  • To add Group,
    • Click the Groups tab.
    • Click the Add button.
    • Enter a name for the new group in the Group Name section.
    • Click the Group Filter Condition field and open the Expression Editor.
    • Enter the group filter condition.
    • Click Validate to check the syntax of the condition.
    • Click OK.
  • Router Transformation step 4

    Router Transformation step 4

  • The Designer creates the default group when you create the first user-defined group.
  • Click the Transformation tab and configure transformation properties. ( change Tracing Level as per your need).
    Router Transformation step 5

    Router Transformation step 5

  • Click OK.
  • Connect group output ports of router to other transformations or targets.

Note: After you make the transformation reusable, you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation.

Example of Router Transformation

Check example of Router Transformation in informatica

More about Router Transformation:

Router Transformation Performance Tuning:

  • Multiple filter transformation processing on the same data source should be replaced by Router.

Advantage of Router Transformation:

  • We can defined multiple condition in Router unlike Filter Transformation
  • We can do work of multiple filter in single Router Transformation. Hence Integration Service need to process only 1 Router instead of multiple filter, hence improving the performance of mapping.
  • With Default group, we have controlled over failed record as well.

So, in this article we have gone through properties of Router Transformation in informatica, component/Properties of Router Transformation, scenarios where to use it, steps to create it and  performance related guidelines. Hope you enjoyed the article. Please feel free to give your feedback.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme