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