Re: [I] Spec has no way to declare a non-additive metric [ossie]

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

ekut commented on issue #290:
URL: https://github.com/apache/ossie/issues/290#issuecomment-5992129711

   A case from the repository's own example, in case it helps choose a direction. `examples/tpcds_semantic_model.yaml` defines (dialects wrapper left out):
   
   ```yaml
   - name: store_productivity
     expression: SUM(store_sales.ss_ext_sales_price) / NULLIF(SUM(store.s_number_employees), 0)
     description: Sales per employee across stores
   ```
   
   It has the shape of `total_lifetime_value` above: `store` is the "one" side of `store_sales_to_store`, so a join from `store_sales` repeats each store's `s_number_employees` once per sale. TPC-DS at scale factor 1, same rows on DuckDB 1.5 and ClickHouse 26.9:
   
   | `s_store_sk` | rows in `store_sales` | `s_number_employees` | `SUM` over the join | metric over the join | metric with each store counted once |
   |---|---:|---:|---:|---:|---:|
   | 1 | 456,769 | 245 | 111,908,405 | 7.63 | 3,486,635.04 |
   | 4 | 458,443 | 218 | 99,940,574 | 8.57 | 3,929,415.39 |
   
   The 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:
   
   | | On a fan-out |
   |---|---|
   | ossie-clickhouse (an executor I maintain) | rejects the metric at validation since 0.2.0; summed over the join before |
   
   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_productivity` stays 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 of `store` is already declared. On this example it gives the right column.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@ossie.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org