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.
ADVERTISEMENT

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.
 Create Aggregator Transformation

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.
 Create Aggregator Transformation

After adding sources and target into the workspace, we should create Aggregator transformation. Open Transformation | click on Create and name it as t_Sorter.

 Create Aggregator Transformation
  • Select the transformation and enter name as s_Sorter and click on Create button.
 Create Aggregator Transformation

From the Source qualifier Transformation drag and drop dept no and Sal into Sorter Transformation as shown below.

 Create Aggregator Transformation

Now double click on the header of Sorter transformation, then edit Transformation window will be open and click on ports tab as shown

 Create Aggregator Transformation

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.

 Create Aggregator Transformation

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.

 Create Aggregator Transformation

Double click on Aggregator Transformation and go to Ports tab to enable the checkbox under the Group By column corresponding to DeptNo port name.

 Create Aggregator Transformation
  • 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.

 Create Aggregator Transformation

Disable the Input checkbox for the corresponding newly created field under expression field type SUM(sal) for the same field.

 Create Aggregator Transformation
  • Click on OK button.
 Create Aggregator Transformation
  • Go to properties tab on the same as shown below to enable Sorted Input field check box.
 Create Aggregator Transformation
  • Click on Apply button.

Connect DeptNo to DeptNo, Dept_Wise_Sal to the Dept_Wise_Sal from Aggregator Transformation to the Target table.

 Create Aggregator Transformation

Finally go to Repository and click on Save button.