Working with Aggregator and Sorter Transformation

Johnson Cyriac Feb 5, 2013
|

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 Sorter Transformation. Using a sorter transformation, you can sort data either in an ascending or descending order. And aggregator can be used to summarize data. 

For the demonstration purpose lets consider the generation of Company a report, which will show all order details in descending order of order.

Solution

  1. Import Order, Items, Order_Items tables from the database 
  2. Calculate the total Order Amount for each Order 
  3. Create a target, which will show the total order amount in descending order 
Below will be the structure of the completed mapping.
Informatica Sorter transformation demo

I. Import Source and Target Definition

Note : Click the link to Learn more on Source Definition and Target Definition.
  1. Connect to the repository and open the project folder. 
  2. Import all the sources definitions Orders, Items, Order_Items from the database . 
  3. Create target table Tgt_OrderListing_x as shown below. 
target definition

II. Source Qualifier and Aggregator Transformation

Note : Click the link to Learn more on Aggregator Transformation.
  1. Create a Source Qualifier transformation and name it SQ_OrderListing_x.
  2. Create an Aggregator transformation and group on the Order_id column
  3. Link ports ORDER_ID, DATE_ENTERED, CUSTOMER_ID, QUANTITY, PRICE, DISCOUNT into the Aggregator.
  4. Add a new output port Order_Amount.
  5. The expression for Order_Amount is SUM(PRICE * QTY – DISCOUNT)
  6. Make QUANTITY, PRICE, DISCOUNT only input ports.
Below will the structure of the mapping at this point.
informatica mapping

III. Create Sorter Transformation

  1. To create the Sorter Transformation, use one of the following methods.
    • Select TRANSFORMATION | CREATE and select the Sorter transformation from the drop down. Enter the name as Srt_OrderListing_x or
    • Click on the icon Informatica Sorter transformation from the Transformations toolbar and rename the transformation to SRT_OrderListing_x.
  2. Informatica Sorter transformationDrag the output ports from Aggregator transformation to Sorter
  3. transformation.
  4. Informatica Aggregator transformationSelect the Ports tab in the Sorter transformation as shown below. Check the Key column of the Order_Amount port and select Descending from the Direction drop down as shown below
Informatica Sorter transformation

IV. Map the Target Columns

  1. Link all ports from Sorter Transformation to target table.
  2. Your mapping should look like the one as given below:
Informatica Sorter transformation

V. Load the Target

  1. Create a Workflow with the name wf_OrderList_x.
  2. Create a session task with the name s_OrderList_x.
  3. Run the Workflow.
  4. Monitor the Workflow.
  5. Verify the results for target table Tgt_OrderListing_x.
Your results should look something like this.

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.





About US Contact US Advertise Guest Post Terms and Conditions Privacy Policy Disclaimer

© 2012-2013 Data Intelligence Solution, All Rights Reserved
The contents in this site is copyrighted to Data intelligence Solution and may not be reproduced on other websites.