2

Aggregator Transformation in Informatica

Aggregator Transformation in Informatica

The aggregator transformation in Informatica is an active, connected transformation. It is very useful when integrating and aggregating data. Just as a reminder, active means the transformation can change the number of records in the pipeline and connected meaning it must be connected to the data flow. Its primary role is to manipulate groups of data or more than one record at time by applying functions to specific fields and grouping like data.

As stated by Informatica’s help guide, this transformation offers even more functionality than SQL’s group by statements since one can apply conditional logic to groups within the aggregator transformation. Many different aggregate functions can be applied to individual output ports within the transformation. One is also able to code nested aggregate functions as well. Below is a list of these aggregate functions:

  • AVG
  • COUNT
  • FIRST
  • LAST
  • MAX
  • MEDIAN
  • MIN
  • PERCENTILE
  • STDDEV
  • SUM
  • VARIANCE

Aggregate functions such as the one’s above are not the only functions allowed however. Non-aggregate functions as well as conditional statements are also allowed. Here are some examples:

Nested Aggregate Functions
MIN( COUNT( CARS ))

Conditional Statement
SUM( GAS, CARS_MPG > 20 )

Non-Aggregate Function
IIF( MAX( COUNT( CARS )) > 0, MAX( COUNT( CARS )), 0))

Aggregator Transformation in Informatica Functions Demo

Aggregator Transformation in Informatica Functions Demo

Null Values

By default, the Integration Service treats null values as NULL in aggregate functions, however this setting can be configured to treat null values as zero instead of null.

Configuring Aggregator Cache

As the aggregator transformation in Informatica runs in a session, data caches in memory or in files. If memory is not adequate to store all the cache needed by the session, cache files store the overflow of data. Caching can be configured in both the transformation or session properties.

Velocity Best Practices

Velocity recommends the following naming standards for the aggregator transformation: AGG_{FUNCTION}

For example: AGG_TOTALCARS

Aggregator Transformation in Informatica Group By Ports

As we select which ports to group by, similar to SQL, we will always get the unique combination of records for the ports we grouped by. One major difference is, by default, the transformation will pick values for the non-group by fields from the last record processed for each unique combination of group by ports selected. For example:

CAR_MAKE CAR_MODEL CAR_YEAR MPG
Honda Civic 2006 38
Honda Element 2008 22
Toyota Tacoma 2004 18
Toyota Tacoma 2012 20
Honda Civic 2012 41

If we group by CAR_MAKE and CAR_MODEL we will get the below output…

CAR_MAKE CAR_MODEL CAR_YEAR MPG
Honda Element 2008 22
Toyota Tacoma 2012 20
Honda Civic 2012 41

Sorting Input and Improving Performance

You can improve the aggregator transformation in Informatica performance by using the sorted input option. By choosing this input option, and sorting data in a sorter transformation prior to the aggregator, it can perform aggregate calculations as it reads rows for groups. If you double click on the aggregator transformation in Informatica PowerCenter, then go to the properties tab, we can check the sorted input checkbox.

Aggregator Transformation in Informatica Sorted Input

Aggregator Transformation in Informatica Sorted Input

If instead, you do not sort data and select the sorted input options, the aggregator must store data for each group until it reads the entire source to ensure all aggregate calculations are accurate. In general, the aggregator performs less efficiently this way.

When sorting the group by ports in the sorter transformation prior to the aggregator, the sorter transformation can be placed as far back in the mapping as necessary. As long as no other transformation disturbs the order of the records, the aggregator will perform as expected. It is very important however, the group by columns stay ordered, in the right order, otherwise the aggregator will not work correctly. Take a look at the below example. We have a sorter transformation connecting directly to an aggregator transformation in Informatica PowerCenter designer.

Sorter Transformation Demo Sorter to Aggregator

Sorter Transformation Demo Sorter to Aggregator

Lets assume we have checked the sorted input text box in the aggregator for better performance, and grouped by CARS and GAS in the aggregator.

Sorter Transformation Demo Aggregator Group By Ports

Sorter Transformation Demo Aggregator Group By Ports

In order for the aggregator transformation in Informatica to work correctly, we must sort by both CARS and GAS in the sorter transformation. Additionally, since the port order is first CARS, then GAS, we must order these ports in the sorter transformation the same way.

Sorter Transformation Demo Order of Ports

Sorter Transformation Demo Order of Ports

Final Summary

The aggregator transformation in Informatica PowerCenter is a useful mapping object that allows us to aggregate data easily. It has superior functionality than tradional SQL group by statements, providing more advanced functionality. Many mappings I have worked with personally have involved aggregating data in some way and this transformation helped me develop the right functionality. So make sure you understand how to use the aggregator transformation in Informatica. You will undoubtedly need it in your tool set for future projects.

Aaron Gendle

Aaron Gendle

Senior Consultant & Data Architect at Eccella
As a data architect, I have the privilege of solving challenging data problems on a daily basis.

I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.

Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.

Find Aaron Gendle on LinkedIn -
Aaron Gendle

Comments 2

  1. can you please help me how to group by without using Aggregator in informatica,i mean is there any alternative of aggregation transformation

    1. Post
      Author

      Great question. You can use the rank transformation followed by a filter to take out unwanted levels, but aggregator does it in one step. If it is record level dedupe, you can do that in the sorter transformation itself. Hope that helps

Leave a Reply

Your email address will not be published. Required fields are marked *