What is the Aggregator Transformation in informatica?

What is the Aggregator Transformation?

• This Transformation is an active Transformation.
• It is used for column level manipulations.
• We can use various types of aggregate functions such as MAX, MIN, SUM, AVG, COUNT etc. in Aggregator transformations.
• It processes the data in column-wise manner.

For Example-

We have the student’s data.

In source, we have Student_ID, Student_Name, Age, Address, Mobile_No,Marks.

We want data in target as shown in figure.

We need to find the Maximum Marks for all students. I have taken MaxMarks column in target.

I have connected all column names of source to all the column names of target.

We need to find Maximum Marks for all students by using Aggregator Transformation.

Creating mapping with Expression Transformation-

Drag and Drop both source and target to mapping.

You have noticed, with source one default transformation is there Source Qualifier.Then, you can create Aggregator Transformation.

How to create Aggregator Transformation?

1-Click Transformation on toolbar

2-Click Create

3-Select the Aggregator and give their name.

4-Drag it all these columns from source Qualifier to Aggregator Transformation.

5-Add one new column “out_ MaxMarks “ in Aggregator.

6-You can unchecked the Output Port of Marks and disable the Input Port of

“out_ MaxMarks “.

7-Write the function — MAX.

MAX(Marks) for finding Maximum Marks

Click validate for checking this function.

8-You can connect new column “out_ MaxMarks “ from Aggregator to target column “MaxMarks “.

9-You can save the mapping –Ctrl+S

10-Now,you mapping is valid. Afterward you can create the session and workflow for this mapping.

Run the workflow.

Then,you will get desire output in MaxMarks column i.e., Maximum marks of all students.

Now I am going to explain you internal process.

How Aggregator works internally-

In expression, we stores data row-wise manner.It means when first record reached to the target through mapping, then informatica will take second record and pass it to the target.

But in Aggregator transformation, it creates problem. It stores data column-wise manner.

In Aggregator_transformation, informatica creates cache memory by its own. Once all calculations are done, then informatica deletes the cache memory automatically.

One more feature in Aggregator transformation that is Group by clause.

Add any column and click check group by in properties.

you can use group by clause in age columns.

You will get max marks according to Age column. I will explain you group by clause in further articles.Read more..