Aggregator ia an active transformation which is used to perform aggregate calculations on group of records. We can specify “group by” on any column. If we don’t specify “group by” on any column aggregator will process the last record as output.
We can configure the below components of aggregator transformation.
Aggregator Expression: We can enter the expressions on output port or variable port.
GroupBy Port: We can configure input, output or variable port for the group.
Properties
Cache Directory: The integration service creates data and index cache files.
Sorted Input:
•If you don’t select this property aggregator will not sort the data, it performs aggregation directly which will save the input.
•If you select this property and pass unsorted data aggregator will fail the session.
Aggregator Data Cache: Data cache stores the row data. The default cache size is 2,000,000 bytes.
Aggregator Index Cache: Index cache stores group by ports data. The default cache size is 1,000,000 bytes.
Transformation Scope: It specifies how the integration service applies transformation logic on incoming data.
Aggregate Expressions
We can create aggregate expressions in aggregate transformation. We can use following aggregate functions in the aggregator transformation.
Avg
Count
First
Last
Max
Median
Min
Percentile
Stddev
Sum
Variance
Implementing Aggregator Transformation with Example
Drag source to the mapping designer
Create target by adding TOT_SAL column and drag it to the mapping designer
Go to Transformation-> Create->Aggregate Transformation->OK
Drag all ports from source qualifier to Aggregator
Edit->Aggregator->Ports->Add port->TOT_SAL
Deselect the input port
Click on Expression editor->From Aggregate functions->SUM(SAL)->OK
Select DEPTNO a "GroupBy"
Connect all the ports from Aggregator to Target
We can configure the below components of aggregator transformation.
Aggregator Expression: We can enter the expressions on output port or variable port.
GroupBy Port: We can configure input, output or variable port for the group.
Properties
Cache Directory: The integration service creates data and index cache files.
Sorted Input:
•If you don’t select this property aggregator will not sort the data, it performs aggregation directly which will save the input.
•If you select this property and pass unsorted data aggregator will fail the session.
Aggregator Data Cache: Data cache stores the row data. The default cache size is 2,000,000 bytes.
Aggregator Index Cache: Index cache stores group by ports data. The default cache size is 1,000,000 bytes.
Transformation Scope: It specifies how the integration service applies transformation logic on incoming data.
Aggregate Expressions
We can create aggregate expressions in aggregate transformation. We can use following aggregate functions in the aggregator transformation.
Avg
Count
First
Last
Max
Median
Min
Percentile
Stddev
Sum
Variance
Implementing Aggregator Transformation with Example
Drag source to the mapping designer
Create target by adding TOT_SAL column and drag it to the mapping designer
Drag all ports from source qualifier to Aggregator
Deselect the input port
