Top level "mertics" vs. dataset-level “measure”s

[cpimhoff]

Currently, the OSI only defines “metrics”, which encode top-level calculations across all the datasets in the model.

This is a significant departure from most semantic layers today. Almost all semantic layers define “measures” as a reusable calculation on datasets (Cube, MetricFlow, Malloy, Looker). Some then reserve top level “metrics” (name varies) as a mechanism to specify a particularly interesting aggregation (Cube, MetricFlow, Looker).

For example:

datasets:
	- name: sales
	  source: public.sales
	  fields: 
		- name: price
		- name: region
		- name: timestamp
	  measures:
		- name: revenue
		  expression: SUM(price)

metrics:
	- name: recent_revenue
	  measure: sales.revenue
	  filter: sales.timestamp > (NOW() - "1 week")
	- name: revenue_by_region
	  measure: sales.revenue
	  group: sales.region

I don’t currently understand the OSI’s rationale for taking such a different approach by encoding all metric and measure definitions at the top level, and I think it is a mistake to call them “metrics” when a formal metric resource would be useful within OSI later on.

[lloydtabb]

This makes things tricky when trying to compute symmetric aggregates.

[khush-bhatia]

@lloydtabb By symmetric aggregates, do you mean multi-fact metrics ?

Grain locking aggregate measures to datasets definitely has some pros. But then OSI will still need a spec to define metrics that are advanced calculations and cannot be scoped to a particular dataset.

[sfc-gh-wpugh]

@cpimhoff what is the value of having them separate? Is it mainly name-spacing? or is there something semantic?

For example, Is there anything different between the results from following 2 examples and your example?

Computing in place

datasets:
	- name: sales
	  source: public.sales
	  fields: 
		- name: price
		- name: region
		- name: timestamp
		- name: revenue
metrics:
	- name: recent_revenue
	  measure: SUM(sales.revenue)
	  filter: sales.timestamp > (NOW() - "1 week")
	- name: revenue_by_region
	  measure: SUM(sales.revenue)
	  group: sales.region

Adding a simple metric that is reused

datasets:
	- name: sales
	  source: public.sales
	  fields: 
		- name: price
		- name: region
		- name: timestamp
		- name: revenue

metrics:
        - name: sales_revenue
	  measure: SUM(sales.revenue)	  
	- name: recent_revenue
	  measure: sales_revenue
	  filter: sales.timestamp > (NOW() - "1 week")
	- name: revenue_by_region
	  measure: sales_revenue
	  group: sales.region

[sfc-gh-wpugh]

Yeah. I'm trying to understand a little more about the differences. Whether they are purely ergonomic or something deeper.

Going back to my examples above. Are they all semantically the same, and the question is one of ergonomics? Or is there some semantic built into the dataset level calcs that you would expect that would cause these to act differently in some way?

[cpimhoff]

I am not sure I can answer your question regarding the examples accurately. I think they may be malformed? I think the two text snippets in your comment are ... uhhh ... exactly the same text? So they are indeed equal, but I'm not sure that's your intent. I also don't see any place where sales.revenue is defined in either of them.

I believe that the locality question (which could have semantic differences, not just ergonomics) only comes into play with a more complex cross-dataset calculation. I think the rest of the differences may be "ergonomics" or style, but I'd argue those may still be important. As mentioned part of this is just ensuring integration is easier for existing vendors. I'd also note that stuff like "this UI needs to show the relevant measures for this dataset" is made much more obviously with a per-dataset approach. If agents work with these definitions, grouping measures alongside the dimensions that cut them is also helpful, and decomposing measures into smaller building blocks helps an agent (and people) reuse/iterate on related definitions.

[sfc-gh-wpugh]

Ah. O.k. I'll call out the differences more directly. Updated the def, the idea is that in 2&3 revenue is just the column. Aggregation is not defined at that level:

1. (Your example) revenue is defined in the dataset as SUM(revenue) . recent_revenue and revenue_by_region directly use that.
2. (Computing in place) Nothing is defined in the dataset, recent_revenue and revenue_by_region have SUM(sales.revenue) as part of their definitions
3. (Re-used global metric). sales_revenue is defined at the global metric level as SUM(sales.revenue). recent_revenue and revenue_by_region use that.

[cpimhoff]

Ah geez seems like our comms are deffs polluted by some stacked typos. I meant to declare measure: revenue as sum(price) and then maybe confusion spiraled from there.

[vigneshc]

Consider the following case

measure1 = complex expression1
measure2 = complex expression2
measure3 = complex expression3

ratio metric1 = measure1 / measure2
ratio metric2 = measure1 / measure3

Without measures, wouldn't we have to duplicate the definitions?

[ralfbecher]

I'm a bit lost here, but is the intention of OSI to support multi-fact metrics (which most BI vendors don't)?

[willpugh]

No. This is not a multi-fact discussion. It is more how to organize and re-use calculations.

Basically, can you define a new expression in the "table" namespace or is everything in the global namespace.

[khush-bhatia]

