[converter/microsoft] Deterministic DAX to OSSIE_SQL_2026 translation (move the #576 portability number) #584

Motivation

#576 measures how much of a model survives the portable core, and the Microsoft converter is one that returns metrics in vendor DAX, not OSSIE_SQL_2026, a working measure the next tool cannot read.

Measured at current main (7496811), round-tripping a small capability ladder Ossie to Microsoft to Ossie: of 17 elements, 0 return portable. 11 single-column aggregates come back as DAX only, and 6 richer expressions (a scalar calculated column, a filtered CASE, a metric reference, date scalars, a window) degrade to BLANK() with the original kept only as an annotation. Every loss is diagnosed, which is to the converter's credit, but none is portable.

#444/#564 made measures on measure-only tables into metrics, and #571 clarified the warning, but those metrics are still DAX-only, so every non-PBI target drops them (the Databricks converter skips non-SQL with a notice).

This proposes a deterministic DAX to SQL engine inside converters/microsoft (not a new converter, per #453) that attaches a portable OSSIE_SQL_2026 expression next to the DAX, directly improving the Microsoft row in #576.

Target: the Expression Language spec

The engine emits into core-spec/expression_language.md (OSSIE_SQL_2026), and the spec's own boundaries define the engine's:

• Exact tiers map onto the spec's REQUIRED core: aggregates (SUM/COUNT/AVG/MIN/MAX, STDDEV/VARIANCE, MEDIAN/PERCENTILE_*), CASE WHEN, date (YEAR/MONTH/DAY, DATE_TRUNC, DATEADD/DATEDIFF), string (LEFT/RIGHT/SUBSTRING/CONCAT), operators.
• Refuse-by-default boundary equals the spec's "Not Supported in Expressions" list (SELECT/FROM/JOIN, GROUP BY, WHERE, subqueries, CTEs). Those stay DAX plus a warning.
• Filtered aggregation uses the spec's portable SUM(CASE WHEN ... THEN x ELSE 0 END); FILTER (WHERE ...) adopted once [core-spec]: add FILTER (WHERE ...) aggregate modifier #382 lands.

Design

• Deterministic, no LLM, refuse by default, mirroring the house rule in _sql_to_dax.py ("Ambiguity is treated as failure, never as a guess"). DAX is kept as a fallback whenever a construct cannot be translated exactly.
• Reuses sqlglot (already a dependency) for the SQL AST and renderer, with a re-parse self-check.
• No change to PBI to Ossie to PBI round trips, DAX stays preferred per fix(microsoft): read OSSIE_SQL_2026 expressions #509 (merged).
• Explicit rules for the known traps: BLANK is not NULL arithmetic, empty-set counts, DIVIDE zero-handling, iterator grain, DAX DATEDIFF(start,end,part) to spec DATEDIFF(part,start,end) plus boundary semantics, PERCENTILE.EXC is not PERCENTILE_DISC, CEILING significance.
• Precedent: the ThoughtSpot converter's data-driven expression catalog plus a "catalog covers the spec" test. Every DAX function is in the catalog with a rule or an explicit refusal plus tier; the mapping doc is generated from it.

Scope note

This is declaration (metric and field expressions), not querying. It is meant to complement, not preempt, the relational query interface (#354). Filter, CALCULATE, and grain cases that touch query semantics are deferred and coordinated rather than forced.

Proposed first PR (PR1)

The smallest slice that is reviewable on its own and moves a measured number:

• A new _dax_to_sql.py mirroring the existing _sql_to_dax.py, wired into the import path behind an opt-in --translate-dax flag (default off, so PBI round trips stay byte-identical and nothing existing changes).
• Handles exactly the single-column aggregate tier: SUM, COUNT, COUNT(DISTINCT), AVG, MIN, MAX over one column, resolving 'table'[column] to the Ossie dataset.field. Everything else is refused and keeps DAX, per the house rule.
• Golden tests per rule plus refusal tests.
• Measured effect on the ladder above: the 11 single-column-aggregate elements move from vendor-only to portable. The 6 that degrade to BLANK() are untouched, since they are lost on the SQL to DAX export leg and are out of scope for an import engine.

Later PRs

• T0 scalar (calculated columns) plus the full ~394-function catalog (most marked refuse).
• Remaining T1 aggregates and the BLANK rules; then T2/T3 (CALCULATE CASE form, measure-reference and VAR inlining).
• T4 time intelligence follows a window-measure spec vote.

Validation

Golden test per rule, BLANK-semantics tables, out-of-repo differential testing (PBI/Fabric versus translated SQL on Databricks, per group-by), and a percent exact / refused-by-tier metric on real anonymised models. Fidelity is reported against the #576 round-trip rubric, including the before/after for the Microsoft converter.

Questions for maintainers (@eisber / @m-kovalsky)

1. Take this in converters/microsoft, in Python, along these lines?
2. Opt-in --translate-dax (default off), or on by default (the OSSIE_SQL_2026 entry is additive and DAX stays preferred)?
3. Catalog as Python code (like ThoughtSpot) or a language-neutral catalog.yaml (so a future Java port for the Databricks converter reuses the corpus)?

Architecture diagram and full catalog available; I will start a dev@ thread once there is a direction here.