[Informatica] Aggregator Transformation

Aggregator Transformation

::Transformation type::
Connected
Active


::Use::
Aggregator Transformation allows you to perform calculations such as Averages, Sums, Mean, Standard Deviation on individual data sets or a set of data ie. group. The Aggregator Transformation diffres from the Expression transformation because Expression Transformation allows you to process data on a row-by-row basis only.

::Components in Aggregator Transformation::
- Aggregate Expression . Can include vaious aggregate functions such as Sum, Mean  etc. along with non-aggregate functions. The complete list of availabe Aggregate functions is as follows. Also, the Aggreagator transformation can use nested aggregate functions.
AVG
COUNT
FIRST
MAX
LAST
MEDIAN
MIN
PERCENTILE
STDDEV
SUM
VARIANCE

- Group by port. Allows you to select the column on which the data is to be grouped. We can select multiple group by ports. Also, the order of the group by ports plays an important role in determination of result set.
Eg. Finding the sales of each Region in Country. Group by Region and SUM(SALES).
While using multiple Group by ports, the order of the ports is important. For example, the result of grouping by SALES_PERSON and then REGION is diffrent than grouping by REGION and then SALES_PERSON.


::Performance Improvement::
- The PowerCenter Server stores data in the aggregate cache until it completes aggregate calculations. We can speed up the process of calculation by providing sorted input to the Aggregator Transformation, thereby greatly improving the performance.
- When used with sorted input, Aggregator performs calculation as it receives the data. Alternately, when the input is not sorted PowerCenter Server stores data for each group until it reads the entire source to ensure all aggregate calculations are accurate.
- Filtering data before Aggregator is another strategy as it limits the data to be passed through the Aggregator. Avoids superflous aggregation.


::Note::
- If you do not group values, the PowerCenter Server returns one row for all input rows
- By default, NULL values are treated as null in aggregattion. If you do not want this,, you must Default the NULL values before the Aggregation.