Re: [D] Proposal: metric additivity (additive / semi-additive / non-additive) in the core spec [ossie]

["anykolaiszyn (via GitHub)" <gi...@apache.org>]

GitHub user anykolaiszyn added a comment to the discussion: Proposal: metric additivity (additive / semi-additive / non-additive) in the core spec

The wrong number is always in the total row.

Twenty years across Excel, Tableau and Power BI, and that's the one constant. The rows are right. The subtotals are right. Then the last line sums twelve month-end balances, or averages three margins instead of recomputing one, and it goes to the board because the bottom line always looks like the truth. That bug has survived every platform migration since the pivot table. It is about to make the jump to agents, where there's no analyst left to squint at the total and say "that can't be right."

That's why this belongs in the core spec and not in `ai_context`. Additivity isn't a hint. It's the difference between a number and a wrong number, and the spec currently can't tell the two apart.

On the open questions:

**1 & 2. Explicit, and the default is *unknown*.** @harrydevforlife's stricter-only rule is right: a declaration may tighten what the analyzer infers, never loosen it. Pair it with "unknown" when omitted, not "additive." A consumer with no parser for the dialect and no declaration should not be told by the spec that summing is safe. Silence should mean *I don't know*, and a careful consumer should treat *I don't know* as *don't roll up across time*. Defaulting to additive is the total-row bug, written into the standard.

**3. Yes, and it's the one that matters most.** Both of you treat ratios as obviously non-additive and move on. Fair, but "non-additive" on its own is a warning with no exit. Gross margin is the canonical case: product, services and consolidated margin are all `(revenue − cost) / revenue`, none can be summed or averaged, and the only correct roll-up is to recompute from the component totals. A metric that says *don't sum me* without saying *what to re-aggregate instead* leaves the consumer stranded.

```yaml
- name: gross_margin_pct
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: (SUM(lines.revenue) - SUM(lines.cost)) / SUM(lines.revenue)
  additivity: non_additive
  reaggregate_from:
    - metric: revenue
    - metric: cost
```

That turns a flag into an instruction. It's also how every semantic layer I've used handles it underneath: the ratio is never stored, the components are, and the ratio is computed at whatever grain the query lands on.

**4. `is_time` is the natural anchor. Don't make it the only one.** The common case is "sum across everything, take *last* over time," and `non_additive_dimensions` pointing at an `is_time: true` field covers it. But derive semi-additivity from `is_time` and you've decided that time is the only snapshot dimension. Inventory by warehouse says otherwise. Last month's count at warehouse A plus this month's at warehouse B is not a number anyone wants. Time is the usual culprit. It isn't the only one.

One thing to keep in view as the shape settles. Additivity answers *can I roll this metric up across this dimension?* The moment a model carries two variants of the same measure, a sibling question arrives: *can I combine this metric with that one?* Product margin and services margin are each perfectly behaved inside their own scope and must still never be averaged together, and nothing in either expression says so. That's not this proposal's job. But it's the next total-row bug, and it would be a shame if the design here closed the door on it.

Happy to contribute test cases. A short, nasty set would pin the stricter-only rule down better than prose: snapshot balance, ratio, `COUNT(DISTINCT)` over a unique key, `COUNT(DISTINCT)` over a non-unique key, opaque MAQL.


GitHub link: https://github.com/apache/ossie/discussions/465#discussioncomment-18824000

----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org