In this Expert Informatica tutorials, we will learn about Router Transformation and its uses with examples.
This is the type on active transformation which allows you to create "multiple conditions" and data can be passed to the "multipliable targets". Router transformation is single input to multiple output group transformation. Routers can be used in place of multiple Filter transformations. Router transformations accept the data through an input group once, and based on the output groups you define, it sends the data to multiple output ports. You need to define the filter condition in each output group.
If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.
The router transformation is created with two types of groups
- Input group:-
It can receive the data from source pipeline
- Output Group:-
It can provide the data for further processing (or) loading there are two type of output groups
- User defined output group:-
It allows you to defined the condition for processing the data.
- default output group
It allows you to capture the rejected records which don't meet none of the user defined conditions.
Difference between Router and Filter transformation
- Single condition 1. Multiple conditions
- Single target 2. Multiple target
- Reject records can't be captured 3. Reject records by default group.
Design a mapping two pass the data to the multiple targets odd number employee Id to one target, even number employee id to another target?
Create the source definition with the name Emp
create a definition with the name Emp-even and Emp-odd
Create a mapping with the name M-emp_even_odd
drop the source and target definitions
create the transformation type router
From source qualifier (emp) copy the required ports to the router transformation.
Double click on the router transformation select the groups tab from toolbar click on add a new group
Group filter condition
|Even||Mod(even,2) = 0|
click apply click ok
From router transformation, from even output group connect the ports to target definition
From default group, connect the ports to the second target
Creating Router Transformation in Informatica
In the mapping designer, create a new mapping or open an existing mapping
Go the toolbar->Click on Transformation->Create
Select the Router Transformation, enter the name, click on create and then click on Done.
Select the ports from the upstream transformation and drag them to the router transformation. You can also create input ports manually on the ports tab.
Configuring Router Transformation in Informatica
The router transformation has input and output groups. You need to configure these groups.
Input groups: The designer copies the input ports properties to create a set of output ports for each output group.
Output groups: Router transformation has two output groups. They are user-defined groups and default group.
User-defined groups: Create a user-defined group to test a condition based on the incoming data. Each user-defined group consists of output ports and a group filter condition. You can create or modify the user-defined groups on the groups tab. Create one user-defined group for each condition you want to specify.
Default group: The designer creates only one default group when you create one new user-defined group. You cannot edit or delete the default group. The default group does not have a group filter condition. If all the conditions evaluate to FALSE, the integration service passes the row to the default group. Specifying Group Filter Condition Specify the group filter condition on the groups tab using the expression editor. You can enter any expression that returns a single value. The group filter condition returns TRUE or FALSE for each row that passes through the transformation.
Informatica Router Transformation Examples
Router Transformation Example 1
Create the employee's data into two target tables. The first target table should contain employees with department_id 10 and second target table should contain employees with department_id 20?
Solution: connect the source qualifier transformation to the router transformation. In the router transformation, create two output groups. Enter the below filter conditions.
In the first group filter condition, department_id=10 In the second group filter condition, department_id=20 Now connect the output groups of router transformation to the targets
The router transformation has the following group filter conditions.
In the first group filter condition, department_id=30 In the second group filter condition, department_id<=30
Router Transformation Example 2
Design a mapping to route the correct to one target an exception data to another target (NULL validation).
Create a source definition with the Name EMP create two target definitions with the name EMP-correct and Emp - exception.
Create the mapping with the name M-emp-correct-exception-data
Drop the source and target definition.
create the transformation type expression
from source qualifier copy are the ports to an expression from the formation.
Double click on expression transformation select port tab from tool bar click on add a new port.
Ports Name data type precession scale I O V Expression
Exception -Flag string 10 0
IIF (ISNULL (empno)OR
ISNULL(Deptnos)), 'E', 'C').
click apply and click ok
Create the transformation type router from expression transformation copy all the ports to the router transformation double click on the router transformation select the groups toab from tool bar click on add a new group(())
Group Name Group filter condition
Correct-data exception -flag='C'
exception - data exception -flag='E'
click apply click on ok
From the router, transformation connects the ports to the target.
Power center Domain:-
The Power center domain is a primary unit for managing and administrating power center services
The Informatica Power Center as "service oriented" architecture which provides the ability to scale the services and share resources across multiple machines.
The power center domain is a collection of one or more nodes.
The node is a logical representation of machines
The nose which host to the domain is known as the primary node or master gate way node.
The other nodes are configured as worker nodes each node is created with repository service and integration service.
Power center administration console:-
The power center administration console is a web application which allows you to manage the row center domain.
Use admin console to perform the following talks
1.create application services changing the configuration of existing nodes
disable and enable nodes
create or delete nodes
Sequence generator transformation:-
This is the type passive transformation which allows you to generate the sequence no's which are to be treated as primary key's
A sorragetkey is a system generated sequence number which is used as dimensional key's artificial key's
For in-depth understanding of Informatica click on
- Data Mart and Types of Data Marts
- Informatica Components Overview
- Expression Transformation in Informatica
- Informatica Components