etlcorner: Aggregator Transformation in Informatica with example

Aggregator Transformation in Informatica , is a connected Active transformation which let you performs aggregate calculations, such as averages and sums on the group of data. Aggregator transformation is differ from Expression transformation , as you use Aggregator transformation to perform calculations on groups . The Expression transformation permits you to perform calculations on a row-by-row basis only.

Aggregator Transformation in informatica

Aggregator transformation in informatica scenarios
The Aggregator transformation is use to perform aggregate calculations for each data. Data can be modified using built-in functions . Sample calculation peformed by the Aggregate transformer are :

AVG, COUNT, MAX, MIN, SUM
FIRST, LAST
MEDIAN, PERCENTILE, STDDEV, VARIANCE

Is Aggregator Transformation Active or Passive ?

Aggregator Transformation is a Active transformation as it also enable you to use conditional clauses to filter rows.

Is Aggregator Transformation Connected or Unconnected ?

Aggregator Transformation is a connected Transformation.

Components of Aggregator Transformation:

Aggregate cache: The Integration Service stores data in the aggregate cache until it completes aggregate calculations. It stores group values in an index cache and row data in the data cache.
Aggregate expression: Enter an expression in an output port. The expression can include non-aggregate expressions and conditional clauses.
Group by port: Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.
Sorted input: Select this option to improve session performance. To use sorted input, you must pass data to the Aggregator transformation sorted by group by port, in ascending or descending order.

You can configure the Aggregator transformation components and options on the Properties and Ports tab.

Configuring Aggregator Transformation Ports:

You can configure the following components on the ports tab

Port name: Add the name of the port.
Datatype, precision, and scale: Configure the datatype and set the precision and scale for each port.
Port type : A port can be input, output, input/output, or variable. The input ports receive data and output ports pass data. The output ports can pass aggregated data (use Aggregate function in Expression ). Variable ports store data temporarily and can store values across the rows.
Expression: Use the Expression Editor to enter expressions. Expressions use the transformation language, which includes SQL-like functions, to perform calculations. Example ( sum , Max,average)
GroupBy: Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified

Check here for expression transformation in informatica

Steps to create an Aggregator transformation

In the Mapping Designer, open a Mapping.
Click Transformation > Create. Select Aggregator transformation.

You can also select Transformation by clicking function button on Informatica Designer

Enter a name and click Done.

Select and drag the ports from the source qualifier or other transformations to add to the Aggregate transformation. You can also open the transformation and create ports manually.
Double-click on the title bar and click on Ports tab. You can create output and variable ports within the transformation.
Select the Ports tab.
Aggregator Transformation Step 4[/caption] Click the group by option for each column you want the Aggregator to use in creating groups. Optionally, enter a default value to replace null groups.

Click Add to add an expression port. The expression port must be an output port. Make the port an output port by clearing Input (I).
Optionally, add default values for specific ports.If the target database does not handle null values and certain ports are likely to contain null values, specify a default value. [caption id="attachment_158" align="alignnone" width="300"]
Aggregator Transformation Step 5

Configure properties on the Properties tab. Aggregator Transformation Step 6

To enhance the performance of Aggregator , it is recommended to provide Sorted Data to it (Via SQ query or by adding Sorter Transformation before it)
In case Sorted Input data is coming to Aggregator, check the "Sorted Input" option under the properties Tab.
Click OK.
Connect the output ports to a downstream transformation or target.