Tutorial corner

Informatica,ETL,oracle,sql/plsql

Aggregator Transformation in Informatica Without group by

Spread the love

Aggregator Transformation in Informatica Without group by : Earlier, we learnt about the Aggregator transformation in Informatica , steps to create it , configuring the port and properties.  We also saw one Example of aggregator Transformation in Informatica mapping .In that example we marked DEPT port as group by port and all calculation was done on group of dept no. Here comes one of the commonly asked informatica interview question on Aggregator transformation , that what will happen if we don’t select any port for grouping condition in aggregator Transformation ?

Aggregator Transformation in Informatica Without group by

Aggregator Transformation

Aggregator Transformation in Informatica Without group by

When Integration service start processing data for aggregator transformation , it first divide input data into multiple group based on the group by port. It start performing aggregate calculations for each row under that group and in the end it will contains last row for that group along with aggregated values ( say sum , avg etc).

Example :

Input data :

A B C
1 1 3
1 1 4
1 2 5

Case 1: Group By on A Only.

Output rows will be :

A B C
1 2 5

Case 2: Group By on B Only.

Output rows will be :

A B C
1 1 4
1 2 5

Case 3: Group By on A  and B

Output rows will be :

A B C
1 1 4
1 2 5

Case 4: Group By on A , B and C (or Group By C )

Output rows will be :

A B C
1 1 3
1 1 4
1 2 5

If you select all ports in aggregator transformation then this will work as a select distinct. We can use this to remove duplicate records when the source is a mainframe or flat file. I’m sure there must be a more performant way but this works very well.

Case 5: ‘Group by’ Not defined

A B C
1 2 5

If none of ‘group by‘ check box is selected then in this case aggregator consider  whole data under one group as default group containing all the records provided at input ports. So, last record will be at the output of this default group which is also the last record entered from input ports

Note : By default, the integration service returns the last row received for each group along with the result of aggregation. By using the FIRST function, you can specify the integration service to return the first row of the group.

 

I hope you enjoyed this small article on Aggregator transformation in Informatica.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme