Informatica Powercenter Express - Aggregator Transformation

Aggregator transformation is used to perform aggregate calculations, such as averages and sums. The Data Integration Service performs aggregate calculations as it reads and stores data group and row data in an aggregate cache.

The transformation language has the following aggregate functions

 AVG

COUNT

FIRST

LAST

MAX

MEDIAN

MIN

PERCENTILE

STDDEV

SUM

VARIANCE

An Aggregator transformation has the following port types

Input Receives data from upstream transformations.

Output Provides the return value of an expression.

Pass-Through Passes data unchanged.

Variable Used for local variables.

Group by Indicates how to create groups. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.

Advanced properties for an Aggregator transformations

Cache Directory

Local directory where the Data Integration Service creates the index cache files and data cache files. If you have enabled incremental aggregation, the Data Integration Service creates a backup of the files each time you run the mapping. The cache directory must contain enough disk space for two sets of the files.

Data Cache Size

Data cache size for the transformation.

Index Cache Size

Index cache size for the transformation.

Sorted Input

Select this option only if the mapping passes sorted data to the Aggregator transformation.

Tracing Level  
Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.

Tips to Improve performance while using Aggregator Transformation

• Use sorted input to decrease the use of aggregate caches. Sorted input reduces the amount of data cached during mapping run and improves performance. Use this option with the Sorter transformation to pass sorted data to the Aggregator transformation.
• Limit the number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.
• If you use a Filter transformation in the mapping, place the transformation before the Aggregator transformation to reduce unnecessary aggregation.

Scenario For Aggregator Transformation

Calculate total salaries paid to employees in each department

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.