[IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References

[jacobwangxt]

Our team is adopting Ossie as the semantic modeling layer for our enterprise data platform. After building dozens of semantic models for TPC-DS and our internal data warehouse, we've found Ossie's dataset/metric/relationship model to be an excellent foundation. However, as we scaled to production, we hit three major pain points:

Problem 1: No Filtering Mechanism for Metrics

In practice, almost every metric needs filtering — "valid sales only", "domestic customers", "last 12 months". Currently, Ossie Metrics only support expression, forcing filter logic to be hardcoded into SQL:

# Current state: WHERE conditions duplicated across every metric
- name: valid_sales
  expression: "SUM(CASE WHEN status='completed' AND amount>0 THEN amount ELSE 0 END)"
- name: valid_profit
  expression: "SUM(CASE WHEN status='completed' AND amount>0 THEN profit ELSE 0 END)"

As the number of metrics grows, the same filter logic gets copied N times. Any definition change (e.g., adding payment_verified = true) requires locating and updating every single occurrence.

Problem 2: Duplicate Dimension Definitions — Spec Lacks Dimension-Level Business Semantics

The same business concept — order_status, sales_region, customer_segment — appears across fields in multiple datasets. Currently, Ossie's dimension object only supports an is_time flag, offering no way to capture richer business semantics.. datatype, description, and ai_context can only be defined as field-level attributes, and enumerated values (values) cannot be expressed at the dimension level at all:

# transactions dataset
- name: status
  datatype: String
  description: "Transaction status"
  ai_context: "PAID=paid, SHIPPED=shipped, CANCELLED=cancelled"
  dimension:
    is_time: false          # ← dimension only has this flag, no business semantics

# orders dataset (maintained by another team)
- name: order_status
  datatype: String
  description: "Order status"
  ai_context: "PAID=paid, SHIPPED=shipped"          # ← CANCELLED missing
  dimension:
    is_time: false

When the same dimension surfaces across multiple datasets, these scattered definitions are highly prone to drift, causing BI tools to display contradictory metadata.

Problem 3: Derived Metrics Require Full Expression Duplication

For derived metrics like profit_margin = total_profit / total_sales, the only option today is to copy and paste the full SQL expressions of the base metrics. There's no way to reference them declaratively. Any change to a base metric requires manually syncing all derived metrics.

---

What We Propose

Following the "define once, reference everywhere" DRY principle, we've designed 5 backward-compatible Schema changes:

#	Change	What It Solves
①	filters — Model-level Shared Filter	Define filter logic once; reuse across metrics via ${filter_name}
②	metrics.filters — Metric filter field	Shared Filter references + Inline Filters, with cross-dataset join_path
③	dimensions — Model-level Shared Dimension	Centralized dimension definitions (enum values, AI context) as single source of truth
④	fields.dimension.ref — Field-to-dimension binding	Declarative field binding to shared dimensions for consistent semantics
⑤	${metric} — Expression-level metric reference	Derived metrics compose existing metrics; filters auto-inherit

All changes are optional — models without them behave exactly as before.

---

Quick Demo (TPC-DS based)

# ① Define once: Shared Filters
filters:
  - name: valid_sales
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: "store_sales.ss_ext_sales_price IS NOT NULL AND store_sales.ss_ext_sales_price > 0"
  - name: domestic_customer
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: "customer.c_birth_country = 'United States'"

# ③ Define once: Shared Dimensions
dimensions:
  - name: country
    datatype: String
    values:
      - { value: "United States", name: "US" }
      - { value: "China", name: "China" }

# ④ Field binds to dimension (no need to repeat values / ai_context)
fields:
  - name: c_birth_country
    datatype: String
    dimension:
      ref: country   # ← binds to shared dimension

# ① + ② Metric references Shared Filter + cross-dataset join_path
metrics:
  - name: total_sales
    filters:
      - expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ${valid_sales}    # ← references shared filter

  - name: domestic_sales
    filters:
      - expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ${domestic_customer}
        join_path:                           # ← explicit cross-dataset relationship
          - store_sales_to_customer

  # ⑤ Derived metric, inherits base metric filters
  - name: profit_margin
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: "${total_profit} / NULLIF(${total_sales}, 0)"

---

Alignment with Industry Practice

These patterns have well-established equivalents in major semantic layers (dbt MetricFlow, Cube.js, LookML). Our proposal builds on industry best practices while strengthening support for multi-dataset enterprise scenarios:

Feature	dbt MetricFlow	Cube.js	LookML	Ossie (Proposed)
Metric Filters	✅ filter	✅ filters	✅ filters	✅ metrics.filters
Metric Reference	✅ type: derived	✅ measures composition	✅ ${measure} composition	✅ ${metric} syntax
Named Filter / Segment	❌ (Jinja workaround)	✅ segments	⚠️ Explore-level always_filter	✅ Top-level filters
Shared Dimension & Enum	❌ (scattered across Entities)	⚠️ (Cube inheritance)	⚠️ (View-bound metadata)	✅ dimensions + ref

Key Differences and Advantages Analysis:

1. Top-level filters (eliminates logic duplication)
   dbt MetricFlow's filters are inline per-metric, forcing the same rule (e.g., valid_sales) to be copied across dozens of metrics. Inspired by Cube.js segments, our proposal elevates Filters to first-class SemanticModel citizens, enabling single-point maintenance. Unlike Cube.js segments, Shared Filters embed directly into Metric expressions via ${filter_name} syntax, making filter conditions an intrinsic part of the metric definition rather than an optional query-time overlay — ensuring no consumer can accidentally omit critical business rules.

2. dimensions for unified business context governance (eliminates metadata fragmentation)
   LookML provides View-level dimension reuse via extends, but dimensions remain coupled to physical views. Proposed Shared Dimensions decouple business concepts from physical storage (Fields), using declarative ref binding to establish a single source of truth for dimension semantics across multiple datasets — resolving the metadata inconsistency problem that surfaces when BI/LLM consumers access the same dimension through different dataset paths.

---

Full Technical Documentation

Detailed schema definitions, parsing rules, a complete e-commerce example, and a minimal TPC-DS demo:

📄 Full proposal specification: https://docs.google.com/document/d/e/2PACX-1vTxGFGlcXK5BHHX5g9SVdGsmFb-IENndonvn5Qhw1_NUEwed8wNyFX3aCr68wHPnp9uHJ6GvoH__fC9/pub

---

Feedback Appreciated

1. Are filters and dimensions appropriate as SemanticModel top-level node names?
2. Is attaching join_path to individual filter items the right design for cross-dataset filtering?
3. Has anyone else encountered similar pain points during enterprise adoption that should be covered in this proposal?

Looking forward to your thoughts! 🙏

[yang85470-afk]

1. Metric filter scope

   Could you clarify how filter scope is preserved when a derived metric references base metrics with different filters? For example, in completed_sales / total_sales, applying the inherited
   completed_sales filter to the entire expression would incorrectly filter the denominator as well. Would each referenced metric be evaluated in its own aggregation scope, such as through
   conditional aggregation or separate subqueries?

2. JOIN cardinality and aggregate correctness

   How does the proposal ensure aggregate correctness when a cross-dataset filter introduces a one-to-many or many-to-many join? Such joins may duplicate fact rows and inflate measures such
   as SUM or COUNT. Would the compiler use relationship cardinality to choose between a regular join, a semi-join or EXISTS, pre-aggregation, or another deduplication strategy?

3. Determining a Filter’s Dataset

   Could you elaborate on how the parser determines a Shared Filter’s “home dataset” from its SQL expression? How would this work reliably with unqualified column names, aliases, dialect-
   specific syntax, or expressions referencing more than one dataset? Would it be more robust for a Shared Filter to declare its dataset explicitly, or to reference structured fields rather
   than relying on SQL-string analysis?

[jacobwangxt]

Thank you for these thoughtful questions — they get right at some of the trickiest parts of the design.
Let me walk through our current thinking below.

Theoretical Foundation

The theoretical foundation of the entire proposal is as follows:

Prerequisites:

1. The tables involved in a defined metric must be connectable via relationships (snowflake schema, star schema).

2. The relationships between these tables must be "safe" — no M:N joins, and no M:1:N join chains.

Through these relationships, the tables can be treated as a single virtual wide table containing all fields from all of them.

Our metrics are then defined on top of this virtual wide table — each metric can be understood as a filter + aggregation applied to this virtual table. This allows us to abstract away the source table of any given metric or filter.

Q & A

Q: Could you clarify how filter scope is preserved when a derived metric references base metrics with different filters?

A: The referenced metric's filter is not applied to the entire dataset/expression. Each referenced metric is evaluated within its own aggregation scope. Depending on complexity, this is implemented either via conditional aggregation (e.g., CASE WHEN inside the aggregate function) or via separate subqueries.

Q: Could you elaborate on how the parser determines a Shared Filter's "home dataset" from its SQL expression?

A: The dataset is derived by parsing the filter expression through the corresponding SQL/dialect parser to resolve which dataset(s) the referenced fields belong to.

Q: How would this work reliably with unqualified column names, aliases, dialect-specific syntax, or expressions referencing more than one dataset?

• Unqualified column names → Not permitted. Fields must explicitly specify the dataset they belong to; this cannot be omitted.
• Aliases → Aliases are not involved in this context.
• Dialect-specific syntax → Handled by using the appropriate dialect-specific parser.
• Expressions referencing multiple datasets → We recommend avoiding references to multiple datasets within a single Filter; such cases should be split into multiple sub-Filters (this can be enforced strictly at the parser level if needed). If not strictly prohibited, such cases fall back to the handling described in the Theoretical Foundation section above (treating the joined tables as one virtual wide table).

Q: How does the proposal ensure aggregate correctness when a cross-dataset filter introduces a one-to-many or many-to-many join?

A: JOINs are constrained by design — only N:1 and 1:1 relationships are permitted in the current scope. M:N relationships are disallowed, so row duplication/measure inflation from fan-out joins does not occur.

Q: Would it be more robust for a Shared Filter to declare its dataset explicitly, or to reference structured fields rather than relying on SQL-string analysis?

A: We prefer relying on SQL-string parsing. The advantage is stronger expressiveness — it isn't constrained by a fixed structured-field schema. The trade-off is that we need to maintain parsers for each SQL dialect we support.

Unsupported Scenarios

For complex scenarios such as M:N relationships and EXISTS/NOT EXISTS-style filtering, there is currently no complete solution — this remains an open area requiring further exploration.

---

Please let me know if any of this needs further clarification, or if you'd like to discuss alternative approaches — happy to dig deeper into any of these points.

[zt19960811]

Building on fields.dimension.ref, would it also be worth allowing a Shared Filter to reference a Shared Dimension and its declared values, rather than only dataset-specific physical columns?

For example:

    dimensions:
      - name: country
        datatype: String
        values:
          - { value: "United States", name: "US" }
          - { value: "China", name: "China" }

    datasets:
      - name: customer
        source: sales.public.customer
        fields:
          - name: c_birth_country
            expression:
              dialects:
                - dialect: ANSI_SQL
                  expression: "c_birth_country"
            dimension:
              ref: country

    # Proposed: reference the logical dimension instead of a physical column
    filters:
      - name: domestic_customer
        dimension: country
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "${country} = 'United States'"

The compiler could resolve ${country} to the concrete field in each dataset whose dimension.ref matches, validate the referenced value and datatype, and generate the dataset-specific predicate.

This would keep the Shared Dimension's value vocabulary as the single source of definition while avoiding equivalent raw SQL filters being re-encoded for each dataset.

How should this behave if multiple fields in the same dataset reference the same Shared Dimension, or if different datasets use different physical encodings for the same logical value?

[jacobwangxt]

Binding filter conditions directly to logical dimensions (Shared Dimensions) rather than physical columns indeed brings the semantic model closer to the underlying business logic.

While this pattern isn't currently supported in our core specification, I've actually encountered similar requirements in real-world scenarios. Once users establish global shared dimensions, it's natural for them to want to apply filter rules directly against these dimensions rather than worrying about the underlying physical fields. As for whether we should extend the Spec at this stage to support such a high level of decoupling, we'll likely need further community discussion to weigh the necessity against the added cognitive load.

Setting those considerations aside for a moment and following your train of thought: if we were to implement this design, we would have to address the edge cases you mentioned. This essentially boils down to two core sub-issues:

1. Syntax Level: How do we explicitly distinguish between physical fields and logical dimensions in Filter expressions?

If we simply use ${country}, it could lead to namespace collisions and parsing ambiguities if a physical field shares the same name.

To solve this, we could look at how dbt MetricFlow handles it—by using explicit function or macro syntax to declare the type of the referenced object. For example:

metrics:
  - name: completed_orders_count
    # ...
    # Explicitly reference the logical dimension in the condition/expression, not the underlying field
    filter: |
      {{ Dimension('country') }} = 'United States' 

This way, the compiler would explicitly know to look for the physical field where dimension.ref is country.

2. Semantic Mapping Level: How do we resolve the "one-to-many" mapping issue between dimensions and fields (Role-Playing)?

As you pointed out, the same dimension might correspond to multiple physical fields within the same table. For instance, a country dimension in an orders dataset might map to both shipping_country and billing_country. If a Filter simply references country, the compiler won't be able to infer which one to use.

In dimensional modeling, this is a classic "Role-Playing Dimensions" scenario. To support this in the spec, we might need to extend the field binding with a role attribute to uniquely identify the context. For example, specifying that the Filter applies to country (role: billing):

# 1. Declare a role when binding a dimension to a field
fields:
  - name: c_shipping_country
    dimension:
      ref: country
      role: shipping
  - name: c_billing_country
    dimension:
      ref: country
      role: billing

# 2. In the Filter, optionally include the role context when referencing the dimension
filters:
  - name: domestic_billing_customer
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: "${ Dimension('country', role='billing') } = 'United States'"

This design is elegant, but it would also increase the complexity of the YAML spec (e.g., having to handle fallback logic for default roles, etc.).

This direction is worth exploring. Given the added complexity mentioned above, this will require further discussion within the community.

[jklahr]

Thanks for the detailed write-up, and for the follow-ups to @yang85470-afk and
@zt19960811. The three pain points match what I've hit as well.

I have comments on all five changes, but they're long enough that I'll split them across a
few replies rather than post one wall of text. Starting with ⑤, since it's the change with
the most precedent and it overlaps directly with an open PR.

---

⑤ Metric references — overlaps with PR #343, and I think there's a clean merge

PR #343 (dataset-scoped metrics) raises
metric-to-metric references as a related open question. If both land, four things need joint
resolution. Concrete proposals for each:

(a) Qualify the reference inside ${...}

I'd adopt your ${...} marker. It makes the reference visible at the point of use and
resolves the ambiguity with column names, which a bare name does not. One amendment: put a
qualified name inside it when the target is dataset-scoped.

metrics:
  - name: profit_margin
    expression:
      dialects:
        - dialect: ANSI_SQL
          # store_sales.total_profit and store_sales.total_sales are
          # dataset-scoped metrics (PR #343)
          expression: "${store_sales.total_profit} / NULLIF(${store_sales.total_sales}, 0)"

This matches the convention Ossie metric expressions already use — the spec's own examples
are SUM(orders.sales), not SUM(sales) — so ${dataset.metric} reads the same way as the
field references beside it. It also makes the reference resolvable without a SQL parser,
which matters for a dialect reason I'll come to in the filters reply.

Resolution rule I'd propose: a name inside ${...} containing a . resolves as
<dataset>.<metric>; a bare name resolves as a model-scoped metric. Ambiguity is then a
validation error rather than an implementation choice.

(b) Reference-direction rules

#343 makes metrics declarable on a dataset (expression must resolve within that dataset) or
on the model (may span datasets). That creates a direction question your proposal doesn't
have to answer yet:

Referencing metric	May reference
Model-scoped	Any metric, model- or dataset-scoped
Dataset-scoped	Only metrics in the same dataset

A dataset-scoped metric referencing a model-scoped one would break the self-containment
property that makes dataset scoping useful, so I'd disallow it. Either PR can land first;
whichever is second should state this.

(c) Acyclicity

Not mentioned in the write-up. Suggested normative text:

The metric reference graph MUST be acyclic. A metric MUST NOT reference itself, directly
or transitively. Validators MUST reject cyclic reference graphs.

(d) Interaction with hidden (PR #287)

PR #287 adds a hidden flag. Combined with ⑤
that gives the intermediate-calculation pattern, which several implementations support:

metrics:
  - name: total_cost_basis
    hidden: true          # exists only to be composed, not exposed to consumers
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: "SUM(store_sales.ss_wholesale_cost)"

  - name: gross_margin_pct
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: "(${total_sales} - ${total_cost_basis}) / NULLIF(${total_sales}, 0)"

Worth confirming explicitly that referencing a hidden metric is legal — otherwise
implementations will differ on it.

(e) On aggregation scope

Your answer to @yang85470-afk — each referenced metric evaluated in its own aggregation scope
via conditional aggregation or subqueries — is more developed than #343's sketch, and I think
it's correct. It's also the part most likely to diverge between implementations, so I'd put
it in the spec as a requirement rather than leaving it to the compiler:

A referenced metric MUST be evaluated within its own aggregation scope, including any
filters attached to it. A derived metric's filters MUST NOT be applied to the expressions
of the metrics it references.

I think this one we should do, then I can update my metrics proposal as well.

⑤ is the best-precedented change in the proposal — it exists in dbt MetricFlow, in Cube, in
LookML, and in Snowflake's semantic view YAML (where model-level derived metrics reference
table-scoped metrics by qualified name). The concept matches everywhere; only the syntax
differs. It also has an Ossie PR touching adjacent ground already. If the proposal needs to
be split for reviewability, this is the piece I'd separate out and merge first.

[jklahr]

Continuing on ① and ② — the filter changes.

Problem 1 is real and I've hit it too.
Three things below: a recommendation that I think removes most of the need for
a new node, one change to the scoping if a filters node does get added, and one piece I'd pull
out of this proposal entirely because it could have broader impact.

Recommendation: use the existing label: filter as the primary mechanism

Ossie already has a label field on fields whose documented example value is literally
"filter". Combined with hidden from #287, the
define-once/reference-many case works with no schema change at all:

datasets:
  - name: store_sales
    source: tpcds.public.store_sales
    fields:
      - name: is_valid_sale
        datatype: Boolean
        label: filter
        hidden: true
        description: "Rows with a positive, non-null extended sales price"
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "ss_ext_sales_price IS NOT NULL AND ss_ext_sales_price > 0"

Defined once, referenced by name from any number of metrics, and since label is already in
the spec it needs no new converter support and no migration for existing models.

I'd recommend making this the primary mechanism for named reusable filters, and scoping ① to
cover only what it genuinely can't express. As far as I can tell that's two things:

• Filters spanning datasets. A field is declared inside one dataset, so a predicate over two
  datasets has no natural home as a field.
• Parameterized filters — anything whose value is supplied at query time ("last N months")
  rather than fixed in the definition. Ossie has no mechanism for this today and I'd argue it's
  a separate proposal regardless.

Everything else in Problem 1 — the valid_sales and domestic_customer examples in your
write-up included — is expressible today. Both are single-dataset boolean predicates over fixed
values.

Two reasons I'd prefer this to a new node. It keeps one concept where the spec would otherwise
have two overlapping ones: a filter as a labelled field and a filter as a standalone object,
with nothing to stop authors from using either. And it means the reuse win is available now,
against the current spec, rather than after a spec revision plus converter updates across every
implementation.

The counter-argument I can see is discoverability — filters: as a top-level node is more
obvious to a model author than a convention on label. That's fair, and if it's the deciding
factor I'd rather fix it by documenting the pattern in the spec text than by adding a parallel
mechanism.

If a filters node is added anyway, scope it to datasets as well as the model

Your example rewritten with dataset-scoped filters:

datasets:
  - name: store_sales
    source: tpcds.public.store_sales
    filters:
      - name: valid_sales
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "store_sales.ss_ext_sales_price IS NOT NULL AND store_sales.ss_ext_sales_price > 0"

  - name: customer
    source: tpcds.public.customer
    filters:
      - name: domestic
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "customer.c_birth_country = 'United States'"

semantic_model:
  - name: sales_analytics
    # Model level reserved for filters that genuinely span datasets
    filters:
      - name: high_value_domestic
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "customer.c_birth_country = 'United States' AND store_sales.ss_net_paid > 1000"

    metrics:
      - name: domestic_sales
        filters:
          - ${customer.domestic}     # qualifier states the home dataset
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "SUM(store_sales.ss_ext_sales_price)"

Three reasons for this over model-level-only:

It removes the home-dataset inference problem. @yang85470-afk asked how the parser
determines a filter's home dataset; the answer given was dialect-specific SQL parsing.
${customer.domestic} states it outright. This isn't just tidier — Ossie declares seven
dialects (ANSI_SQL, SNOWFLAKE, MDX, TABLEAU, DATABRICKS, MAQL, BIGQUERY) and
validation/validate.py currently skips SQL analysis for three of them (MDX, TABLEAU,
MAQL) because sqlglot can't parse them. A parse-based resolution strategy can't work for
those three at all. Declaration-site scoping works for all seven.

Your own guidance already points here. You recommend that a filter avoid referencing
multiple datasets, and that multi-dataset cases be split into sub-filters. If the recommended
form is single-dataset, the dataset is the natural declaration site, and the model level is
the exception rather than the default.

It's symmetric with #343. If metrics get two scopes with an expression-containment rule,
filters behaving identically is one concept to learn instead of two. The same rule text
applies:

A dataset-scoped filter's expression MUST resolve entirely within its dataset and MUST NOT
traverse relationships or reference fields of another dataset.

A note on the precedent table

The comparison table cites Cube segments as precedent for model-level filters, but Cube
segments are declared inside a cube. dbt MetricFlow scopes filters to a single metric. LookML
has view-level filter fields plus Explore-level always_filter. Snowflake's semantic views
scope filters to a table. As far as I can tell no widely-used implementation puts filters at
the model level only.

That's not an argument against doing it — it is an argument that dataset scoping should be
available, since that's what the field has converged on.

---

On join_path — I think this deserves its own discussion

I've left this to the end because I don't think it belongs in the filters proposal at all, and
I'd rather see it split out than settled as a side effect of ②.

join_path isn't a filter feature. It's a general resolution feature: whenever a definition
touches more than one dataset, something has to decide which relationships are traversed. That
applies to ② filters, but equally to ⑤ metric references, to the cross-dataset metrics in
#343, and to any query a consumer forms against the
model. Ossie has no mechanism for it today.

The gap is real and the current examples hide it. The shipped TPC-DS example has four
relationships, and no two share the same from/to pair, so path resolution is unambiguous
there and the problem never surfaces. TPC-DS itself has the ambiguous case — catalog_sales
carries both cs_sold_date_sk and cs_ship_date_sk, both foreign keys to date_dim.d_date_sk:

relationships:
  - name: catalog_sales_to_sold_date
    from: catalog_sales
    to: date_dim
    from_columns: [cs_sold_date_sk]
    to_columns: [d_date_sk]

  - name: catalog_sales_to_ship_date
    from: catalog_sales
    to: date_dim
    from_columns: [cs_ship_date_sk]
    to_columns: [d_date_sk]

Given both, a filter of date_dim.d_year = 2001 has two valid readings — sold year or ship
year — and qualifying by dataset name doesn't disambiguate, because both paths terminate at the
same dataset. So this isn't solved by the scoping proposal above; it's an orthogonal gap.

The design space is wider than a list of relationship names. Two approaches are visible in
existing implementations, and they're not variations on each other:

• Name the path explicitly at the point of use. This is your join_path. It's precise, but
  it means every definition that crosses datasets carries path metadata, and consumers forming
  ad-hoc queries need the same mechanism.
• Alias the dataset so each path terminates somewhere distinct. LookML does this with
  from: on a join, and Cube and dbt MetricFlow both push multi-path cases toward separate
  cubes/entities rather than path expressions. In Ossie this needs no new syntax at all —
  name and source are independent, so you can declare sold_date and ship_date as two
  datasets over tpcds.public.date_dim, and ${sold_date.d_year} is then unambiguous.

The second is also the mechanism I'd suggest evaluating for the role-playing dimension case
@zt19960811 raised, which makes it a decision with consequences across ②, ③/④ and ⑤ rather
than a local one.

Why separate it. Whichever proposal lands first will otherwise define path resolution
unilaterally for the whole spec, and get it named after its own use case. If ② lands with
join_path attached to filter items, cross-dataset metrics in #343 either inherit a
filter-shaped mechanism or grow a second one. I'd rather see one discussion that settles it
once — including whether path selection is needed at all, or whether dataset aliasing is
sufficient — and have ②, ⑤ and #343 all reference the outcome.

If you'd rather keep it in scope here, then concretely: I'd propose naming the field
using_relationships rather than join_path, and putting it on metrics as well as filters.

semantic_model:
  - name: sales_analytics
    filters:
      - name: high_value_domestic
        using_relationships: [store_sales_to_customer]
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "customer.c_birth_country = 'United States' AND store_sales.ss_net_paid > 1000"

    metrics:
      - name: sales_to_domestic_customers
        using_relationships: [store_sales_to_customer]
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "SUM(store_sales.ss_ext_sales_price)"

Three reasons for that name over join_path:

• It names the node it references. The values are relationships[].name entries, and Ossie
  already names reference fields after their target — from and to hold dataset names,
  from_columns/to_columns hold column names. using_relationships says what it contains;
  join_path describes an implementation step.
• It's plural, which is accurate. The value is a set of relationships, not a single path. In
  a tree-shaped model the anchor dataset plus that set determines the traversal, so the name
  shouldn't imply an ordered sequence the author has to get right.
• It reads correctly on a metric. "This metric is computed using relationships X and Y" works;
  "this metric's join path is X and Y" reads like it belongs to a query, not a definition. Since
  the same field will end up on both metrics and filters, it should read naturally on both.

Worth noting as a secondary benefit rather than a reason: at least one existing implementation
already uses this name for the same concept, so converters would map it 1:1 rather than
approximately.

I'd still rather see the concept itself settled in its own discussion, and have ②, ⑤ and #343
reference the outcome — but if it's decided here, using_relationships is the name I'd argue for.

[jklahr]

On ③ and ④. The cross-model problem is real. I'm less sure about the mechanism, and I wanted to
test whether Problem 2 needs new syntax at all. I think the current spec may already cover it, but
I'd like you to check my working. My suggestion is to pull ③/④ out and take it up as a separate
composability discussion.

③/④ moves the duplication instead of removing it

If order_status shows up in twelve models, you now write the dimensions block twelve times.
One team lists CANCELLED, another forgets it. That's the same drift as Problem 2, just between
files instead of between datasets. It's harder to catch, too, since the definitions live in
different repos owned by different teams.

So this doesn't really give you a single source of truth for dimension semantics.

I tried Problem 2 with the current spec, does this hold up?

I wanted to see how far today's spec gets on your example before adding anything, so I tried
modeling it as a conformed dimension: put the definition in its own dataset, and have both fact
datasets reach it through a relationship.

datasets:
  # The definition lives here, once.
  - name: order_status
    source: sales.public.order_status_lookup
    primary_key: [status_code]
    fields:
      - name: status_code
        datatype: String
        description: "Lifecycle state of an order"
        ai_context: "PAID=paid, SHIPPED=shipped, CANCELLED=cancelled"
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "status_code"
        dimension:
          is_time: false

  # Both fact datasets now carry a plain foreign key and nothing else.
  - name: transactions
    source: sales.public.transactions
    fields:
      - name: status
        datatype: String
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "status"

  - name: orders
    source: sales.public.orders
    fields:
      - name: order_status
        datatype: String
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "order_status"

relationships:
  - name: transactions_to_order_status
    from: transactions
    to: order_status
    from_columns: [status]          # the degenerate dimension is the foreign key
    to_columns: [status_code]

  - name: orders_to_order_status
    from: orders
    to: order_status
    from_columns: [order_status]
    to_columns: [status_code]

As far as I can tell this holds up. description and ai_context appear once, and the two fact
fields carry no semantics that could drift, so there's no second place for CANCELLED to go
missing. Consumers group and filter on order_status.status_code through either relationship. No
spec change needed, and since source accepts a query as well as a table, you don't need a
physical lookup table for this to work.

I validated the datasets and relationships above against the current schema, but schema-valid
isn't the same as actually solving your problem, and you've built a lot more models against this
spec than I have. Does this work for your cases, or is there something it breaks on that I'm not
seeing?

There is a real cost here. It adds a dataset and two relationships, which for three enum values
does feel heavy. If that verbosity is the actual pain point, I think it's worth saying so
directly, because "this is clunky to model" and "this can't be modeled" point at pretty different
solutions.

One question on the example itself. The comment says the orders dataset is "maintained by another
team." If the two definitions are owned by different teams, are they in the same file in practice?
If not, this is really the cross-model case, which is the part I don't think ③/④ fixes either.

Proposal: defer ③/④ to a composability discussion

Landing a model-level dimensions node now makes the real fix harder. Any cross-model
composability design needs its own way to declare and reference a shared concept. If ③/④ ships
first, the spec carries two mechanisms for one job and has to keep supporting the weaker one.
Deprecating a released node costs a lot more than waiting a release.

So I'd pull ③/④ out and take ①/②/⑤ forward on their own. They stand up independently and don't
depend on it.

I'd frame the separate discussion around DRY specifically, meaning how a definition written once
gets reused across models. That's a developer workflow problem, and it needs its own answers: a
namespace across files, a resolution mechanism, versioning, and behavior when a target is missing.
None of that is really about dimensions. The same mechanism will get asked to handle metrics,
filters, and relationships. The join-path question I raised on ② belongs in the same discussion,
since it's also about how definitions reach across a boundary.

On role

This mostly goes away if ③/④ is deferred, since role is an attribute on a binding that wouldn't
exist yet. I'm flagging it because @zt19960811's thread is heading toward a Dimension() macro
plus role resolution, and that's a lot of machinery to add on top of a node I don't think should
ship yet.

If ref does land later, the baseline I'd measure role against is two datasets over the same
source, with the role carried by the dataset name. I wrote that up in my reply on ②, since it's
also the answer to the multi-path join question. LookML does it this way (from: on a join), and
Cube and dbt MetricFlow push multi-path cases the same direction.

A note on the ontology spec

I want to separate this out, because I think it's a different problem from the one you're
describing.

Ossie ships an ontology specification
next to the core spec, which can declare a concept once and map it into a semantic model. That's
useful for telling an agent or a consumer that transactions.status and orders.order_status are
the same business concept across analytic domains. It gives you semantic identity for whoever is
reading the model.

It isn't a DRY tool for developers today, though. ontology_mappings[].semantic_model is a $ref
to the full SemanticModel, which requires name and datasets, so an ontology map has to inline
an entire semantic model rather than reference one. You'd end up avoiding duplicated dimensions by
duplicating models instead.

So I'd keep the two apart. Concept identity for consumers and definition reuse for authors are
different problems, and I don't think the ontology answers the second one as it stands. Problem 2
is the second one, which is why I'd rather see it in its own composability discussion than solved
with a node inside the semantic model.

[jklahr]

Coming back to ③/④, because I re-read your Problem 2 example and I think I under-read it the first
time. My earlier reply treated it as two dimension definitions drifting apart. That's a symptom, not
the problem.

The actual problem is a shared degenerate dimension: status lives directly on the
transactions fact, order_status lives directly on the orders fact, and there is no status
dimension table anywhere. That matters, because it changes what a fix has to do, and it makes my
earlier example wrong in one specific way I want to correct.

Correction to my earlier example

I wrote source: sales.public.order_status_lookup as though a lookup table existed. For a degenerate
dimension it doesn't. The honest version synthesizes the dimension from the values present in the
facts, and it needs a UNION rather than a SELECT DISTINCT from one of them:

datasets:
  - name: order_status
    source: >-
      SELECT DISTINCT status AS status_code FROM sales.public.transactions
      UNION
      SELECT DISTINCT order_status AS status_code FROM sales.public.orders
    primary_key: [status_code]
    fields:
      - name: status_code
        datatype: String
        description: "Lifecycle state of an order"
        ai_context: "PAID=paid, SHIPPED=shipped, CANCELLED=cancelled"
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "status_code"
        dimension:
          is_time: false

Relationships from each fact stay as I had them. The UNION isn't cosmetic: if orders carries a
status value transactions never sees, a one-sided lookup silently drops those fact rows on an inner
join.

This is the standard answer across the ecosystem, so I don't think it's a hack. dbt uses a seed or
model, Power BI a common table, Tableau a shared dimension table, Snowflake semantic views a UNION
helper view, and a classic star schema just adds a physical dim_status. Nobody has made it
first-class, which says the gap is real rather than that Ossie is behind.

The part I missed: "shared dimension" is two different needs

This is what I'd now push on in ③/④.

Need 1, shared metadata. description, ai_context and enum values written once instead of
per-field. This is what fixes the "BI tools display contradictory metadata" symptom you described.

Need 2, a shared queryable dimension. One dimension any metric can be grouped by, whichever fact
it came from. Slice any metric by status without picking one fact table's version.

③/④ delivers Need 1 and not Need 2. If transactions.status and orders.order_status both carry
dimension.ref: order_status, you have two fields asserting they mean the same thing, but there's no
entity to group by and no join between them. You still can't put a transactions metric and an
orders metric side by side under one status column, because nothing connects them.

I think Need 2 is the harder and more valuable half, and a dimensions registry with ref bindings
doesn't reach it. That's the main thing I'd want to resolve before this lands in any form.

Why I no longer think the workaround is good enough

My earlier reply leaned on "this is solvable today, so defer it." The mechanism does work, but I
understated the costs:

• You invent a dataset that doesn't exist in the warehouse, and building it scans the facts.
• Every model that needs it repeats the same UNION.
• The SQL goes in source, the one field in Ossie with no dialect mechanism.
  Field.expression and Metric.expression are both dialect-aware arrays. Dataset.source is a
  plain string. So the standard pattern pushes hand-written, engine-specific SQL into the least
  portable field in the spec.

That last point is what changed my mind. The workaround works by putting the problem somewhere the
spec can't express portability, which cuts against the reason for having a portable spec.

Proposal: make conformance declarative rather than adding a dimensions registry

If this becomes first-class, I'd rather state the conformance directly and let an implementation
generate whatever union its engine needs:

datasets:
  - name: order_status
    # Instead of hand-written UNION SQL in `source`.
    conformed_from:
      - transactions.status
      - orders.order_status
    primary_key: [status_code]
    fields:
      - name: status_code
        datatype: String
        description: "Lifecycle state of an order"
        ai_context: "PAID=paid, SHIPPED=shipped, CANCELLED=cancelled"
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: "status_code"

Why this shape over ③/④:

• It covers both needs. Metadata is written once, and the result is a real dimension entity, so
  cross-fact grouping works.
• It's dialect-independent. No SQL in source, so it stays portable.
• It reuses machinery that already exists. Relationships from each fact still do the joining, so
  cardinality and fan-out rules are unchanged. No parallel resolution path.
• The conformed fields are named explicitly, so there's no role-playing ambiguity to resolve, which
  also makes the role attribute discussion mostly unnecessary.

Open questions I don't have good answers to: whether relationships should be inferred from
conformed_from or still declared explicitly, how to express a value mapping when two facts encode
the same concept differently ('CANCELLED' vs 'C'), and whether this deserves a distinct kind of
dataset rather than a variant of the existing one.

Where this leaves my earlier recommendation

I'd still not merge ③/④ as it stands, but the reason is better than the one I gave. It isn't that the
problem is unreal or already solved. It's that ③/④ covers the metadata half and leaves the queryable
half to a workaround, and whatever goes into the spec should cover both. Otherwise we ship a
dimensions registry, people keep hand-writing UNION views for the part that matters, and the spec
carries two half-mechanisms.

So: split the shared-dimension piece out and give it a focused discussion, framed as the degenerate
dimension problem rather than metadata duplication. ①/②/⑤ don't depend on it and can proceed. I'm
happy to open that and write up conformed_from properly.

Before that, I'd want your read, since you've built far more models against this spec than I have.
Does the UNION pattern match what you're doing today? And is Need 2 what you're actually after, or is
the metadata consistency the thing that's really hurting?