Structured aggregation_method for Metrics

[diannewood]

Problem

String expressions require parsing and have cross-platform incompatibilities:

• APPROX_COUNT_DISTINCT() vs approx_distinct() (not in ANSI SQL)
• Median/percentiles vary widely (not in ANSI SQL)
• COUNT(DISTINCT (a,b)) vs COUNT(DISTINCT CONCAT(a,'|',b))

Some platforms interpret definitions rather than execute SQL directly.

Per Tom Lento's comment on the original OSI proposal: "Primitive types are restrictive and using strings to allow flexibility causes a lot of problems down the line."

Proposal

Metrics have either of the following but not both

• aggregation_method enum for common aggregations: sum, min, max, average, count_distinct, percentile
  • aggregation_params for aggregation-specific settings
  • dataset and field
• expression with dialect. These can reference other metrics and dimensions

Examples

metrics:

  - name: total_sales
    dataset: store_sales
    field: ss_ext_sales_price
    aggregation_method: sum

  - name: p99_order_value
    dataset: orders
    field: amount
    aggregation_method: percentile
    aggregation_params:
      percentile: 0.99

  - name: average_sales_per_order
    expression:
      dialects:
        - dialect: MDX
          expression: [Measures].[total_sales] / [Measures].[orderquantity]

[siljamardla]

Distinguishing between metric types has many important applications.

Establishing lineage for derived metrics

Understanding the lineage of derived metrics is a must. Consider the example of a profitability metric that relies on the total cost and the total revenue metric, which in turn involve components from N different business lines etc. For clearly understanding what is involved and how in the profitability metric we can look into the upstream components (metrics) that feed into this metric.

Directing downstream behaviour

We need to distinguish different aggregation behaviours in metrics. And I imagine downstream tools making use of these distinctions.
For example, we have a BI tool connect to OSI and reading metric definitions.

• If the metric is additive, the BI tool will allow the user to calculate the metric value on a certain grain and then aggregate further from that grain (e.g. city to country grain).
• If the metric is a derived metric where we add up additive metrics, the BI tool would also allow aggregating the number further.
• If the metric is a ratio metric (derived metric with a division expression), the BI tool would not allow the user to aggregate the metric value any further after it has been calculated on a specific grain. Even better, the BI tool would pick up on the fact that the components of the derived expression are additive, would query the components and would reuse the expression to provide the correct ratio metric for the user. e.g. acceptance rate = no of orders accepted / no of total orders.
• If the metric is a distinct count, the BI tool would not allow the user to aggregate the number further. e.g. active users.

[sfc-gh-wpugh]

I'm not sure any of these approaches really disallow lineage, do they? It is only a question of how you need to parse them.

Even in @diannewood 's suggestion above, there is almost certainly some set of scalar operations that can occur before aggregation that lineage will need to be able to run through.

[lloydtabb]

A simplification of this might be to create a set of OSI_ aggregate functions that can get interpreted expanded by the various implementations. The advantage here is that you can write much more complex metrics (measures).

metrics:

  - name: total_sales
    expression:  OSI_SUM(${store_sales.ss_ext_sales_price})

  - name: p99_order_value
    expression: OSI_PERCENTILE(${orders.amount}, 0.99)

  - name: user_count
    expression:  OSI_APPROX_COUNT_DISTINCT(${events.user_id})

When we compile an OSI model, we should be able to generate the downstream recompose-ability that @siljamardla describes above. I'm not sure we need to add more meta-data to the model, it should be derivable.

BTW: I'm a believer that metrics belong in datasets, not as a top level thing). There are many ways to compute user_count. Having the metric in the dataset lets the query composer chose the right one. I'm also a fan of using dimension: and measure: instead of -name (or better yet, using YAML hashes instead of YAML arrays as OMNI does).

[khush-bhatia]

@lloydtabb Interesting idea for expression like OSI_SUM(${store_sales.ss_ext_sales_price}). Are you thinking that these expressions should still follow SQL grammar ? The one main challenge I am hearing from multiple BI tools is that it is very hard to support expressions like these without a parser. To support expressions like these, OSI will also need to build and maintain an open source parser. Maybe it is worth the cost given the increased expressivity.

Will follow up on the scope of metrics in that thread. That's a top of mind discussion point for me as well.

[sfc-gh-wpugh]

@khush-bhatia What I would expect would be running in the spirit of what @lloydtabb mentions. However, see how far we can get without adding something like OSI_* functions.

For example SUM is going to be similar enough across all DBs that we probably don't need a new function for it. However Percentile and approximation functions are going to be less so.

It would be great to have some core functions that you can use in OSI expressions that work regardless of the DB. Percentile and approximate percentiles should be in this group, however, currently have different names and signatures across some DBs.

Someone should be able to do PERCENTILE_CONT(orders.amount, 0.99) and expect it to work, regardless of which DB is the provider. Although, I think we should be able to do SQL rather than requiring some templating like the ${} above.

My personal preference would be to define a set of functions with SQL that are safe across all implementations, but don't disallow passthrough of unsafe operations.

[djwaldo]

Supporting data point from the ThoughtSpot mapping (#285): beyond structured aggregation, there's currently no way to declare a metric semi-additive (e.g. account balances, summable across accounts, not across time). Snowflake's semantic views express this with non_additive_dimensions, ThoughtSpot has an equivalent concept, and OSI's Decomposability idea is unreachable from a model document today. If this proposal moves, semi-additivity would be worth including in the same structure.