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!


Publicado por Pedro Carvalho

Apaixonado por análise de dados e Power BI

Um comentário em “Informatica Powercenter – Aggregator Transformation

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do

Você está comentando utilizando sua conta Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: