Informatica - Aggregator Transformation

Aggregator

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

Aggregate cache. The Integration Service stores data in the aggregate cache until it completes aggregate calculations. It stores group values in an index cache and row data in the data cache.

Group by port. Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.

Sorted input. Select this option to improve session performance. To use sorted input, you must pass data to the Aggregator transformation sorted by group by port, in ascending or descending order.

Sorted Input Conditions

Do not use sorted input if either of the following conditions are true:

-->The aggregate expression uses nested aggregate functions.

-->The session uses incremental aggregation.

If you use sorted input and do not sort data correctly, the session fails.

To use sorted input, you pass sorted data through the Aggregator.Data must be sorted in the following ways:

-->By the Aggregator group by ports, in the order they appear in the Aggregator transformation.

-->Using the same sort order configured for the session.


Note: The Integration Service uses memory to process an Aggregator transformation with sorted ports. It does not use cache memory. You do not need to configure cache memory for Aggregator transformations that use sorted ports.

--> The Designer allows aggregate expressions only in the Aggregator transformation. An aggregate expression can include conditional clauses and non-aggregate functions.

Conditional Clauses
Use conditional clauses in the aggregate expression to reduce the number of rows used in the aggregation. The conditional clause can be any clause that evaluates to TRUE or FALSE.
For example, use the following expression to calculate the total commissions of employees who exceeded their quarterly quota:SUM( COMMISSION, COMMISSION > QUOTA )

Non-Aggregate Functions
You can also use non-aggregate functions in the aggregate expression.
IIF( MAX( QUANTITY ) > 0, MAX( QUANTITY ), 0))


Incremental Aggregation
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.