Aggregator Transformation

Explain about Aggregator Transformation

Aggregator Transformation is an active Transformation which allows you to calculate the summary's for group of records. The Integration Service performs aggregate calculations as it reads and stores data group and row data in an aggregate cache

The Aggregate functions supported are 
SUM, AVG, COUNT, MAX, MIN, FIRST, LAST, MEDIAN, VARIANCE, PERCENTILE and STDDEV



An Aggregator Transformation is created with following components

• Group by: This component defines the group for a specific port which participates in aggregation

• Aggregator Expressions: Use aggregate function to derive aggregate expressions which can be developed in either variable ports or only output ports

• Aggregator cache: An Integration service creates aggregate cache for "unsorted inputs". It stores the group value in index cache data in the data cache

• Sorted input:  "Group by ports" are sorted using a sorted transformation and receive the sorted data as input to improve the performance of data aggregation 

Keep the sorted transformation prior to Aggregator Transformation to perform sorting on group by ports

The aggregate cache contains group by ports, non-group by input and output ports which contains Aggregate expressions

After you create a session that includes an Aggregator transformation, you can enable the session option, Incremental Aggregation. When the Integration Service performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally

When you run a session which consist of Aggregate Transformation, the Integration service creates index cache and data cache in the memory. If it requires more space it uses the cache. The index and data cache can be configured in the session properties

A session with an Aggregator transformation with Sorted input doesn't use the cache.