Data Aggregation

Aggregation is used to merge several values in a single element. For example, for calculation of parent element based on selected child elements or for calculation of element data based on several fields.

Data is merged by means of various mathematical functions, for example, the sum or arithmetical mean of all aggregated values.

Aggregation of Cube Fact Dimension

It enables the user to merge data source fields that are not linked to cube dimensions. Aggregation result is passed to cube fact dimension.

Depending on the calculation location, the aggregation is divided into two types:

Use the Facts Binding page of the standard cube creation wizard to set up aggregation.

Select the type of cube data aggregation in the drop-down list of the Aggregation Method column:

None. No aggregation.
Sum. Source level elements are summed up.
Minimum. The minimum value is selected on the source level
Maximum. The maximum value is selected on the source level
Number of Non-Empty. The number of non-empty values is defined on the source level.
Number of Empty. The number of empty values is defined on the source level.
Number of All Children. The number of child elements is defined on the source level
Arithmetic Mean. The arithmetic mean of the level elements is defined, considering empty values.
Actual Mean. The actual mean value of the level elements is defined, not considering empty values.
First Actual. The first actual value is found on the source level.
Last Actual. The last actual value is found on the source level.
Root-mean-square Deviation based on Sample. Root-mean-square deviation is defined for row by sample data.
Median. A median is defined for source level values.
Root-Mean-Square Deviation. A root-mean-square series deviation based on the entire population is defined.
Number of Different Values. The number of unique values is defined on the source level.

This example is based on the example of creating a cube but the src_id field will not be linked to a separate dimension.

Each value (zn) has the same data presented from different subjects (src_id). To determine the most appropriate value, set up displaying of the maximum available value.

The table used as a data source for cube contains the fields:

src_id. The field that contains data of the subject that provides data. The field is not linked to dimension.
zn. The field with values. The field is used for linking to fact dimension.
ter_id. The field for linking of territories.
sep_id. The field for linking indicators dimension.
v_date. The field for linking of calendar.

On the Facts Binding page:

Set up the Maximum aggregation method for the fact.
Specify required settings in the Source Properties additional panel.

In the Dimensions page create and bind dimensions for territories (ter_id), indicators (sep_id) and calendar (v_date). The src_id field remains unused.

The source table contains the following data:

As a result, on working with a cube, if the 2002 year element is displayed, the indicator (with the index 30800) for the observation object (with the index 49) will have the value 1643.7.

NOTE. If cube fact dimension aggregation is set, it is not available to use calculated expressions in linked cube facts.

Aggregation by Dimension Levels

It enables the user to merge data of dimension elements by levels. Result of aggregation of child elements is passed to parent element.

Aggregation is set up for a separate cube display variant on the Aggregation page of standard or automatic cube creation wizard.

Aggregation method depends on the selected aggregation mechanism:

Basic aggregation mechanism. It is set up for any dimension, including a calendar one.
Chronological aggregation mechanism. It is set up only for a calendar dimension.

Two cube layouts were created during setup:

Standard layout. Parent elements do not contain values:

Layout with aggregation. Data from all child elements is aggregated to parent elements. Aggregation method is arithmetic mean by territories levels:

NOTE. If cube with aggregation by dimension levels is displayed, on setting up totals the From Source calculation method is available in the table visualizer.

Aggregation by Alternative Hierarchy Levels

It enables the user to merge data of dimension elements by alternative hierarchy levels. Result of aggregation of child elements is passed to parent element.

Aggregation is set up for a separate cube display variant on the Aggregation page of standard or automatic cube creation wizard.

Aggregation method depends on the selected aggregation mechanism:

Basic aggregation mechanism. It is set up for any dimension, including a calendar one
Chronological aggregation mechanism. It is set up only for a calendar dimension.

The setup by alternative hierarchy levels is the same as setup of aggregation by dimension levels.

On selecting the Alternative Calendar item, the aggregation by alternative hierarchy levels is applied, which was determined on setting up standard or automatic cube aggregation.
On selecting the Reset item, the settings stored in cube main dimension and alternative hierarchies will be reset. The reset is made for selected dimension.

Aggregation of Fixed Dimensions for Metrics

It enables the user to merge data of all elements selected in fixed dimensions. Aggregation result is passed to a tool visualizer. The aggregation method on dimensions is available for each dimension metric with the Metric role.

In case of multiple selection by the Value metric, the aggregation type of the first cube element is applied.

Select the type of cube data aggregation in the drop-down list of the Aggregation Method row:

Sum. Source level elements are summed up.
Minimum. The minimum value is selected on the source level.
Maximum. The maximum value is selected on the source level.
Number of Non-Empty. The number of non-empty values is defined on the source level.
Number of Empty. The number of empty values is defined on the source level.
Number of All Children. The number of child elements is defined on the source level.
Arithmetic Mean. The arithmetic mean of the level elements is defined, considering empty values.
Actual Mean. The actual mean value of the level elements is defined, not considering empty values.
First Actual. The first actual value is found on the source level.
Last Actual. The last actual value is found on the source level.
Root-mean-square Deviation based on Sample. Root-mean-square deviation is defined for row by sample data.
Median. A median is defined for source level values.
Root-Mean-Square Deviation. A root-mean-square series deviation based on the entire population is defined.
Number of Different Values. The number of different values is defined on the source level.