Aggregator Transformation in Informatica IICS – IDMC

Aggregator Transformation is Active and Connected Transformation in Informatica cloud IICS – IDMC.

Aggregator Transformation Usage in IICS – IDMC:

▹ Aggregator transformation is used to perform aggregate calculations, such as averages and sums, against groups of data.

▹ For example, you can use the Aggregator transformation to calculate the average salary for employees in each department of an organization. In the Aggregator transformation, create a group for the department number and then configure an expression to calculate the average salary for the employees in each group.

▹ The Aggregator transformation behaves like the Expression transformation except you can configure the Aggregator transformation to perform calculations on a group of data. Aggregator transformation has the properties as shown in the below.

▹ You can use Group or Aggregate functions like Sum (), Max() , Min() , Count() , Avg() in Aggregator Transformation as shown in the below.

▹ You can Create Output ports or Variable ports to perform aggregate calculations for the fields in Aggregator transformation as shown in below.

Group by Fields:

▹ We will use group by fields to define how to group data for aggregate expressions. Configure group by fields on the Group By tab of the Properties panel as shown in below.

▹ You can select multiple Group By fields to create a new group for each unique combination

▹ When you select group port, Data Integration produces one row for each group. If you do not select any group by port, Data Integration returns last row.

Improve the Performance of Aggregator Transformation:

▹ To Improve the performance of Aggregator, you can select Sorted input option on the advanced tab in Aggregator Transformation as shown in below.

▹ When you select Sorted input option in Aggregator Transformation, it will expect the input data to be sorted based on the group by port and also reduces the usage of Aggregator Cache.

▹ Use Sorter or Source Transformation before the aggregator to send the sorted data to Aggregator.

▹ Use filter before aggregator transformation, to reduce un-necessary aggregations.

▹ Group by on Numeric fields rather than String fields will improve the performance of aggregator.

▹ Limit the number of ports in Aggregator to avoid un-necessary usage of aggregator cache.

Cache in Aggregator Transformation: Aggregator will create the cache when the mapping task starts, calculates the values and deletes automatically when the session completed.

Aggregator Transformation is having two types of cache.

1. Index Cache: Contains Group by ports.
2. Data Cache: Contains Non-Group by ports.

▹ If you use the sorter transformation, before aggregator transformation and if you dnt check the sorted input option in aggregator transformation what will be the output?
Ans: Agg will not increase performance and, it will again take times to group up

▹ If you checked sorted input option in aggregator transformation, but you are not sending sorted data to aggregator Transformation tr what will happen?
Ans: Session will fail

▹ What will happen, if we check all the ports as group by in aggregator transformation?
Ans: Aggregator transformation will give unique records.

▹ what if we dnt check any group by port in aggregator transformation?
Ans: Aggregator transformation will give last record.

Nested aggregate functions:
A nested aggregate function is an aggregate function within another aggregate function.

For example, the following expression sums sales and returns the highest number:

MAX (SUM (SALES))

Conditional clauses: Use conditional clauses in the aggregate expression to reduce the number of rows used in the aggregation. The conditional clause can be any clause that evaluates to TRUE or FALSE.
For example, use the following expression to calculate the total commissions of employees who exceeded their quarterly quota:
SUM(COMMISSION, COMMISSION > QUOTA )