Informatica Powercenter – Aggregator Transformation

In the image above we can see inside of a aggregation transformation in Informatica Powercenter. So Aggr transformation can be any of those, Avg, Count, First, last, SUM, etc.

For our example I will use this as baseline:

We have the SQ_EMP_FILE table and here’s how I want this table to look like after my Powercenter workflow is complete:

So, instead of doing via SQL, let’s do via Powercenter.

Aggregation Transformation with Powercenter

The image above shows final Powercenter mapping. Here’s how to do it:

1. Create a mapping
2. drag your source to your mapping
3. create a new transformation of type Aggregation
4. edit/adjust your transformation
5. create a target
6. relate your aggr transformation to your target
7. make sure you have a table created in your database for your target
8. create a workflow
9. run your workflow
10. check the results
11. the end 🙂

I assume you already know how to create a source and a target, so let’s talk about the transformation itself, steps 3 and 4.

By following the steps shown in those images above you will be able to have your transformation.

Now drag from your Source Qualifier to your Aggr Transformation the field LOCATION, JAN_SALARY and FEB_SALARY.

Now double click your transformation so we can Edit it.

As you can see, couple of things needs to be done.

1. Check group by on LOCATION
2. Uncheck output for JAN and FEB salary,
3. Create a new field
4. Name it TOTAL_GERAL
5. Uncheck TOTAL_GERAL input port
6. Edit TOTAL_GERAL expression
7. Add this to TOTAL_GERAL expression: SUM(JAN_SALARY+FEB_SALARY)
8. Drag to your target Location to Location and Total_Geral to Total_Geral

Why does it happen this way?

Because Powercenter ( like DAX ) creates just-in-time variables that store JAN and FEB totals (grouping by Location, on this example) and do it LINE by LINE, and once the entire source is mapped those results are thrown to the target and recorded in your database.

The Result

As you can see our resulting table is showing exactly what was expected of it!

Reference

https://docs.informatica.com/data-integration/powercenter/10-4-0/transformation-guide/aggregator-transformation/creating-an-aggregator-transformation.html