The Router Transformation in Informatica is just like IF condition or CASE statement. It will check the given condition and based on the condition result, an output will be sent to the appropriate destination path. This is similar to Filter transformation and we can use the Informatica Router Transformation when we have to test multiple conditions.
The Router Transformation in Informatica is very useful in real-time. For example, if we have a situation where we want to store the Employees records whose salary is greater than 40000 in one Table and less than 40000 in another table then we can use this Informatica Router Transformation to split the data based on the specified expression.
For this Informatica Router Transformation example, we are going to use the below show data. Here, Our task is to conditionally split the below-shown data and store them in multiple database tables.
Configure Router Transformation in Informatica
Before we start configuring the router transformation in Informatica, First connect to Informatica repository service. In order to connect with Repository service we have to provide the Admin Console credentials so, Please provide the appropriate Username and Password and click on Connect button as shown below.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Server.
Creating Router Transformation in Informatica Source Definition
Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Router Transformation example, we use the [Products] table from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition
Create an Informatica Router Transformation Target Definition
Please navigate to Target Designer and define the Target. In this Informatica Router Transformation example, we are using the already created SQL tables (Router1, Router2, and Router3) as our target definitions. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition
Creating Router Transformation in Informatica Mapping
In order to create a new mapping, Please navigate to Mappings menu in Menu Bar and select the Create.. option. Here, you have to write a unique name for this mapping (m_Router_Transformation) and click OK button.
Next, Drag and drop the [Products] source definition from Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer will automatically create the default transformation called source qualifier.
Creating Router Transformation in Informatica
In order to create Router transformation in Informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below.
Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please select the Informatica Router Transformation from the drop-down list and specify the unique name (rtr_Sales) for this transformation and click on Create button
Once you click on the Create button, router transformation will be added to the mapping designer. In order to route the data, router Transformation requires some data so, please connect the Source definition with the transformation by dragging the required fields.
From the above screenshot, you can observe that we have our INPUT data. Let us define the output groups so, Double click on the Informatica Router transformation to provide the route conditions. From the below screenshot you can see the list of available properties in the Transformation tab:
- Select Transformation: By default, it will select the transformation you clicked on.
- Rename: This button will help you to rename the router transformation to a more meaningful name.
- Make Reusable: If you check mark this option then, this transformation will become reusable transformation.
- Description: Please provide a valid description of this transformation.
Below screenshot will show you the list of available options in the Informatica Router Transformation Ports tab:
- Port Name: List of available column names. By clicking the New column button you can add new columns and by clicking the scissors button you can delete the unwanted columns.
- I: Columns that are check-marked under this section are the Input columns.
- O: Columns that are check-marked under this section are the router transformation Output columns. If you unchecked any column then, that column will not be available to load in the target table.
Within the Groups tab, we have to define the group names and condition we want to use. From the below screenshot, you can observe that we created two groups (LEAST SALES and MEDIUM SALES).
TIP: The router transformation in Informatica will automatically create the default group (i.e., DEFAULT 1). Records which does not belong to above-mentioned groups will be stored in this group.
Let us provide the expression or condition for the First group (Least Sales) by clicking the arrow button as we shown below. This will open the Expression Editor to write a custom expression.
Here, we want the records whose Sales Amount is less than 400 so, we are writing the below-shown expression (SalesAmount < 400).
Next, provide the expression or condition for the Second group (Medium Sales) by clicking the arrow button and write the condition (SalesAmount >= 400 AND SalesAmount < 2000).
All the remaining rows will be acted as default output and they will be stored in DEFAULT 1 group (Sales Amount > 2000)
Once you finish configuring the properties, Click OK to close the Informatica Router Transformation window.
Next, Drag and drop the target definitions (Router 1, Router2 and Router3) from Targets folder to the mapping designer as shown below.
Next, Please connect the LeastSales (Output Group 1) with the first target definition (Router 1), MediumSales (Output Group 2) with Router 2 and Default 1(Default Group) with Router 3
Before we close the Mapping, Let us validate the mapping by going to Mapping Menu bar and selecting the Validate option.
Creating an Informatica Router Transformation Workflow
Once we finish creating the Mapping we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.
In this Informatica Router Transformation example, we will create the Workflow manually. In order to create a new workflow, Please navigate to Workflows Menu and select the Create option.
This will open Create Workflow window as shown below. Please provide the unique name (wf_Router_Transformation) and leave the default settings.
Once we created the workflow, our next step is to create a session task for our mapping.
NOTE: We strictly recommend to refer Workflow article to understand the steps involved in creating Workflow manually.
Creating Session for Router Transformation in Informatica
There are two types of sessions:
- Non-reusable Session Task: Please refer Session article to understand the steps involved in it.
- Reusable Session Task: Please refer Reusable Session article to understand the steps involved in it.
For this Informatica Router Transformation example, we are going to create Non-reusable Session. Please navigate to Tasks Menu and select the Create option to open the Create Task window. Here you have to select the Session as Task type (default) and enter a unique name (S_Router_Transformation) for the session.
Once you click on the Create button, a new window called Mappings will be opened. Here you have to select the mapping you want to associate with this session. From the below screenshot you can observe that we are selecting the mapping (m_Router_Transformation) that we created earlier (in Step 3).
Double click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we not explaining any of them. We strictly recommend visiting the Session article to understand the properties.
From the below screenshot you can observe that the Router Transformation in Informatica workflow is a valid one. Now, Let us start the Workflow by navigating to Workflows menu and selecting the Start Workflow option.
Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our workflow is executed without any errors.
Let us open the SQL Server Management Studio to show you the result of [Router 1] (i.e., an output of the Least Sales group)
Let’s see the result in [Router 2] Table in SQL Server. The following screenshot will show you the output of the Medium Sales group
Let’s see the result in [Router 3] Table in SQL Server. The following screenshot will show you the output of the Default group. Please refer to Filter article.
