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.
- Aggregate Cache:The integration service stores the group values in the index cache and row data in the data cache.
- Aggregate Expression:We can enter expressions in the output port or variable port.
- 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.
- 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.