This page explains how Holistics works with additive and non-additive measures.
What are additive measures and non-additive measures?
Non-additive measures are measures/calculation that cannot be aggregated multiple times because it will still produce incorrect results.
- Additive measures usually involve COUNT, SUM calculations. These measures can be rolled up (aggregated) multiple times (e.g daily → weekly → monthly) and still produce correct results.
- Non-additive measures involve calculations like COUNT DISTINCT, AVERAGE, MEDIAN. Due to the nature of calculation, these measures cannot be rolled up multiples times because it will produce incorrect results.
Let's take an example of an
orders table where we're interested in reporting 2 key metrics (measures): a) Revenue, b) Active Users
You can aggregate Revenue in multiple aggregation rounds (daily → monthly). But you cannot aggregate Active Users the same way.
In other words, in the below 2 SQL statements, the calculation of monthly active users (MAUs) is incorrect.
MAUs cannot be calculated from DAUs, but have to get directly from raw:
In this case, Revenue is an additive metric, and Active Users is a non-additive metric.
How does Holistics modeling layer work with non-additive measures?
Holistics modeling can work well with non-additive measures when you have the raw data. Since Holistics can perform just-in-time SQL generation based on the list of fields the user select, instead of requiring pre-aggregation of data.
The above example can be modeled into Holistics modeling as:
And when the end-user explores the data, a corresponding SQL will be generated based on the selection of the user.