Aggregate Transformation

The Aggregate transformation object provides the functionality to create aggregations of your dataset, using aggregate functions such as Sum, Count, First, Last, Min, Max, Average, Var or StDev. The dataset can be split into groups so that the aggregate value(s) can be generated for the group instead of the whole dataset. For example, calculate product count by month of year, or get average sales price by region and year.

Aggregate Transformation can be applied to unsorted data or data sorted by group by values. When applied to an input stream that is sorted on group by fields, Aggregate Transformation performs substantially better and consumes very little memory. On the other hand, when applied to unsorted data set, Aggregate Transformation may consume substantial memory resources for large data sets and may slow down the performance of the server.

Usage

Steps

To add an Aggregate transformation, drag the Aggregate object from the Transformations group in the Flow toolbox and drop it on the dataflow.

An example of what an Aggregate transformation might look like is shown below.

To configure the properties of an Aggregate object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:

Record Layout screen:

Using Record Layout screen, you can select the aggregate functions you wish to create for your dataset, as well as apply any group-by conditions to split your dataset into groups.

Note: The available aggregate functions are specific to the selected data type of the field that will store the aggregate value.

A field’s data type can be selected in the Data Type dropdown.

Select an aggregate function from the Aggregate dropdown. At least one field in the Record Layout screen should be assigned as a Group-By field. Select Group-By from the Aggregate dropdown for the field whose values will be used to create data groups.