You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ossie.apache.org by "djwaldo (via GitHub)" <gi...@apache.org> on 2026/09/25 01:36:50 UTC

[I] ThoughtSpot converter: metrics get no ANSI_SQL sibling, so every metric is dropped on any cross-vendor conversion [ossie]

djwaldo opened a new issue, #462:
URL: https://github.com/apache/ossie/issues/462

   The ThoughtSpot converter emits metric expressions in the `THOUGHTSPOT` dialect only. Every
   other converter requires `ANSI_SQL` (or its own vendor dialect) and skips the rest, so a
   ThoughtSpot-derived document loses **all** of its metrics on the way to any vendor.
   
   This is not a proposal to change a convention -- the converter already emits `ANSI_SQL`
   siblings for *fields*, 37 of them in its own fixtures. Metrics are simply not reaching the
   same path.
   
   ## Measured
   
   Across 31 real ThoughtSpot models (164 datasets, 643 fields):
   
   ```
   metrics emitted:                     476
   with ANY non-THOUGHTSPOT dialect:      0
   ```
   
   Observed end to end, converting a ThoughtSpot-derived document onward:
   
   ```
   # converters/snowflake
   Skipping metric '...': no Snowflake-compatible expression
   (has dialects: THOUGHTSPOT; requires SNOWFLAKE, ANSI_SQL, or OSSIE_SQL_2026)
   
   # converters/databricks
   UserWarning: [metric 'total_order_amount'] no DATABRICKS/ANSI_SQL/OSSIE_SQL_2026
   dialect; dropping metric
   ```
   
   The returned document has no `metrics:` key at all. Tables, columns and relationships
   survive; the business logic does not.
   
   ## Root cause
   
   Most of the machinery already works. `_compose_aggregate_entries` composes an `ANSI_SQL`
   sibling for an aggregate metric, and `tests/test_tml_to_ossie_metrics.py` exercises it.
   
   It never fires on a real model because the resolver's index excludes measures
   (`_index_attribute_columns`, `tml_to_ossie.py`):
   
   ```python
   if properties.get("column_type") != "ATTRIBUTE":
       continue
   ```
   
   A metric aggregates a MEASURE column. The reference therefore resolves to nothing, and the
   metric falls out through the `TS-EXPR-UNRESOLVED` path -- logged at INFO, so it is quiet.
   
   By shape, across those 476 metrics:
   
   | shape | count | |
   |---|---:|---|
   | `column_aggregation` | 348 | 73% -- a bare aggregate over one column |
   | (no recorded shape) | 107 | 22% |
   | `scalar_formula_plus_aggregation` | 21 | 4% |
   
   So roughly three quarters are the simplest possible case, and are blocked by the index
   rather than by anything about expression translation.
   
   ## Proposal
   
   Let a metric's reference resolve against MEASURE columns as well as ATTRIBUTE ones, and
   emit the `ANSI_SQL` sibling the aggregate path already builds.
   
   `ANSI_SQL` rather than `OSSIE_SQL_2026`, deliberately: `ANSI_SQL` is what this converter
   already writes for fields and what five sibling converters (orionbelt, gooddata,
   salesforce, omni, nvidia) write today, while nothing yet writes `OSSIE_SQL_2026`. Per #450
   the two parse identically -- `DIALECT_MAP.get()` returns the same sqlglot default for both
   -- so this stays forward-compatible: adopting `OSSIE_SQL_2026` later is a relabel, not a
   re-translation.
   
   **The care this needs is aggregation semantics, not syntax.** A ThoughtSpot MEASURE column
   carries its own default `aggregation`, and whether that property applies depends on the
   formula wrapping it -- it is live for a raw column and for a bare `group_aggregate(...)`,
   and a no-op under `sum(...)` or the `group_*` shorthands. Emitting `SUM(col)` for a metric
   over a column that is already summed would double-count, which imports cleanly and answers
   wrongly. `TS-METRIC-AGGREGATION-ALREADY-AGGREGATED` fires 46 times across the corpus, so
   this case is common rather than hypothetical, and the rule is already encoded in the
   converter -- it just has to be honoured on this path too.
   
   The remaining 26% (unrecorded shapes and scalar-formula metrics) need real expression
   translation. `expressions/reverse.py` is the inventory for that -- 84 ThoughtSpot-native
   functions, 53 of them classified as composable into a portable expression -- and is built,
   tested and published as `docs/reverse-inventory.md` but not called from the conversion
   path. That is a reasonable follow-up; the 73% does not have to wait for it.


-- 
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.apache.org

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