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 functions are used to drive the aggregate expression, which can be developed either in variable ports or output ports only.
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.
An integration service creates an aggregate cache.
The aggregate cache contains group by ports, non-group by input ports, and output port, which provides aggregate expression.
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:
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.
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.