Informatica Aggregator Transformation
Informatica Aggregator Transformation is an active and connected type Transformation which allows user to perform calculations like COUNTS, AVERAGES, SUMS etc on group of data. When performing aggregate expression, we use conditional clauses, aggregate functions, non-Aggregate functions to filter rows.
Informatica Aggregator Transformation has the following components :
Aggregate expression : Aggregate expression is entered in an output port and it include non-aggregate expressions and conditional clauses. It can include one aggregate function nested within another aggregate functions.
Example : MAX(COUNT(ITEM)).
Group by port : Aggregator transformation allows user to define groups for aggregations, rather than performing the aggregation across all input data. When performing Group by Port, the Informatica server returns one row for all input rows.
Sorted Input : Aggregator transformation performance can be improved using sorted ports when we configure the session with multiple partitions. When Sorted Input is not used , the data is not sorted, server stores data for each group until it reads the entire source to ensure all aggregate calculation are accurate.
Aggregator cache : When we run a workflow in Informatica PowerCenter with aggregate transformation, the Informatica server stores data in the aggregate cache until it perform aggregate calculations.
Aggregate functions
- AVG.
- COUNT.
- FIRST.
- LAST.
- MAX.
- MEDIAN.
- MIN.
- PERCENTILE.
- STDDEV.
- SUM.
- VARIANCE.
Creating Informatica Aggregator Transformation
To use an Aggregator Transformation in a mapping, we must added Aggregator transformation to Mapping in PowerCenter designer and then configure the transformation with aggregate expression, Group by ports if necessary.
Follow the steps given to create an Aggregator Transformation in Informatica :
Before creating transformation, we must create target table and must be imported into Informatica PowerCenter designer tool as a target. Open Informatica PowerCenter designer tools and connect with PowerCenter repository with username ans password.
- Navigate to Tools | Mapping Designer | Create.
In this Informatica tutorial, we creating Aggregator transformation so name the mapping as m_AGG_EMP and click on OK button.
- Click on Folder, now drag and drop source and target into the Workspace as shown below.
After adding sources and target into the workspace, we should create Aggregator transformation. Open Transformation | click on Create and name it as t_Sorter.
- Select the transformation and enter name as s_Sorter and click on Create button.
From the Source qualifier Transformation drag and drop dept no and Sal into Sorter Transformation as shown below.
Now double click on the header of Sorter transformation, then edit Transformation window will be open and click on ports tab as shown
Enable the checkbox under Key, corresponding DeptNo port, which is marked by red color as shown above. Now click on OK button.
Creating Aggregator Transformation
Now we should create Aggregator Transformation, to create go to Transformation | Create and name the transformation as t_AGG.
Click on create button and click on Done button. Now drag and drop the DeptNo and Sal from Sorter Transformation to Aggregator Transformation as shown.
Double click on Aggregator Transformation and go to Ports tab to enable the checkbox under the Group By column corresponding to DeptNo port name.
- Click on OK button.
Select Sal column and click on Add New Port icon which is marked by red colored circle. Now a new column is added under Port Name field and rename it as DEPT_WISE_SAL.
Disable the Input checkbox for the corresponding newly created field under expression field type SUM(sal) for the same field.
- Click on OK button.
- Go to properties tab on the same as shown below to enable Sorted Input field check box.
- Click on Apply button.
Connect DeptNo to DeptNo, Dept_Wise_Sal to the Dept_Wise_Sal from Aggregator Transformation to the Target table.
Finally go to Repository and click on Save button.