#
__Aggregator Transformation__

Ø
An aggregator transformation performs
calculations on groups of data

Ø If you
do not group values, the Integration Service returns one row for all input
rows.

Ø The
Integration Service typically returns the last row of each group (or the last
row received) with the result of the aggregation

Ø When
selecting multiple group by ports in the Aggregator transformation, the
Integration Service uses port order to determine the order by which it groups.
Since group order can affect the results, order group by ports to ensure the
appropriate grouping

Ø This
transformation can be used to aggregate data coming from Relational &
Non-Relational data sources.

__Conditional Clauses__
Ø you
can use conditional clauses in the aggregate expression to reduce the number of
rows used in the aggregation.

Ø The
conditional clause can be any clause that evaluates to TRUE or FALSE.

For example, use
the following expression to calculate the total commissions of employees

who exceeded
their quarterly quota: SUM( COMMISSION, COMMISSION > QUOTA)

__Non-Aggregate Functions__
Ø
You can also use non-aggregate functions
in the aggregate expression.

The following
expression returns the highest number of items sold for each item (grouped by
item).

If no items were
sold, the expression returns 0.

IIF( MAX(
QUANTITY ) > 0, MAX( QUANTITY ), 0))

__Null Values in Aggregate Functions__
Ø
When you configure the Integration
Service, you can choose how you want the Integration Service to handle null
values in aggregate functions. You can choose to treat null values in aggregate
functions as NULL or zero. By default, the Integration Service treats null
values as NULL in aggregate functions.

__Performance Improvements with Aggrgator Transformation: Sorted Input Option__
Ø The Aggregator performance can be increased if you
enable the option 'Sorted Input'

Ø
If sorted input option is configured then
data should be sorted on group by column of Aggregator

__Do not use sorted input if either of the following conditions are true:__
1. The session
uses incremental aggregation

2. The aggregate
expression uses nested aggregate functions.

__Elimination of Duplicate records using Aggregator Transformation:__
Ø check
group by option for the key element and pass the records to target.

Ø If
there is no key column for the incoming source data then check the group by
option for all the ports, but this is a costly option in terms of performance of
Aggregator.

## No comments:

## Post a Comment