Updated March 21, 2023
Introduction to Router Transformations in Informatica
Router Transformation in Informatica is a special type of transformation functionality used to route the data flow direction based upon the route transformation condition. The router transformation is mostly used if there are multiple target systems or data tables available to process the logical scenarios in Informatica mappings. It is an active type of transformation in Informatica and is used as a connected transformation. Router Transformation in Informatica supports multiple filter conditions and source data discard functionality to manage data quality to the target systems using the Informatica mapping. One of the use cases of Informatica route transformation is routing the country-specific sales data to different target tables of the oracle database, creating data-marts for analysis.
How to create Router Transformation in Informatica?
Let us have a look at creating a router transformation step by step.
Step 1: Create a source and target in the mapping as below
Here address data is being stored with many other attributes.
Step 2: Go to the transformation menu and then select the create option.
Step 3: Once this done, select router transformation. Enter a name for this router transformation and select the create option.
Double click on router transformation to provide conditions for routing conditions. There will be below properties in the transformation tab.
- The default would be the one that you clicked upon.
- Rename: This will help the user in renaming the router transformation. This can be any meaningful name that will suggest what it is doing.
- Make Reusable: If you checkmark this option, then the transformation which is created will become reusable.
- Description: A description can be provided, which will explain what the transformation is doing.
The next tab which comes in this window is the ‘Ports’ tab. This tab has the below options:
- Port Name: List of all available columns. By clicking on a new column, you can easily add a new column and similarly, by clicking on the scissors, you can delete a column.
- I: Columns that are check-marked under this transformation will act as input for the router transformation.
- O: Columns that are check-marked under this section will act as output columns for the transformation. If any particular column is unchecked, then that column will not be sent to be loaded to the target table.
The next tab is the group’s tab. Here we can define the group name and conditions which are to be used. In the step ahead, you will observe two groups are created with two filter conditions.
A new group can be created by clicking on the below button.
Step 4: The router transformation will be created in the mapping. You can drag and drop all columns from source or previous transformations.
Step 5: Once all columns are present in the transformation, then go to its transformation property. You can give a group name and then enter the filter condition for this group.
Here we have two groups. One has filter condition of having OP_TYPE having value as I where values are getting inserted while the other group has filter condition as U where values will be updated.
Step 6: The filter condition can be done by clicking on the filter condition. By clicking on the highlighted arrow, you can open the expression window.
This window will open the below editor and help you in specifying different conditions. The second group can have similar conditions specified similarly. All remaining rows which do not satisfy any of the conditions will act as default output. These will be stored in the default group.
After applying the filter condition, click on OK. Once the filter condition is added you can click on ok then this transformation will be added in the mapping.
Step 7: The ports which are to be routed can be connected to the next transformations or the target.
Once this is done, and all groups are created, you can route the output to the next transformations or the target.
Here the insert group is sent to a target as below:
The update transformation is also traversed to another transformation which is update strategy.
All remaining ports which do not fulfil the specified conditions are sent to the default group.
Once the mapping is ready, you should validate it. This is done by going to the Mapping Menu bar and clicking on the validate option.
The validated mapping can be run by creating a workflow. This is done by using the workflow manager. It has two approaches to this.
- Create workflow manually
- Create a workflow using the wizard
To create a new workflow, you can go to the workflow menu and then click on the create option. This will open Create Workflow window where a unique name can be mentioned. Once the workflow is created, the next task is of creating a session. A non-reusable task can be created by navigating to the task menu and selecting the create task option. A new mapping window will be opened where you can associate the mapping with the session created. All sources, targets and common properties can be configured in the session task which is created. Once this setup is done, you can go to workflow and click on the start Workflow option. Once this is done, the workflow manager starts the monitor to observe the workflow for any errors or execution status.
The Router Transformation helps in routing the data from one transformation to another. The data can be split according to the different conditions of groups that are created. There is also one default group that will have ports that do not satisfy the group’s conditions. Router transformation acts like the If Else condition or Case condition. The result of the condition will decide where the output should go. It will decide where which data should flow. The rows which do not satisfy the condition can be dropped. The same router transformation can be used in multiple conditions. Router transformation is hence useful in creating different conditions for different input columns.
This is a guide to Router Transformation in Informatica. Here we discuss the introduction and steps for Router Transformation in Informatica, respectively. You may also look at the following articles to learn more–