Working with Router Transformation and Aggregator Transformation
This tutorial shows the process of creating an Informatica PowerCenter mapping and workflow which pulls data from multiple data sources and use Aggregator and Router Transformation. Router transformation can be used to split the data into different groups. And aggregator can be used to summarize data.
A Router transformation is similar to a Filter transformation, this transformation can be used to split the data into different groups. A Router transformation consists of input and output groups, input and output ports, group filter conditions, and properties that you configure in the Designer.
For the demonstration purpose lets consider the generation of a report, which requires Store wise order details.
- Import Items, Orders, Order-Items and Stores tables from the database.
- Calculate order amount for each order for each store.
- Route the output based on store_id and load the data in different tables created for each store.
- Retrieve store wise order details.
Create a Mapping
I. Create Sources and TargetsNote : Click the link to Learn more on Source Definition and Target Definition.
- Import source tables from the database (Items, Orders, Order-Items and Stores).
- Create three target tables as shown below and name them as follows.
II. Drag Sources and Targets into the Mapping
Note : Click the link to Learn more on Mapping Designer.
- Drag all the source tables into the Mapping Designer.
- Create the Source Qualifier transformation and link the sources to the transformation.
III. Create an Aggregator TransformationNote : Click the link to Learn more on Aggregator Transformation.
- Drag all columns from Source qualifier into the transformation and group on Store_id and Order_id.
- Create an output port ORDER_AMOUNT.
- Create the expression: SUM(PRICE * QUANTITY - DISCOUNT)
- Change PRICE, QUANTITY and DISCOUNT to input ports only.
IV. Create a Router Transformation
- To create a Router transformation
- Select TRANSFORMATION | CREATE and select Router from the drop down, or
- Click the icon from the Transformation toolbar.
V. Load the Target
- Create a Workflow with the name wf_StoresOrders_x.
- Create a session task with the name s_StoresOrders_x.
- Run the Workflow.
- Monitor the Workflow.
- Verify the results for target table Tgt_KAUAIFRANCHISE_x, Tgt_MAUIFRANCHISE_x, Tgt_OAHUFRANCHISE_x
Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise and subscribe to the mailing list to get the latest tutorials in your mail box.