Semi-additive measures over time in data cubes

A semi-additive measure over time is a metric that can be summed across some dimensions but not others, typically not across the Time dimension. For dimensions where the measure is not additive, an alternative calculation logic must be specified. In data cubes, when semi-additive measures are used with the Time dimension, the default aggregation method is inadequate and different calculations are required.

For example, the Account dimension in a cube contains the base elements Headcount and Salary Costs. By default, both are summed to derive quarterly and annual totals. This makes sense for the Salary Cost element but not for the Headcount element. The option now exists to specify an alternate time calculation method for headcount. For example, to take the value from the first base period, last base period or the average across the base periods.

You must have the Administer OLAP Databases permission to implement this feature.

Time calculation methods in OLAP

These are the time calculation methods:

Aggregation: Default method for consolidated cells using base cell aggregation.
Last Period: Gets the last period child value. Or, if configured, uses the specified child from which to get the value.
First Period: Gets the first period child value. Or, if configured, uses the specified child from which to get the value.
Average Weighted: Calculates the weighted average of the child values. The weights are determined by the number of base descendants of each child. Alternatively, the weights can be configured individually. There are two Average Weighted methods that can be configured independently. For example, method 1 to calculate monthly average weighted by month and method 2 to calculate the monthly average weighted by working days in the calendar.

Note: The default behavior of the Last Period and First Period methods only works for Time dimensions with a natural order of the elements in the hierarchy structure. For example, the natural order of the children of the parent Quarter 1 are January, February, and March in this order.

Calculation dimension

The Calculation dimension is the Format dimension of the data cube. If no Format dimension is defined, then it is the Measure dimension. If neither a Format dimension nor a Measure dimension is defined, then semi-additive calculations are not available.

The Format dimension and the Measure dimension are properties of a data cube and can be set by an administrator in the Edit Database dashboard.

The Calculation dimension must include the SAM_MODE dimension attribute that defines the time calculation method. Each element of the Calculation dimension can have an individual time calculation method. These are the possible dimension attribute values:

Aggregation: Empty dimension attribute value.
Last Period: The value of the dimension attribute is SAM_LA.
First Period: The value of the dimension attribute is SAM_FI.
Average Weighted: The value of the dimension attribute is SAM_AD1 or SAM_AD2.

Note: Dynamic attributes cannot be used to specify time calculation methods.

Time dimension

The Time dimension can be enriched with some of the SAM_LA, SAM_FI, SAM_AD1, or SAM_AD2 attributes. These attributes can be used for a detailed definition of the aggregations in the Time dimension. If an attribute has not been configured, OLAP calculates the factors automatically based on the structure of the dimension hierarchy.

Only one Time dimension per data cube is supported. For example:

Business case 1: A data cube contains the Year and Period dimensions. Here, Period should be used as Time dimension.
Business case 2: A data cube contains the Order Date and Delivery Date dimensions. Here, semi-additive calculations across both Time dimensions are not possible.

The dimension type can be set to Time in the Advanced Properties of the Edit Database dashboard by changing the corresponding line to <Alea:ODBOType Code="1" />.

Preparation of the calculation factors

When the elements in the Time dimension or the attributes mentioned in this topic are changed, the calculation factors must be prepared.

You can achieve this by using the XMLAExecuteXML OLAP Runtime function of Application Engine to call Dimension PrepareSemiAdditiveCalculations to prepare the calculation factors.

OLAP calculation procedures

Error situations are detected and written to the application log.

You can obtain detailed information on cell calculations by using the XMLAExecuteXML OLAP Runtime function of Application Engine to call Cube GetCellDebugInformation.