Aggregator Transformation

Aggregator is just like a calculator in Informatica mappings.

It helps us to do calculations like, SUM(+),MINUS(-), MAX,MIN etc.

Lets take one scenario where you need to find,MAX salary by DEPARTMENT.

DATA we have is,

How do you solve the same problem in real world?

First you will group the employees working in same department and then you can easily identify which employee is getting max salary.

In Informatica world also we follow the same procedure.

As you can observe above, we set the group by for DEPTNO and we are finding MAX_SAL in the below port.

In similar way we can use other aggregate functions as well.

You can use condisional clauses to reduce the number of rows used in the aggregation.

For example to calculate sum of salary of managers who have 5 years of experience,

SUM(SAL,experience =5)

You can also use Non-Aggregate Functions like,

IIF(MAX(SAL)>100,MAX(SAL),0))

Try using these conditions as well in the output port MAX_SAL of  mapping.


Click Here to Download Mapping.

Click Here to Download Scripts to create EMP table.

What will happen if you do not set the group by port?

Integration Service will return only last row received.

Uncheck the group by port in above mapping and try to see above behavior.

So you are done with your first Mapping on Aggregator Transformation.

So what next? Actually we can help aggregator to improve its performence.

Why we need to do so? What happens if we do not help him?

You can find that in next post.