Aggregator Transformation

Aggregator transformation is an active transformation. And it is used to perform calculations on the data such as sums, averages, counts, etc.

The integration service stores the group of data and row data in the aggregate cache. The Aggregator Transformation is more beneficial in comparison to the SQL. We can use conditional clauses to filter rows.

Properties of Aggregator Transformation

Here are some features of aggregator transformation, such as:

• Aggregate Expression
• Group by port
• Sorted Input
• Aggregate cache
• Unsorted Input

Aggregate Expression

Aggregate functions are used to drive the aggregate expression, which can be developed either in variable ports or output ports only.

Sorted input

Group by ports are sorted using a sorted transformation and receive the sorted data as an input to improve the performance of data aggregation.

It keeps the sorted transformation before the aggregator transformation to perform sorting on fro up by ports.

Aggregate Cache

An integration service creates an aggregate cache.

Unsorted inputs

The aggregate cache contains group by ports, non-group by input ports, and output port, which provides aggregate expression.

Aggregate Expressions

This transformation offers more functionality to the comparison of SQL's group by statements. Because one can apply conditional logic to groups within the aggregator transformation. Many different aggregate functions can be used to individual output ports within the transformation. Below is the list of these aggregate functions, such as:

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

Creating an Aggregator Transformation

Follows the following steps, such as:

Step 1: Go to the Mapping Designer, click on transformation in the toolbar and create.

Step 2: Select the Aggregator transformation, enter the name, and click create.

Step 3: Then click on the Done button.

It will create an aggregator transformation without ports.

To create ports, we can either drag the ports to the aggregator transformation or create in the ports tab of the aggregator.

Configuring the Aggregator Transformation

We can configure the following components in aggregator transformation in the Informatica.

1. Aggregate Cache:The integration service stores the group values in the index cache and row data in the data cache.
2. Aggregate Expression:We can enter expressions in the output port or variable port.
3. Group by Port: This option tells the integration service on how to create groups. We can configure input, output, or variable ports for the group.
4. Sorted Input: This option is used to improve session performance. This option will apply only when the input to the aggregator transformation in sorted on the group by ports.

Informatica Nested Aggregate Functions

We can nest one aggregate function within another aggregate function. We can either use single-level aggregate functions or multiple nested functions in an aggregate transformation.

But we cannot use both single-level and nested aggregate functions in an aggregator transformation Informatica. The mapping designer marks the mapping as invalid if an aggregator transformation contains both single-level and nested aggregate functions. If we want to create both single-level and nested aggregate functions, create separate aggregate transformations.

Incremental Aggregation in Informatica

We can enable the session option and Incremental Aggregation After creating a session that includes an Aggregator transformation. When the integration service performs incremental aggregation, it passes source data through the mapping and uses historical cache data to perform aggregation calculations incrementally.