Wednesday, February 24, 2016

Aggregator Transformation In Informatica

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