@cpimhoff and others, FYI, This is one of an important discussion points for the metric language working group. We will add a public document with the proposal when it is ready. But also do join the slack workspace to participate in any adhoc discussions.

[vigneshc]

@khush-bhatia , is the slack workspace open?

[khush-bhatia]

@vigneshc Slack join link

[mgorinevgit]

@khush-bhatia I'm at PhoenixAI (formerly Celerdata), where we've built some answers to this. Andy Ye here applied to OSI at some point, but the slack link rejects our domain. Who is the right contact to get CelerData/PhoenixAI added to this Slack instance?

[khush-bhatia]

@mgorinevgit Can you please try the link here https://github.com/open-semantic-interchange/OSI/blob/main/CONTRIBUTING.md

[lanvendar]

I agree with treating all of these as Metrics. A Measure looks more like one way to construct a Metric than a separate, peer-level business object.

If we keep a flat discriminator, I would call the calculation-plus-filter case SCOPED_AGGREGATE. “Scoped” communicates that the aggregation is evaluated over a constrained data scope. I would also use AGGREGATE rather than CALCULATE for the calculation-only case.

A direct classification could be:

Metric
├── AGGREGATE          aggregation only
├── FILTERED           an existing Metric plus a filter
├── SCOPED_AGGREGATE   aggregation plus a filter
└── DERIVED            a formula over other Metrics

I would prefer the discriminator name metric_kind rather than metric_type, because metric_type may later be useful for business types such as amount, ratio, quantity, or balance.

One important constraint is that a filter alone cannot produce a value, so a FILTERED Metric must reference a base_metric. Also, these should not be modeled as several unrelated optional fields such as calculate?, filter?, and formula?. That permits both an empty Metric and conflicting definitions. Instead, Metric.definition should be required and modeled as a discriminated union—conceptually a JSON Schema oneOf.

However, my preferred design is an expression tree rather than introducing a new Metric kind for every combination:

aggregation:              Aggregate(...)
filtered metric:          Filter(MetricRef(...), predicate)
filtered aggregation:     Filter(Aggregate(...), predicate)
derived metric:           Formula(MetricRef(...), MetricRef(...))

In that model, “calculation plus filtering” is simply Filter(Aggregate(...), predicate), so SCOPED_AGGREGATE is no longer needed as an atomic type.

This structure also scales better. Future operations such as cumulative(...), period_over_period(...), rank(...), and time_shift(...) can be added as expression nodes, without creating a new MetricType for every combination of capabilities.

[kayemkim]

Wanted to add a concrete data point in favor of keeping the filter separate from the aggregate expression, since the thread has been circling exactly that.

We've been evaluating Ossie as the input to a semantic query engine (the engine compiles SQL from the model). A metric shape that comes up constantly for us is "revenue, but only completed orders": an aggregate plus a row-level predicate. With a single expression per metric, the only place to put that predicate today is inside the aggregate:

SUM(CASE WHEN status = 'COMPLETED' THEN amount ELSE 0 END)

That looks equivalent to filtering, but stops being equivalent the moment the metric is grouped by a dimension. Small repro, plain SQL:

CREATE TABLE sales(sale_id INT, sale_month TEXT, status TEXT, amount INT);
INSERT INTO sales VALUES
 (1,'2024-01','COMPLETED',100),
 (2,'2024-01','COMPLETED', 50),
 (3,'2024-02','CANCELLED',200),  -- Feb has no completed sales
 (4,'2024-03','COMPLETED', 70);

-- filter as WHERE, the intended result:
SELECT sale_month, SUM(amount) FROM sales
WHERE status='COMPLETED' GROUP BY sale_month;
-- 2024-01 | 150
-- 2024-03 |  70

-- filter folded into the aggregate, what a single expression forces:
SELECT sale_month, SUM(CASE WHEN status='COMPLETED' THEN amount ELSE 0 END)
FROM sales GROUP BY sale_month;
-- 2024-01 | 150
-- 2024-02 |   0   <-- phantom row
-- 2024-03 |  70

The CASE form invents a 2024-02 | 0 row for a month that had no completed sales at all. For trend or period-over-period views that's a real correctness difference, and it gets worse with HAVING, with COUNT(DISTINCT ...) (CASE can't drop rows before the distinct count), and with ratios of two differently-filtered metrics.

The root cause is that a row-level filter belongs in the query (WHERE/HAVING at compile time), not inside the aggregate function. Any consumer that compiles a metric's expression into a grouped query inherits this, because the spec gives it no filter to lift out.

Which is why I think the direction upthread is right: metrics composing measures + filters, or the Filter(Aggregate(...)) / SCOPED_AGGREGATE shape. In both, the predicate stays separate from the aggregate, so a compiler can place it as WHERE. A flat expression can't support that. On our side, modeling metrics as {aggregate, filter} and compiling the filter to WHERE fixed all the grouped cases. (We first hit this via LLM-generated SQL over flat expressions, but as the repro shows it needs no LLM; the divergence is inherent to folding the filter into the aggregate.)