In certain scenarios, especially when using time dimensions, it may feasible to aggregate not in the default additive manner, but using different kinds of aggregation. You can define semi-additive aggregations for certain measures that will be used for aggregations in one specific dimension.
The following settings are possible:
- Average of children
Note: if you are using Semi Additive Measures, then the children from the year (YTD elements) are part of the aggregation and the average. In this case, the calculated average differs from the normal calculated average.
- First child
- Last child
You can also define some exceptional elements in the used-time dimension to be excluded from the calculation.
Across all other dimensions used in a given cube, values will be aggregated normally, as sums.
Defining time aggregation behavior
To define the semi-additive behavior for a given cube, open the Modeler and select the cube. For the demonstration, we use the cube “Sales” in the database “Demo”.
In the tab "Cube Sales Properties" you can set “Dimension Time” in the column “Semi-additive” for the dimension that is used as the time dimension in the cube:
Dimensions that have been designated as a time dimension have access to the Time Editor, where various time-related settings can be adjusted.
In the same way, you can set the semi-additive behavior for the dimension “Measures”.
After this setting you can define the aggregation type for the elements in the dimension “Measures”. Possible types are “Average of children”, “First child”, or “Last child”.
Right-click the element whose behavior you want to change. Here we set “Average of children” for the element “Units”:
Defining exceptions from aggregation behavior
In some cases, certain elements within a time dimension may need to be used differently in the semi-additive aggregations. For example, if Data Total elements are used, and you want to store some data as total values rather than quarterly/monthly/weekly granularity, such elements should not be included as part of an average calculation in the same way as quarter/month/week elements.
Another example might be opening or closing periods and their inclusion in "First child" and "Last child" aggregations.
To define an exception for an element in the time dimension, right-click the element in Modeler. In the context menu, expand the sub-menu "Aggregation inclusion", then select either "Data total", "Include in first/last", or "Remove from first/last":
Updated July 8, 2024