Semi-Additive Measures

Semi-additive measures are those whose values can be summarized for some dimensions of a cube, but not all. Ratios such as Average are also considered semi-additive measures.

For example, suppose you have a column that tracks employee headcount. The headcount yesterday was 10 employees, today it is 11, so does this make the total for the month 21? Definitely not! This is an example of a semi-additive measure. While headcounts of two regions or two departments can be summed up, the changes across time for a single region or department should not be summed up. So, headcount is a semi-additive measure across time. In this example, we want to look at the last non-empty value we have in a given month to determine our total headcount.

For this use case, you'd choose a Semi-Additive Measure option when you added the measure to your cube model, specifying the dimension over which the values should not be summarized (usually a date or time dimension). Instead, the query engine returns either the First Non-empty or Last Non-empty value in the result set when the results are sorted by time.

note

In Tableau, semi-additive measures will appear as CNTD (Count Distinct) aggregations in the client application if the return value is an integer and AVG if the value is not an integer. However, the actual query will use whatever aggregate calculation you specified when the measure was defined in AtScale. This is because SQL clients do not understand the concept of semi-additive measures, and this is how AtScale informs the client that the measure is non-additive. Users should not change the aggregation function on the client-side in Tableau for these semi-additive measures.

note

To configure the default totaling behavior for semi-additive measures, you can use the query.planning.semiAdditive.defaultAggregationWhenOutOfScope engine setting. When this setting is set to True (default), the first/last non-empty subtotal or grand total is shown only when the query contains one of the measure's configured semi-additive dimension attributes. When this setting is False, the first/last non-empty subtotal or grand total is shown when the query does not contain a configured semi-additive dimension attribute.

You do not need to restart the engine after changing this setting. For more information on engine settings, see Engine Level Configuration Settings.

Limitations on Embedded Dimensions in Semi-Additive Measures

For semi-additive measures that contain embedded dimensions, the following restrictions apply to the non-additive component of first non-empty/last non-empty value measures:

• Only the leaf levels of the embedded dimension hierarchies can be referenced. Note that this does not restrict the ability to query at higher levels of the embedded dimension hierarchy.
• Embedded dimensions cannot be referenced via many-to-many relationships.
• Embedded dimensions cannot be referenced through a path that involves role-playing.