Aggregator Transformation In Informatica
Type: Active & Connected.
#Aggregator transformation perform calculation on groups instead of processing row-by-row as in Expression transformation.
While performing this, you can also use conditional clauses like filter condition providing more flexibility.
Components Of Aggregator Transformation:
Aggregator cache:
Integration service reads & store data group and row data in Aggregate Cache. It stores group values in Index Cache
and row data in Data Cache.
For ex: If we need to get department wise avg salary then integration service allocates memory for Index cache and stores group values
for department id say example 10,20,30.... and allocates memory for data cache & stores raw data values such as salary values.
If allocated is not enough to store data then integration service stores overflow values in cache file in specified cache directory.
Group By Ports:
Allows you to create group/s. Port can be input, input/output, output even Variable port is also allowed.
Sorted Input:
Selecting sorted input option will improve session performance. To use sorted input, you must pass
sorted data by group by ports to aggregator transformation.
Also Integration service uses memory to perform aggregator transformation when sorted input option is used.
So you do not need to configure cache memory size when using sorted input.
Examples: AVG, COUNT, MIN, MAX, FIRST, LAST, SUM etc.
Conditional clauses example SUM(new_salary, new_salary > old_salary) will return sum of salaries only for the records
where new salary is greater than old salary.
You can also use non aggregate functions such as
IIF (SUM(quantity) > 0, SUM(quantity, 1)) will rerturn sum of quantities by group by port, if it is 0 or less than 0 then it will return 1.
When you configure the session to treate null values as 0 if specified otherwise it will treat null values as null.
To improve the performance, pass sorted data and filter the data if required before passing to aggregator transformation.
Type: Active & Connected.
#Aggregator transformation perform calculation on groups instead of processing row-by-row as in Expression transformation.
While performing this, you can also use conditional clauses like filter condition providing more flexibility.
Components Of Aggregator Transformation:
Aggregator cache:
Integration service reads & store data group and row data in Aggregate Cache. It stores group values in Index Cache
and row data in Data Cache.
For ex: If we need to get department wise avg salary then integration service allocates memory for Index cache and stores group values
for department id say example 10,20,30.... and allocates memory for data cache & stores raw data values such as salary values.
If allocated is not enough to store data then integration service stores overflow values in cache file in specified cache directory.
Group By Ports:
Allows you to create group/s. Port can be input, input/output, output even Variable port is also allowed.
Sorted Input:
Selecting sorted input option will improve session performance. To use sorted input, you must pass
sorted data by group by ports to aggregator transformation.
Also Integration service uses memory to perform aggregator transformation when sorted input option is used.
So you do not need to configure cache memory size when using sorted input.
Examples: AVG, COUNT, MIN, MAX, FIRST, LAST, SUM etc.
Conditional clauses example SUM(new_salary, new_salary > old_salary) will return sum of salaries only for the records
where new salary is greater than old salary.
You can also use non aggregate functions such as
IIF (SUM(quantity) > 0, SUM(quantity, 1)) will rerturn sum of quantities by group by port, if it is 0 or less than 0 then it will return 1.
When you configure the session to treate null values as 0 if specified otherwise it will treat null values as null.
To improve the performance, pass sorted data and filter the data if required before passing to aggregator transformation.
