Working with Router Transformation and Aggregator Transformation

  Level2 Training , Transformations

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.

Solution

1. Import Items, Orders, Order-Items and Stores tables from the database. 
2. Calculate order amount for each order for each store.
3. Route the output based on store_id and load the data in different tables created for each store.
4. Retrieve store wise order details.

Below image shows the completed Mapping Layout.

Create a Mapping

I. Create Sources and Targets

Note : Click the link to Learn more on Source Definition and Target Definition.

1. Import source tables from the database (Items, Orders, Order-Items and Stores).
2. Create three target tables as shown below and name them as follows.
• Tgt_KAUAIFRANCHISE_x
• Tgt_MAUIFRANCHISE_x
• Tgt_OAHUFRANCHISE_x
3. The ports in all three target tables are as shown below

II. Drag Sources and Targets into the Mapping

Note : Click the link to Learn more on  Mapping Designer.

1. Drag all the source tables into the Mapping Designer.
2. Create the Source Qualifier transformation and link the sources to the transformation.

III. Create an Aggregator Transformation

Note : Click the link to Learn more on Aggregator Transformation.

1. Drag all columns from Source qualifier into the transformation and group on Store_id and Order_id.
2. Create an output port ORDER_AMOUNT.
3. Create the expression: SUM(PRICE * QUANTITY - DISCOUNT)
4. Change PRICE, QUANTITY and DISCOUNT to input ports only.

IV. Create a Router Transformation

1. To create a Router transformation
• Select TRANSFORMATION | CREATE and select Router from the drop down, or
• Click the icon from the Transformation toolbar. 
2. Link all the output ports from Aggregator Transformation to Router Transformation
3. Enter the name of the Router as : Rtr_StoreOrder_x.
4. Select the Groups tab and enter the values under Group Name and Group Filter Condition as shown in the figure below.
5. The router transformation will generate three groups : Kauai, Maui, Oahu and a default group.
6. Link columns from each group to the respective targets. For example, the ports under the Kauai group are linked to the Tgt_KAUAIFRANCHISE_x target. This target table contains the order details for the store where store id = 2014.
7. The final mapping will look like one given below:

V. Load the Target

1. Create a Workflow with the name wf_StoresOrders_x.
2. Create a session task with the name s_StoresOrders_x.
3. Run the Workflow.
4. Monitor the Workflow.
5. 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.

Share with your friends