Working with Aggregator and Souter 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.

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.
Informatica PowerCenter Router Transformation

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. Informatica PowerCenter Router TransformationThe ports in all three target tables are as shown below
Informatica PowerCenter Router Transformation

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. Informatica PowerCenter Router TransformationThe router transformation will generate three groups : Kauai, Maui, Oahu and a default group.
  6. Informatica PowerCenter Router TransformationLink 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:
Informatica PowerCenter Router Transformation

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

Readers Comments