Aggregator Transformation in Informatica with Example

What is Aggregator Transformation?

Aggregator transformation is an active transformation that performs aggregate calculations such as sum, average and count.

For example, if you want to calculate the sum of salaries of all employees department wise, you can use the Aggregator transformation.

The aggregate operations are performed over a group of rows, so a temporary placeholder is required to store all these records and perform the calculations.

For this, the aggregator cache is used. This is temporary main memory allocated to the Aggregator transformation to perform such operations, and it is held in two parts: the index cache stores the group values while the data cache stores the row data being aggregated.

The transformation is active because it changes the number of rows in the pipeline. Several thousand employee records can enter it and only one row per department leaves it.

How to Use Aggregator Transformation in Informatica

In this example, we will calculate the sum of salaries department wise. For this, we require a new column to store this sum. So, first of all, we will prepare a new column.

Step 1) Create a new database target table, for example, say “sum_sal_deptwise”, using the script below. You will see the new database target table created under the Targets folder in the next step.

Download the above Create_table_sal_deptwise.txt File

Step 2) Create a new mapping “m_sum_sal_deptwise”.

In order to create the new mapping, we need the source table (EMP) and the target table (sum_sal_deptwise) in Mapping Designer, so we need to

1. Import the target table “sum_sal_deptwise” in the mapping.
2. Import the source table “emp”.

Both definitions now appear on the canvas, as shown below.

Step 3) In the mapping,

1. From the Source Qualifier, delete the columns empno, ename, job, mgr, hiredate and comm, leaving only the columns deptno and sal.
2. Create a new Aggregator transformation using the toolbox menu as shown in the screenshot. When you click the aggregator icon, a new Aggregator transformation will be created.

The new AGGTRANS object appears beside the trimmed Source Qualifier.

Step 4) Drag and drop the SAL and DEPTNO columns from the Source Qualifier (SQ_EMP) to the Aggregator transformation. The two ports are now linked into AGGTRANS.

Step 5) Double-click the Aggregator transformation to open its properties, and then

1. Add a new port in the transformation
2. Rename the port to SUM_SAL
3. Change the data type of this new port to double
4. Make this port an output port by selecting the checkbox of the output port
5. Click the expression option

The Ports tab now lists SAL, DEPTNO and the new SUM_SAL output port.

Step 6) In the Expression Editor window

1. Add the expression sum(SAL); you have to write this expression yourself.
2. Select the OK button, this will bring back the Edit Transformations window.

The Expression Editor shows the aggregate expression assigned to SUM_SAL.

Step 7) In the Edit Transformations window, select the option “GroupBy” by marking the check box against the deptno column and click OK. By selecting group by against deptno, we are instructing Informatica to group salaries by deptno.

Step 8) Link the deptno and sum_sal columns from the Aggregator transformation to the target table. The mapping is then complete from source to target.

Now save the mapping and execute it after creating a new session for this mapping. The target table would contain the sum of salaries department wise. In this way, we can use the Aggregator transformation to calculate aggregate results.

Group By Ports in Aggregator Transformation

The step above marked a single port as Group By, which is what turned a company-wide total into one total per department. Any input, input/output, output or variable port can be marked in the same way.

Three rules govern the result set:

• One row per group. When values are grouped, the Integration Service produces one row for each unique combination of the group by ports.
• No group by, one row. If no port is marked, the whole input is treated as a single group and one row is returned for all input rows.
• The last row wins. Alongside the aggregate result, the Integration Service usually passes the last row received in the group, unless a function such as FIRST names a different row.

Where several ports are marked, port order decides the grouping order, and the order can change the result. Grouping by DEPTNO and then JOB is not the same as grouping by JOB and then DEPTNO, because the second column values are not necessarily unique.

Aggregator Transformation Properties

The Properties tab of the Edit Transformations window carries the settings that decide where the cache lives and how much of it is used. The table below lists them.

Setting	What it controls
Cache Directory	Local directory in which the Integration Service creates the index and data cache files. The default is the $PMCacheDir process variable set in Workflow Manager.
Tracing Level	Amount of detail written to the session log for this transformation.
Sorted Input	Declares that incoming data is already sorted by the group by ports. Select it only when the mapping really does deliver sorted data.
Aggregator Data Cache Size	Size of the data cache. The default is 2,000,000 bytes, and Auto lets the Integration Service size it.
Aggregator Index Cache Size	Size of the index cache. The default is 1,000,000 bytes, and Auto lets the Integration Service size it.
Transformation Scope	Applies the logic to each transaction, or to all incoming data. All Input drops the incoming transaction boundaries.

When incremental aggregation is enabled, the Integration Service writes a backup of the cache files on every run, so the cache directory has to hold two sets of files rather than one.

Aggregate Expression Rules and Performance Tips

An aggregate expression may combine an aggregate function with conditional clauses and non-aggregate functions, which makes conditional sums and counts possible inside one port. Two rules constrain what can be written.

• One level of nesting. Only one aggregate function may be nested inside another, and the inner expression is evaluated first.
• No mixing. A transformation may contain single-level functions or nested functions, never both. If it contains both, the Designer marks the mapping invalid, so split the logic across two Aggregator transformations.

On the tuning side, three settings do most of the work:

• Sorted input. When rows arrive sorted by the group by ports, each group can be released as soon as its last row arrives, so far less data is cached and the session runs faster. Sorted input cannot be combined with incremental aggregation.
• Incremental aggregation. New source rows are passed through the mapping and combined with the historical cache instead of recalculating history, which suits a nightly load onto a running total.
• Filter early. Rows that will never contribute to a total should be dropped before the Aggregator, either in the Source Qualifier query or in a Filter transformation, because every row that reaches the Aggregator costs cache memory. This is one of the standard checks during performance tuning.

FAQs