Aggregator transformation is an active transformation is used to performs aggregate calculations like sum, average, etc.
For example, if you want to calculate the sum of salaries of all employees department wise, we can use the Aggregator Transformation.
The aggregate operations are performed over a group of rows, so a temporary placeholder is required to store all these records and perform the calculations.
For this, aggregator cache memory is used. This is a temporary main memory which is allocated to the aggregator transformation to perform such operations.
In this example, we will calculate the sum of salaries department wise. For this, we require a new column to store this sum. So, first of all, we will prepare a new column.
Step 1 – Create a new database target table, for example, say "sum_sal_deptwise", using the below script. You will see the new database target table is created under Target folder in next step.
Download the above Create_table_sal_deptwise.txt File
Step 2 – Create a New mapping "m_ sum_sal_deptwise".
In order to create new mapping, we need source table (EMP) and target table (sum_sal_deptwise) both in mapping designer for that we need to
- Import the target table "sum_sal_deptwise" in the mapping.
- Import the source table "emp".
Step 3 – In the mapping,
- From the Source Qualifier, delete the columns empno, ename, job, mgr, hiredate & comm so leaving only the columns deptno and sal.
- Create a new aggregator transformation using the toolbox menu as shown in screen shot. When you click on the aggregator icon, a new aggregator transformation will be created.
Step 4 - Drag and drop SAL & DEPTNO columns from source qualifier (SQ_EMP) to the aggregator transformation
Step 5 – Double click on the aggregator transformation to open its properties, and then
- Add a new port in the transformation
- Rename the port name to SUM_SAL
- Change the data type of this new port to double
- Make this port as output port by selecting the checkbox of the output port.
- Click on the expression option
Step 6 – In the expression window
- Add expression- sum(SAL), you have to write this expression.
- Select Ok Button, this will bring back the edit transformation window.
Step 7 – In edit transformation window, select option "GroupBy" by marking the check box against the deptno column and Click Ok ( by selecting group by against the deptno, we are instructing Informatica to group salaries by deptno)
Step 8 – Link the deptno and sum_sal columns from aggregator transformation to the target table
Now save the mapping and execute it after creating a new session for this mapping. The target table would contain the sum of salaries department wise. In this way, we can use aggregator transformation to calculate aggregate results.
