Getting Started

10.5.1
- 10.5
- 10.4.0

Back Next

Creating an Aggregator Transformation

Next, add an Aggregator transformation to calculate the average, maximum, and minimum prices of items from each manufacturer.

Click

Transformation

Create

to create an Aggregator transformation.

Select

Aggregator

in the

Select the transformation type to create

dialog box and type AGG_PriceCalculations in the

Enter a new name for this transformation

dialog box. Click

Create

, and then click

Done

The naming convention for Aggregator transformations is AGG_
TransformationName
.

The Mapping Designer adds an Aggregator transformation to the mapping.

The following image shows a mapping with an unconnected Aggregator transformation:

The mapping contains a source, source qualifier, and an unconnected Aggregator transformation and target.

Click

Layout

Link Columns

When you drag ports from one transformation to another, the Designer copies the port description and links the original port to its copy.

If you click

Layout

Copy Columns

, every port you drag is copied, but not linked.

From the Source Qualifier transformation, drag the PRICE column into the Aggregator transformation.

A copy of the PRICE port now appears in the new Aggregator transformation. The new port has the same name and datatype as the port in the Source Qualifier transformation.

The Aggregator transformation receives data from the PRICE port in the Source Qualifier transformation. You need this information to calculate the maximum, minimum, and average product price for each manufacturer.

Drag the MANUFACTURER_ID port into the Aggregator transformation.

You need another input port, MANUFACTURER_ID, to provide the information for the equivalent of a GROUP BY statement. By adding this second input port, you can define the groups for the aggregate calculation. You will create a group to organize the data by manufacturer.

Double-click the Aggregator transformation, and then click the

Ports

tab.

Clear the

Output (O)

column for PRICE.

You want to use this port as an input (I) only, not as an output (O). Later, you use data from PRICE to calculate the average, maximum, and minimum prices.

Select the

Group By

option for the MANUFACTURER_ID column.

Click the

Add

button three times to add three new ports.

When you select the

Group By

option for MANUFACTURER_ID, the Integration Service groups all incoming rows by manufacturer ID when it runs the session.

Configure the output ports.

The following table describes the output ports: