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

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. 

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.

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  from the Transformations toolbar and rename the transformation to SRT_OrderListing_x.
2. Drag the output ports from Aggregator transformation to Sorter
3. transformation.
4. Select 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

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:

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.

Share with your friends