Tutorial corner

Informatica,ETL,oracle,sql/plsql

Informatica Performance Tuning Tips Aggregator transformation

Spread the love

Informatica Performance Tuning Tips Aggregator transformation .Aggregator transformation in Informatica is one of the most popular Informatica Transformation. In below article,we will go through some of the guidelines related to performance tuning of Aggregator Transformation in Informatica.

Check here for Lookup transformation performance tuning tips

First basic rule for tuning aggregator transformation is to avoid aggregator transformation unless…

  • You really cannot do the aggregation in the source qualifier SQL query (e.g. Flat File source)
  • Fields used for aggregation are derived inside the mapping

Informatica Performance Tuning Tips Aggregator

Selecting Group By port in Aggregator transformation

While marking the port as grouping port , you should take care of below points :-

  • Whenever possible, use numbers instead of string and dates in the columns used for the GROUP BY.
  • Avoid complex expressions in the Aggregator expressions.

Sort the input for Aggregator Transformation

Use sorted input wherever possible ensuring that the group by ports are in the same order at the top of the transformation as the order by clause of the incoming data.

Informatica Performance Tuning Tips Aggregator

Informatica Performance Tuning Tips Aggregator

Check here for Expression transformation performance tuning tips

Aggregation in Informatica mapping

  • Minimize aggregate function calls.
  • Optimise the expression e.g. sum (A) + sum (B) is more complex but gives the same results as sum (A+B).

Reducing Number of Cached Rows in Aggregator

  • Filter the data before aggregation it. It is recommended to have filter transformation in the mapping, prior to the Aggregator transformation. This reduces unnecessary aggregation.

Other Informatica Performance Tuning Tips Aggregator

  • Limit the number of connected input/output or output ports to the ports required in the target table. This reduces the amount of data the Aggregator transformation stores in the data cache.
  • Use incremental aggregation

Check here for Expression transformation performance tuning tips

In above article , we discussed about the various technique of performance tuning for  Aggregator transformation in Informatica , overriding the various property of Aggregator transformation etc and these are important interview question as well :)

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme