Semi-Additive Metrics
Semi-additive metrics are those whose values can be summarized for some dimensions of a model, but not all. Ratios such as Average are also considered semi-additive metrics.
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 metric. 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 metric 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 define the semi_additive
property in the
metric's SML, specifying the dimensions 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. For more
information, see Add Semi-Additive
Metrics.
In Tableau, semi-additive metrics 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 metric was defined in AtScale. This is because SQL clients do not understand the concept of semi-additive metrics, and this is how AtScale informs the client that the memetricasure is non-additive. Users should not change the aggregation function on the client-side in Tableau for these semi-additive metrics.
Limitations on Embedded Dimensions in Semi-Additive Metrics
For semi-additive metrics that contain embedded dimensions, the following restrictions apply to the non-additive component of first non-empty/last non-empty value metrics:
- 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.