Semi-additive measures
A semi-additive measure, also known as snapshot data, is a measure that is usually aggregated for all attributes except for date and time. For certain measures, like inventory, you want to know what the value was at the beginning or end of a period. In that case, you can use the last_value function to find the inventory at the end of a month, rather than aggregating all values over the month. Similarly, you can use the first_value function to find out your stock at the beginning of a month.
The last_value and first_value functions leverage the equivalent SQL functions to return the last or first value for the defined data partition. This is not necessarily the last date of the date partition, but rather the last date for which there is a data entry. Note that if the underlying database does not support the last value or first value SQL functions, you cannot use these functions in ThoughtSpot.
The first_value and last_value functions return the first or last value of the defined partition. The partition is defined with the query_groups() setting. This does not necessarily correspond to the first or last value of a date bucket. For example, the value returned may not be the last day of the month (but rather the last day in the month that had a value). The reason for this is that NULL (or no records) is not the same as a record of zero value.
|
Fully additive, semi-additive, and non-additive measures
| Measure Type | Definition | Summed Across Dimensions? | Summed Across Time? | Example KPIs |
|---|---|---|---|---|
Fully Additive |
Measures that can be aggregated across all dimensions, including time. |
Yes |
Yes |
Sales Revenue, Quantity Sold, Total Transactions |
Semi-additive |
Measures that can be aggregated across some dimensions but require special handling for time-based aggregation. |
Yes |
No (Require a function like FIRST_VALUE or LAST_VALUE for time-based aggregation.) |
Account Balances, Inventory Levels, Employee Headcount |
Non-Additive |
Measures that cannot be aggregated across any dimension. Typically require different aggregation logic (for example, ratios, averages). |
No |
No |
Profit Margins, Conversion Rates, Average Order Value |
Modeling semi-additive data
This data is usually modeled as a snapshot balance, where each row represents the value or balance at a specific point in time for a given level of detail (grain).
For example, consider inventory tracking where stock levels are recorded daily for each product and store. Here, the grain of the data is at the date, product, and store level. Additional attributes such as product categories, product groups, and store location details would be available in their respective dimensional tables.
By structuring semi-additive data in this way, businesses ensure they are aggregating and analyzing their data correctly—retrieving the appropriate balance at a given date while allowing roll-ups across relevant dimensions.
| When using first_value or last_value functions for semi-additive measures, incomplete or inconsistent snapshot data can lead to incorrect results. Take care during ETL to generate the complete date series, create product/store combination matrices, fill gaps with appropriate values, and validate the completeness of the snapshots. |