[IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References #342
Replies: 9 comments 7 replies
|
|
Thank you for these thoughtful questions — they get right at some of the trickiest parts of the design. Theoretical FoundationThe theoretical foundation of the entire proposal is as follows: Prerequisites:
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 & AQ: 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., 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?
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 ScenariosFor 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. |
|
Building on 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 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? |
|
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 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 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 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 # 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. |
|
Thanks for the detailed write-up, and for the follow-ups to @yang85470-afk and I have comments on all five changes, but they're long enough that I'll split them across a ⑤ Metric references — overlaps with PR #343, and I think there's a clean mergePR #343 (dataset-scoped metrics) raises (a) Qualify the reference inside
|
| 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.
|
Continuing on ① and ② — the filter changes. Problem 1 is real and I've hit it too. Recommendation: use the existing
|
|
On ③ and ④. The cross-model problem is real. I'm less sure about the mechanism, and I wanted to ③/④ moves the duplication instead of removing itIf 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 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. I validated the datasets and relationships above against the current schema, but schema-valid There is a real cost here. It adds a dataset and two relationships, which for three enum values One question on the example itself. The comment says the orders dataset is "maintained by another Proposal: defer ③/④ to a composability discussionLanding a model-level So I'd pull ③/④ out and take ①/②/⑤ forward on their own. They stand up independently and don't I'd frame the separate discussion around DRY specifically, meaning how a definition written once On
|
|
Coming back to ③/④, because I re-read your Problem 2 example and I think I under-read it the first The actual problem is a shared degenerate dimension: Correction to my earlier exampleI wrote 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: falseRelationships from each fact stay as I had them. The UNION isn't cosmetic: if This is the standard answer across the ecosystem, so I don't think it's a hack. dbt uses a seed or The part I missed: "shared dimension" is two different needsThis is what I'd now push on in ③/④. Need 1, shared metadata. Need 2, a shared queryable dimension. One dimension any metric can be grouped by, whichever fact ③/④ delivers Need 1 and not Need 2. If I think Need 2 is the harder and more valuable half, and a Why I no longer think the workaround is good enoughMy earlier reply leaned on "this is solvable today, so defer it." The mechanism does work, but I
That last point is what changed my mind. The workaround works by putting the problem somewhere the Proposal: make conformance declarative rather than adding a dimensions registryIf this becomes first-class, I'd rather state the conformance directly and let an implementation 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 ③/④:
Open questions I don't have good answers to: whether relationships should be inferred from Where this leaves my earlier recommendationI'd still not merge ③/④ as it stands, but the reason is better than the one I gave. It isn't that the So: split the shared-dimension piece out and give it a focused discussion, framed as the degenerate Before that, I'd want your read, since you've built far more models against this spec than I have. |
|
Problem 1 matches our experience, and I want to add a data point from a system that already ships model-scope filters, because the failure mode we actually measured is not the one this proposal is aimed at. ThoughtSpot has had model-level filters for years, and duplication was never the thing that hurt. They are declared once at model scope, and the documentation is unambiguous that they are applied before any query. Mandatory, not advisory. So the define-once problem ① describes is solved, and it has not been the source of our bugs. What did hurt was a consumer applying the filter optionally. When we convert a ThoughtSpot model carrying That is worth raising here because the proposal does not currently say whether a filter is mandatory, and I do not think a For optional filters we support the ability to define when the filter should be applied. I.e. when customer table is included in the query then apply filter 'new customers' |
|
Thanks for the additional context. I'd like to add a quick clarification: in this proposal, filters are an integral part of the metric definition rather than optional elements. The actual semantics of a metric with a filter is that it represents the aggregated result after the data has been filtered accordingly. In our implementation, we handle this by translating the filter into either a WHERE clause or a CASE WHEN condition within the aggregation. Hope this helps clarify the intended behavior. 😊 |
|
I believe model-level filters are necessary. In enterprise environments, many filters are shared by multiple metrics. If these filters can only be defined inline within each metric, this creates two problems:
Modeling these filters as Therefore, I am inclined to support both dataset-scoped and model-scoped filters: the former for predicates that belong entirely to a single dataset, and the latter for cross-dataset rules or business rules governed at the model or domain level. In addition, the discussion above raises a broader directional question for me: to what extent does Ossie ultimately intend to provide executable and deterministic semantics for the semantic layer? One direction would be to provide a sufficiently complete DSL from which consumers could deterministically compile a semantic model into an equivalent query plan or SQL statement. However, this would require the specification to cover many complex scenarios, including role-playing dimensions, multiple possible relationship paths, join cardinality, fan-out, and independent aggregation scopes for different metrics. This could make the specification extremely complex. Another direction would be to primarily provide a standardized semantic description that serves as knowledge and context for LLMs and other consumers. This approach would offer greater flexibility, but at the cost of execution determinism. Harness engineering could mitigate some of the risks through validation, retries, and result verification, but it may not eliminate the possibility that different consumers interpret the same metric or filter differently. I understand that these two directions are not necessarily mutually exclusive, and that Ossie may ultimately adopt a middle ground. If so, how should that boundary be defined? Which semantics that affect query correctness must be deterministic, compilable, and machine-validatable, and which may remain descriptive context intended for AI consumption? |
|
RE: querying, you should check out this document, which captures the language working group's latest thinking. The current model is that there will be multiple layers for querying an Ossie Model. Layer 3 (#246) semantics provide a top-level interface on top of multi-table models. The big benefit of this approach is it can abstract away all the join path / filter flow logic from a caller. To your point, to cover all the complex scenarios, the Ossie spec will need to get richer. Layer 2 (#354) exposes an Ossie model more like a DB schema. Each dataset is independently queryable in SQL, with measure-typed columns that are designed to avoid fanout on joins. Layer 3's query compilation could be implemented using Layer 2. Layer 2 requires a BI tool or agent to perform the actual joins / multi-table logic. The benefit is greater flexibility (you can easily join in a table from another model or perform your own custom multi-table logic on top). |
Proposal SplitThanks for the suggestion on splitting the proposal scope. Breaking down the five changes into separate tracks is definitely a better approach. Here is our proposed breakdown: 1. ⑤ Metric Reference (Next immediate step)
2. ①/② Filter (Continuing with narrowed scope)
3. ③/④ Shared Dimension (Separate discussion)
4. Relationship Resolution (Standalone foundational specification) Relationship Resolution is highly complex, so I’ll add the detailed content separately. Current Proposal1. ⑤ Metric ReferenceRegarding the aggregation and filtering scope of referenced metrics, we need to add the following clarifications: Proposed Content (Original)
Your Point (Original)
Our ResponseClarifications and Additions on the Aggregation Scope of Referenced Metrics
Conclusion: The final aggregation filter of a referenced metric = Illustrated ExampleThe following example demonstrates how the two types of filters work together when calculating the derived metric flowchart TD
subgraph DerivedMetric ["Derived Metric: apac_conversion_rate"]
direction TB
DF["Contextual Filter<br/>region = 'APAC'"]
subgraph Composition ["Composite Logic"]
M1_Ref["Ref: completed_orders"]
M2_Ref["Ref: total_orders"]
end
Formula["Division: M1 / M2"]
end
subgraph BaseMetric1 ["Base Metric: completed_orders"]
IF1["Intrinsic Filter<br/>status = 'completed'"]
AGG1["COUNT(order_id)"]
end
subgraph BaseMetric2 ["Base Metric: total_orders"]
IF2["Intrinsic Filter<br/>status != 'deleted'"]
AGG2["COUNT(order_id)"]
end
%% Contextual Filter Push-down
DF -.->|AND Push-down| Merge1(("AND"))
DF -.->|AND Push-down| Merge2(("AND"))
IF1 --> Merge1
IF2 --> Merge2
Merge1 -->|Final Aggregation Condition| AGG1
Merge2 -->|Final Aggregation Condition| AGG2
AGG1 --> M1_Ref
AGG2 --> M2_Ref
M1_Ref --> Formula
M2_Ref --> Formula
style DerivedMetric fill:#e8f4fd,stroke:#2196F3,stroke-width:2px
style BaseMetric1 fill:#fff3e0,stroke:#FF9800,stroke-width:2px
style BaseMetric2 fill:#fff3e0,stroke:#FF9800,stroke-width:2px
style DF fill:#c8e6c9,stroke:#4CAF50,stroke-width:2px
style IF1 fill:#ffe0b2,stroke:#FF9800,stroke-width:2px
style IF2 fill:#ffe0b2,stroke:#FF9800,stroke-width:2px
style Merge1 fill:#f5f5f5,stroke:#666,stroke-width:2px
style Merge2 fill:#f5f5f5,stroke:#666,stroke-width:2px
💡 Key Takeaways from the Diagram
When writing this rule into the spec, we will add an additional conflict resolution principle:
2. ①/② FilterRegarding the standalone Shared Filter node, we need to add the following context:
We agree with your core concern about "avoiding conceptual overlap," but retaining a standalone
By drawing these boundaries, the 3. ③/④ Shared Dimension1. Additional ContextSome context from actual systems we are building:
2. Single-Model vs. Cross-Model ReuseProposed Content (Original)
Your Point (Original)
Our Response
3. Does Shared Dimension provide a queryable consistent dimension?Proposed Content (Original)
Your Point (Original)
Our Response
Example Illustration: In the example below, both flowchart TB
D["Shared Dimension: order_status<br/>datatype: String<br/>values: PAID, SHIPPED, CANCELLED"]
subgraph T["Dataset: transactions"]
TF["Field: transactions.status"]
TM["Metric: transaction_amount<br/>SUM(transactions.amount)"]
TA["Grouped by transactions.status<br/>PAID = 150<br/>SHIPPED = 80"]
TF --> TM
TM --> TA
end
subgraph O["Dataset: orders"]
OF["Field: orders.order_status"]
OM["Metric: order_count<br/>COUNT(orders.order_id)"]
OA["Grouped by orders.order_status<br/>PAID = 2<br/>SHIPPED = 1<br/>CANCELLED = 1"]
OF --> OM
OM --> OA
end
TF -->|"dimension.ref: order_status"| D
OF -->|"dimension.ref: order_status"| D
Q["Query Request<br/>Group By: order_status<br/>Metrics: transaction_amount, order_count"]
Q --> TR["Resolve transaction_amount<br/>order_status → transactions.status"]
Q --> OR["Resolve order_count<br/>order_status → orders.order_status"]
TR --> TA
OR --> OA
D --> ALIGN["Align aggregated results by unified Shared Dimension values"]
TA --> ALIGN
OA --> ALIGN
ALIGN --> RESULT["Final Result<br/>PAID: 150, 2<br/>SHIPPED: 80, 1<br/>CANCELLED: NULL, 1"]
Based on this example, we can add the following context:
4. On
|
| Phase | Scope Supported | Description |
|---|---|---|
| Phase 1 | Only fields with exactly identical value encodings | Fields use the exact same enum values/encoding systems and can be bound equivalently. |
| Later Phases | Explicit value mapping (e.g., CANCELLED ↔ C) |
Will be introduced once mapping syntax, conflict resolution, and bidirectional semantics are fully defined. |
⚠️ Important Principle: Mapping rules for mismatched values must never be guessed or implicitly inferred by the implementation. Subsequent designs must deliberately tackle where mappings are declared, how many-to-one/one-to-many conflicts are handled, and semantic consistency for reverse queries.
Conclusion: We recommend against adopting conformed_from and instead advocate for the decoupled Shared Dimension + Field Binding approach, restricting Phase 1 to identical encoding scenarios. Value mapping capabilities will be deferred as a standalone topic to ensure every abstraction layer maintains a clear, singular semantic responsibility.
5. Deferring role extensions
Proposed Content (Original)
To support this in the spec, we might need to extend the field binding with a
roleattribute to uniquely identify the context.
${ Dimension('country', role='billing') } = 'United States'
Your Point (Original)
This mostly goes away if ③/④ is deferred, since
roleis an attribute on a binding that wouldn't exist yet.
Ifrefdoes land later, the baseline I'd measureroleagainst is two datasets over the same source, with the role carried by the dataset name.
Our Response
- I agree to defer the
roleextension. - This round will establish the Shared Dimension and base
refsemantics without introducing theDimension()macro or default role rules. - Future discussions will need to distinguish between a Dimension's business role and the path role of relationships between datasets.
Other Proposals
3.1 Dataset-Scoped Filter / Metric
I agree with the necessity of Dataset-Scoped Filters and Metrics, but to keep the current proposal focused and deliverable, we are carving this out with the following design principles:
- Split and push independently: We support the design for Dataset-scoped Filters and Metrics but will extract this content into a separate, standalone proposal.
- Two-level scope model: This independent proposal will utilize a Dataset Scope and Model Scope model. The scope of an object is explicitly determined by where it is declared, abandoning the approach of inferring a home dataset from the expression.
- Reference rules and constraints:
- Metric (Dataset Scope): Can only reference other metrics within the same dataset. Cross-dataset metric composition is handled at the Model Scope.
- Filter (Dataset Scope): Expressions can only reference fields within its own dataset. Cross-dataset filters must be declared at the Model Scope.
- Syntax and disambiguation: We will restrict directional referencing using
${dataset.metric}and${dataset.filter}for dataset-scoped objects, reserving bare references for model-scoped objects. The exact syntax will be finalized alongside the overarching namespace strategy. - Enforced disambiguation: A syntax like
${dataset.name}could potentially match either a field, metric, or filter. This proposal must resolve ambiguities via independent namespaces or explicit type tags. If a reference cannot be uniquely resolved, it must throw an error; silent fallbacks or guessing are forbidden. - Validation boundary: The declaration location only dictates scope ownership. The validity of the expression itself is still parsed and validated by the native parser or adapter of the respective language. If validation cannot be completed, the system must return a definitive error message.
3.2 Referencing hidden metrics
Your Point (Original)
Worth confirming explicitly that referencing a hidden metric is legal — otherwise implementations will differ on it.
- Yes, referencing a hidden metric is fully supported and legal.
- The
hiddenflag only controls exposure to consumers; it does not impact compilation, dependency resolution, or the ability of other metrics to reference it. - While this rule falls outside the core focus of this proposal, we recommend formally defining it in PR feat: add extended metadata fields for fields and metrics #287. This proposal simply affirms that metric references should not alter that baseline semantic.
3.3 Parameterized filters
Your Point (Original)
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.
Our Response
- Parameterized Filters are out of scope for this proposal and will be discussed separately as a future extension.
- The current proposal only handles fixed, named filters and inline filters defined at compile time.
- We will adjust the problem description to clarify that a concept like "last 12 months" only falls under the current scope if the time window is fixed. Scenarios passing an
Nparameter at runtime are explicitly excluded from this proposal.
Let us know your thoughts on these adjustments whenever you have a moment.
|
For this comment, I’m going to focus on filters, and set aside metric references for another thread. I’m strongly aligned with @jklahr’s principle of minimizing net-new concepts by biasing towards existing parts of the spec. I suspect the new building blocks we need are more general than filtering. The core pain points I’m seeing:
TL;DR: problem + solution
1. Declaring a filter on a metric / layeringLet's start with expressibility and punt ergonomics to Section 2. One option is to extend the supported expression language to support You can declare In the Layer 2 query doc and #354 we propose that single-dataset metrics can be queried with
gives the sum of sales where I think this directly expresses @jacobwangxt's intrinsic/contextual distinction within the expression language:
And the conflict rule you specified (mutually-exclusive filters -> 0/NULL, never an error) comes for free: ANDing the predicates yields an empty set, and an aggregate over an empty set is NULL/0. No special-casing required. I can put together a spec proposal for this expression language extension, likely with some initial restrictions (e.g. the 2. Avoiding re-declaring a filter conditionDefinitely hear you on the re-declaration pain. The concrete answer available today combines the language extension above with Josh's boolean-dimension idea: declare the condition once as a boolean dimension ( The open design question is whether we want a more general templatized / parameterized primitive — and whether it should be filter-specific or broader. PowerBI's calculation groups tackle exactly this for families of related measures (year-over-year, year-to-date) without repeating boilerplate. I'd rather answer that as a general composability question than build a filters-only mechanism. 3. Complex filter flowsThe scope/flow arguments here apply just as much to dimensions:
So the richer reuse/flow features (pain points 2 and 3) are best designed for filters and dimensions together. One possible reframe: the thing we are really modeling is the metric. For a given metric — which filters impact it, which dimensions can slice it, and what relationship paths are in play? |
|
Really appreciate your insightful points. To make future discussions and alignment easier, could you break down your optimization solutions into a quick summary table corresponding to your points above? That way, everyone can see the problem-to-solution mapping at a glance, making it much more intuitive to learn. Thanks so much! |
|
@jacobwangxt - thanks for the feedback! I've added in a TL;DR table at the top of the comment. I'll put together a PR proposing this initial expression language extension but curious your thoughts on the ideas above |
Uh oh!
There was an error while loading. Please reload this page.
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: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'sdimensionobject only supports an is_time flag, offering no way to capture richer business semantics..datatype,description, andai_contextcan only be defined as field-level attributes, and enumerated values (values) cannot be expressed at the dimension level at all: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:
filters— Model-level Shared Filter${filter_name}metrics.filters— Metric filter fieldjoin_pathdimensions— Model-level Shared Dimensionfields.dimension.ref— Field-to-dimension binding${metric}— Expression-level metric referenceAll changes are optional — models without them behave exactly as before.
Quick Demo (TPC-DS based)
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:
filterfiltersfiltersmetrics.filterstype: derivedmeasurescomposition${measure}composition${metric}syntaxsegmentsalways_filterfiltersdimensions+refKey Differences and Advantages Analysis:
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.jssegments, 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.dimensionsfor 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 declarativerefbinding 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
filtersanddimensionsappropriate as SemanticModel top-level node names?join_pathto individual filter items the right design for cross-dataset filtering?Looking forward to your thoughts! 🙏
All reactions