OLAP Server User Guide
MDX Documentation
MDX Functions Reference

Measure Aggregation Types

When creating fact measures we need to define its aggregation type. The aggregation type defines how the engine is going to calculate the measure value. Those calculation, as opposed, to calculated members are specially optimized for the best performance.

There are two main families of aggregations in icCube. The first ones are independent from any hierarchy, as for example the sum. The second one needs the definition of a rollup hierarchy to calculate their value. As an example, the average on children that needs to define a hierarchy to get the children.

Aggregation types without rollup hierarchy

Sum

This is the default aggregation type, all row values are aggregated using the sum operator.

Count (rows)

Counts the number of rows that are not empty. Pay attention that rows and cells are usually the same but might be different. You've to set 'aggregate facts' field to true in order to aggregate rows by cell, but take care as this might be time consuming for large facts during the load phase.

Distinct Count

Counts the number of 'distinct' (different) row values.

Min/Max

Returns the minimum or maximum row value.

Average (rows)

Returns the average of existing rows (no empty).

Aggregation types with rollup hierarchy

Average (children)

Returns the average of no empty children. This aggregation type needs a rollup hierarchy.

Open

Returns the first leaf member value as defined in the rollup hierarchy (e.g. first day of a year).

Close

Returns the last leaf member value as defined in the rollup hierarchy (e.g. last day of a year).

Open (no empty)

Returns the first 'no empty' leaf member value as defined in the rollup hierarchy (e.g. first day of a year with a value).

Close (no empty)

Returns the last 'no empty' leaf member value as defined in the rollup hierarchy (e.g. last day of a year with a value).

Sum Square

Returns the sum all row 'square' value ( value * value ). Handy for doing statistics: variance, standard deviation...

Unary Operator (+-*/~)

Redefines the aggregation for the rollup hierarchy depending on the value of a member property ('@uo').

  • + (plus sign), the value of the member is added to the parent's value
  • – (minus sign), the value of the member is subtracted from the parent's value
  • * (asterisk), the value of the member is multiplied by the sum of the previous siblings, and added to the parent's value
  • / (slash mark), the value of the member is divided by the sum of the previous siblings, and added to the parent's value
  • ~ (tilde), the value is ignored to calculate the parent's value. But taken into account for '*', '/' calculations

Examples

You can check the different types of aggregations with the fact walkthrough schema (download). In order to have an overview of the different types you might run the following query :

SELECT
    [Measures].members ON 0,
    [Time].[Time].members - [time].[Time].[Month] ON 1  // get rid of the month level in the rollup hierarchy
FROM [Aggr. Type]
    

OLAP Fact creation

For the Unary Operator aggregation type you can run the following request :

WITH
 MEMBER [UO] as [Account Structure].[Account Structure].properties("@UO")
SELECT
 { [UO],[Unary Operator] } on 0,
 [Account Structure].[Account Structure].members on 1
FROM [Aggr. Type]
    

OLAP Fact creation

Next chapter: Facts: Many to Many shows how to create facts with a many-to-many relation.