Tutorial corner

Informatica,ETL,oracle,sql/plsql

Aggregator Transformation in Informatica

Spread the love

Aggregator Transformation in Informatica , is a connected Active transformation which let you performs aggregate calculations, such as averages and sums on the group of data. Aggregator transformation is differ from Expression transformation , as  you use Aggregator  transformation to perform calculations on groups . For example: calculating averages, count first, last, max, median, min, percentile, stddev, sums and variance . The Expression transformation permits you to perform calculations on a row-by-row basis only.  In below article we will go through the properties of Aggregator Transformation. We will also discuss the steps of adding /configuring Aggregator  transformation in Informatica Mapping. We will also check the some performance tuning related guidelines of Aggregator Transformation.

Aggregator Transformation image

Aggregator Transformation in informatica

Business Purpose of Aggregator Transformation:

The Aggregator transformation is use to perform aggregate calculations for each data. Data can be modified using built-in functions . Sample calculation peformed by the Aggregate transformer are :

  • AVG, COUNT, MAX, MIN, SUM
  • FIRST, LAST
  • MEDIAN, PERCENTILE, STDDEV, VARIANCE

Is Aggregator Transformation Active or Passive ?

Aggregator Transformation is a Active  transformation as  it also enable you to use conditional clauses to filter rows.

Is Aggregator Transformation Connected or Unconnected ?

Aggregator Transformation is a connected Transformation.

Properties of Aggregator Transformation :

  • Active /Passive : Aggregator Transformation is a Active  transformation as  it also enable you to use conditional clauses to filter rows
  • Connected/Unconnected Transformation : Aggregator Transformation is a connected Transformation
  • Types of ports in Aggregator Transformation:
    • Input : For reading input Data
    • Output : For providing output Data
    • Variable: Used to store any temporary calculation

Components of Aggregator Transformation:

  • Aggregate cache:  The Integration Service stores data in the aggregate cache until it completes aggregate calculations. It stores group values in an index cache and row data in the data cache.
  • Aggregate expression:  Enter an expression in an output port. The expression can include non-aggregate expressions and conditional clauses.
  • Group by port:  Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.
  • Sorted input:  Select this option to improve session performance. To use sorted input, you must pass data to the Aggregator transformation sorted by group by port, in ascending or descending order.

You can configure the Aggregator transformation components and options on the Properties and Ports tab.

Configuring Aggregator Transformation Ports:

You can configure the following components on the ports tab

  • Port name:  Add the name of the port.
  • Datatype, precision, and scale:  Configure the datatype and set the precision and scale for each port.
  • Port type : A port can be input, output, input/output, or variable. The input ports receive data and output ports pass data. The output ports can pass aggregated data (use Aggregate function in Expression ). Variable ports store data temporarily and can store values across the rows.
  • Expression:  Use the Expression Editor to enter expressions. Expressions use the transformation language, which includes SQL-like functions, to perform calculations. Example ( sum , Max,average)
  • GroupBy:  Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified

Check here for expression transformation in informatica

Configuring Aggregator Transformation Properties :

Modify the Aggregator Transformation properties by clicking on the Properties tab.

Property Description
Cache Directory Local directory where the Integration Service creates the index and data cache files.By default, the Integration Service uses the directory entered in the Workflow Manager for the process variable $PMCacheDir. If you enter a new directory, make sure the directory exists and contains enough disk space for the aggregate caches. If you have enabled incremental aggregation, the Integration Service creates a backup of the files each time you run the session. The cache directory must contain enough disk space for two sets of the files
Tracing Level Amount of detail displayed in the session log for this transformation.
Sorted Input Indicates input data is presorted by groups. Select this option only if the mapping passes sorted data to the Aggregator transformation
Aggregator Data Cache Size Data cache size for the transformation. Default cache size is 2,000,000 bytes. If the total configured session cache size is 2 GB (2,147,483,648 bytes) or greater, you must run the session on a 64-bit Integration Service. You can configure the Integration Service to determine the cache size at run time, or you can configure a numeric value. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache
Aggregator Index Cache Size Index cache size for the transformation. Default cache size is 1,000,000 bytes. If the total configured session cache size is 2 GB (2,147,483,648 bytes) or greater, you must run the session on a 64-bit Integration Service. You can configure the Integration Service to determine the cache size at run time, or you can configure a numeric value. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to
allocate to the cache.
Transformation Scope Specifies how the Integration Service applies the transformation logic to incoming data:

  • Transaction. Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction, but does not depend on rows in other transactions.
  • All Input. Applies the transformation logic on all incoming data. When you choose All Input, the PowerCenter drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.

Check here for filter transformation in informatica

How to use Aggregator transformation in Informatica:

Use the following procedure to create an Aggregator transformation in Informatica mapping.

Steps to create an Aggregator transformation:

  • In the Mapping Designer, open a Mapping.
  • Click Transformation > Create. Select Aggregator transformation.
  • You can also select Transformation by clicking  function  button on Informatica Designer

    Aggregator Transformation short cut

    Aggregator Transformation short cut

  • Enter a name and click Done.
  • Select and drag the ports from the source qualifier or other transformations to add to the Aggregate  transformation. You can also open the transformation and create ports manually.
  • Double-click on the title bar and click on Ports tab. You can create output and variable ports within the transformation.
  • Select the Ports tab.
  • Aggregator Transformation Step 6

    Aggregator Transformation Step 4

    Click the group by option for each column you want the Aggregator to use in creating groups. Optionally, enter a default value to replace null groups.

  • Click Add to add an expression port. The expression port must be an output port. Make the port an output port by clearing Input (I).
  • Optionally, add default values for specific ports.If the target database does not handle null values and certain ports are likely to contain null values, specify a default value.

    Aggregator Transformation Step 5

    Aggregator Transformation Step 5

 

  • Configure properties on the Properties tab.

    Aggregator Transformation Step 6

    Aggregator Transformation Step 6

 

  • To enhance the performance of Aggregator , it is recommended to provide Sorted Data to it (Via SQ query or  by adding Sorter Transformation before it)
  • In case Sorted Input data is coming to Aggregator, check the “Sorted Input” option under the properties Tab.
  • Click OK.
  • Connect the output ports to a downstream transformation or target.

Note: After you make the transformation reusable, you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation.

Example of Aggregator Transformation:

Check here for Example of Aggregator transformation in informatica

Aggregator Transformation Performance Tuning :

  • Use Sorter Transformation before aggregator Transformation to improve the session performance.
  • Use required data port only in aggregator to reduce the size of  data cache which hold this data.
  • If you are filtering the data in mapping then add filter in starting itself to avoid unnecessary processing of data.
  • Ports are evaluated in the following order: input ports first, then variable ports in the display order (from top to bottom), then output ports.

So , in this article we have gone through properties of Aggregator Transformation in Informatica  ,component/Properties of Aggregator Transformation , scenarios where to use it , steps to create it and  performance related guidelines. Hope you enjoyed the article. Please feel free to give your feedback.

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