Repository navigation
Spec has no way to declare a non-additive metric #290
Description
The gap
Ossie can't say that a metric is non-additive along a dimension. Metrics are static SQL strings, and the spec doesn't define the join semantics they run under.
So two conforming consumers can get different numbers from the same model.
Example
datasets:
- name: orders # many
primary_key: [id]
- name: customers # one
primary_key: [id]
relationships:
- name: orders_to_customers
from: orders
to: customers
from_columns: [customer_id]
to_columns: [id]
metrics:
- name: total_lifetime_value
expression:
dialects:
- dialect: ANSI_SQL
expression: SUM(customers.lifetime_value)Group total_lifetime_value by orders.status. The join repeats each customer row once per order, so the SUM over-counts by the customer's order count.
The model isn't wrong. The spec just doesn't say which answer is correct.
Two converters already hit this
dbt. MetricFlow has non_additive_dimension. Ossie has no field for it, so converters/dbt/src/ossie_dbt/osi_to_msi.py sets non_additive_dimension=None on export, and import records a CUMULATIVE_SEMANTICS_LOSS issue.
Cube. Cube fixes this at query time. When a cube is on the multiplied side of a join, it selects the distinct primary keys, joins them back to the measure's own cube, and aggregates there. Each row counts once. It refuses the query when the measures themselves span cubes that fan out. That's a runtime rewrite driven by declared primary keys, so a static expression can't inherit it.
That makes Cube → Ossie → Databricks unsafe for these metrics. A Databricks metric view has join cardinality and rely.at_most_one_match, but at_most_one_match asserts there is no fan-out. It isn't a dedup instruction. A SUM over a one_to_many join is computed on the flattened rows.
Options
Ossie already has enough to detect this: primary_key plus directional from(many)/to(one) relationships. What's missing is a way to declare it.
- Spec language only. Require consumers to deduplicate on the aggregated dataset's
primary_keywhen a relationship fans it out. No schema change, but it changes what existing models mean. - A metric-level field.
non_additive_dimension(following dbt), or anadditivity/grainmarker. - Leave it to
custom_extensionsand accept these metrics don't round-trip portably.
Is this worth pursuing, and which direction? I can write up a concrete proposal for whichever the list prefers.
Context
Came up building the Ossie ↔ Cube converter (#289), where it's the biggest fidelity issue. Possibly adjacent to #279, though that's about dialect drift rather than join semantics.
Activity
djwaldo commentedon Sep 1, 2026
ThoughtSpot encodes non-additivity in the function, not in a declaration.
There is no property that says "do not sum this across time". Instead you choose a different aggregate:
last_value/first_value(and*_in_periodvariants), which take an explicit partition and an explicit time axis. So the additivity fact is real, it is authored deliberately, and it lives inside the expression string, precisely where, as you say, the spec cannot reason about it.Balance : last_value(sum(balance),query_groups(),{balanceDate})
That gives three vendors and three different failure modes:
CUMULATIVE_SEMANTICS_LOSSissueThoughtSpot's engine and charts ensures that reaggregation does not occur.
I would argue for Option 2 does. This needs to be rich enough to drive a rewrite, not just a check. Coming the other way: Ossie → ThoughtSpot, a non-additivity declaration has no field to land in, so the converter has to change the expression itself,
SUM(...)intolast_value(..., <partition>, <time axis>). That needs to know which dimension is non-additive and which reduction applies along it (last? first? average?), not merely that non-additivity exists.Happy to review a concrete proposal, and to supply ThoughtSpot-side worked examples for whichever direction the list picks.
ekut commentedon Oct 5, 2026
A case from the repository's own example, in case it helps choose a direction.
examples/tpcds_semantic_model.yamldefines (dialects wrapper left out):It has the shape of
total_lifetime_valueabove:storeis the "one" side ofstore_sales_to_store, so a join fromstore_salesrepeats each store'ss_number_employeesonce per sale. TPC-DS at scale factor 1, same rows on DuckDB 1.5 and ClickHouse 26.9:s_store_skstore_saless_number_employeesSUMover the joinThe denominator is off by exactly the store's row count in
store_sales. A conforming consumer could return either column today, and the wrong one still looks like a plausible "sales per employee".One more row for djwaldo's table above:
Refusing was the only fix I could make without picking an answer the spec doesn't give.
Where things stand since this issue was opened, as I read them: #343 says an expression referencing another dataset "is model-scoped and belongs in semantic_model.metrics", and #354 §10.3 treats a measure with no home relation as "a family of results rather than a single metric", outside Layer 2. So
store_productivitystays in the gap this issue describes. Option 1 is the one an executor can implement and test without anything new in the model: the primary key ofstoreis already declared. On this example it gives the right column.