Aggregator transformation

Aggregator transformation is used for calculations using aggregate functions on a column as against in expression transformation, which is used for row-wise manipulation.

You can use aggregate functions such as SUM, AVG, MAX, MIN in aggregator transformation.

Scenario for aggregator transformation: Use the EMPLOYEE oracle table as the source, and get the sum of salaries of all the employees in the target.

Perform the following steps to achieve the functionality:

1. Import the source using the Employee Oracle table in the Source Analyzer, and create Target TGT_TOTAL_SALARY in Target Designer.
2. Create mapping m_AGG_TOTAL_SALARY, and drag the source and target from the Navigator to Workspace. Create the aggregator transformation with the name AGG_TOTAL_SAL.
3. As we need to calculate TOTAL_SALARY, drag only the SALARY column from the Source Qualifier to aggregator transformation, as shown here:
   
4. Add a new column TOTAL_SALARY in the aggregator transformation to calculate the total salary as shown in the following screenshot:

1. Add the function in the TOTAL_SALARY port by opening the expression editor as described in the preceding section. The function we need to add to get the total salary is SUM(JAN_SAL):
   
2. Connect the TOTAL_SALARY port to the target as shown in the following screenshot:
   

With this, we are done using the aggregator transformation. When you use aggregator transformation, integration services store the data temporarily in cache memory. Cache memory is created because the data flows in a row-wise manner in Informatica, and the calculations required in aggregator transformation are column wise. Unless we store the data temporarily in cache, we cannot calculate the result. In the preceding scenario, the cache starts storing the data as soon as the first record flows into the aggregator transformation. Cache will be discussed in detail later in the chapter under the Lookup transformation section.

In the next section, we will talk about added features of aggregator transformation. Aggregator transformation comes with features such as group by and sorted input.