• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Aggregator Transformation in Informatica

Aggregator Transformation In Informatica

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 aggregate cache. The Aggregator Transformation provides more advantages than the SQL, you can use conditional clauses to filter rows.

Properties of Aggregator Transformation

-Aggregate Expression

-Group by port

-Sorted Input

-Aggregate cache

-Unsorted Input

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 develop 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 a 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 ouptput port which contains aggregate expressions.

 Learn Informatica Online

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

Creating an Aggregator Transformation in Informatica

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.

Interested in mastering Informatica Training? Enroll now for FREE demo on Informatica Training Online.

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 how to create groups. You can configure input, input/output or variable ports for the group.

Sorted Input: This option can be used to improve the session performance. You can use this option only when the input to the aggregator transformation in sorted on group by ports.

Informatica Nested Aggregate Functions

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))

Conditional clauses

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.

Non Conditional clauses

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.

Incremental Aggregation in Informatica

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.

What is Incremental Aggregation

Using incremental aggregation, you can apply changes captured from the source to aggregate calculations such as Sum, Min, Max, Average etc… If the source changes incrementally and you can capture changes, you can configure the session to process those changes. This allows the Integration Service to update the target incrementally, rather than forcing it to process the entire source and recalculate the same data each time you run the session.

Incremental Aggregation Works

When the session runs with incremental aggregation enabled for the first time, it uses the entire source data. At the end of the session, the Integration Service stores aggregate data from that session run in two files, the index file and the data file, in the cache directory specified in the Aggregator transformation properties.
Each subsequent time you run the session with incremental aggregation, you use the incremental source changes in the session. For each input record, the Integration Service checks historical information in the index file for a corresponding aggregate group. If it finds a corresponding group, the Integration Service performs the aggregate operation incrementally, using the aggregate data for that group, and saves the incremental change. If it does, the Integration Service creates a new group and saves the record data.

For indepth understanding of  Transformation in Informatica click on

Summary
Review Date
Reviewed Item
Aggregator Transformation in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Aggregator Transformation in Informatica"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.