Aggregator transformation is an active transformation used to perform calculations such as sums, averages, counts on groups of data. The integration service stores the data group and row data in the aggregate cache. The Aggregator Transformation provides more advantages than the SQL, you can use conditional clauses to filter rows.
Group by
This component defines the group for a specific port (s) which participates in aggregation
Aggregate Expression
Use aggregate functions to drive the aggregate expression which can be developed either in variable ports (or) In only output ports
Sorted input
"Group by ports" are sorted using a sorted transformation and receive the sorted data as an input to improve the performance of data aggregation.
Keep the sorted transformation prior the aggregator transformation to perform sorting on fro up by ports.
Aggregate Cache
An integration service create aggregate ache for
Unsorted inputs
The aggregate cache contains group by ports, non group by input ports and the output port which contains aggregate expressions.
This transformation offers, even more, functionality than SQL’s group by statements since one can apply conditional logic to groups within the aggregator transformation. Many different aggregate functions can be applied to individual output ports within the transformation. One is also able to code nested aggregate functions as well. Below is a list of these aggregate functions:
AVG
COUNT
FIRST
LAST
MAX
MEDIAN
MIN
PERCENTILE
STDDEV
SUM
VARIANCE
Go to the Mapping Designer, click on transformation in the toolbar -> create.
Select the Aggregator transformation, enter the name and click create. Then click Done. This will create an aggregator transformation without ports.
To create ports, you can either drag the ports to the aggregator transformation or create in the ports tab of the aggregator.
Configuring the aggregator transformation.
| If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain. |
You can configure the following components in aggregator transformation in Informatica.
Aggregate Cache: The integration service stores the group values in the index cache and row data in the data cache.
Aggregate Expression: You can enter expressions in the output port or variable port.
Group by Port: This tells the integration service on how to create groups. You can configure an input, input/output, or variable ports for the group.
Sorted Input: This option can be used to improve session performance. You can use this option only when the input to the aggregator transformation in sorted on a group by ports.
You can nest one aggregate function within another aggregate function. You can either use single-level aggregate functions or multiple nested functions in an aggregate transformation. You cannot use both single-level and nested aggregate functions in an aggregator transformation. The Mapping designer marks the mapping as invalid if an aggregator transformation contains both single-level and nested aggregate functions. If you want to create both single-level and nested aggregate functions, create separate aggregate transformations.
Examples: MAX(SUM(sales))
You can reduce the number of rows processed in the aggregation by specifying a conditional clause.
Example: SUM(salary, slaray>1000)
This will include only the salaries which are greater than 1000 in the SUM calculation.
You can also use non-aggregate functions in aggregator transformation.
Example: IIF( SUM(sales) <20000, SUM(sales),0)
Note: By default, the Integration Service treats null values as NULL in aggregate functions. You can change this by configuring the integration service.
After you create a session that includes an Aggregator transformation, you can enable the session option, Incremental Aggregation. When the Integration Service performs incremental aggregation, it passes source data through the mapping and uses historical cache data to perform aggregation calculations incrementally.
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
