You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ossie.apache.org by "jacobwangxt (via GitHub)" <gi...@apache.org> on 2026/08/26 06:45:14 UTC
[D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
GitHub user jacobwangxt created a discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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:
```yaml
# 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:
```yaml
# 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)
```yaml
# ① 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! 🙏
GitHub link: https://github.com/apache/ossie/discussions/342
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "zt19960811 (via GitHub)" <gi...@apache.org>.
GitHub user zt19960811 added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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:
```yaml
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?
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18169782
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "christianeu-db (via GitHub)" <gi...@apache.org>.
GitHub user christianeu-db added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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:
1. Declaring a filter on a metric, and layering additional context-filters on top
2. Avoiding re-declaring a filter condition
3. Encoding complex filter flows
# 1. Declaring a filter on a metric / layering
Let's start with expressibility and punt ergonomics to Section 2. One option is to extend the supported expression language to support `FILTER (WHERE ...)` clauses, which are supported by [PostgresSQL](https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES), [SQLLite](https://www.sqlite.org/lang_aggfunc.html), [DuckDB](https://duckdb.org/docs/current/sql/query_syntax/filter), [Spark](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-aggregate.html) and [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Aggregate-Functions.html).
You can declare `SUM(sales) FILTER (WHERE region = 'North')` — call it `NorthernSales`.
In the [Layer 2 query doc](https://docs.google.com/document/d/1uKc3x9NlvV9O9UPdvlnCTDxsc-XWsQrxb0ylG45H-IQ/edit?tab=t.0#heading=h.5vtm2vjqu4mp) and [#354](https://github.com/apache/ossie/pull/354) we propose that single-dataset metrics can be queried with `MEASURE()`. If `MEASURE()` behaves like other aggregates:
`MEASURE(NorthernSales) FILTER (WHERE category = 'Toys')`
gives the sum of sales where `region = 'North' AND category = 'Toys'`. At least one vendor (Databricks' metric views) already ships this as a SQL extension rather than a first-class filter concept.
I think this directly expresses @jacobwangxt's intrinsic/contextual distinction within the expression language:
- **Intrinsic filter** = the `FILTER WHERE` baked into the metric definition
(`region = 'North'` in `NorthernSales`).
- **Contextual filter** = the `FILTER WHERE` layered at query time (`category = 'Toys'`).
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 `FILTER WHERE` clause must not span a different dataset from the base aggregate).
# 2. Avoiding re-declaring a filter condition
Definitely 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 (`is_northern`), then reference it — `SUM(sales) FILTER (WHERE is_northern)`.
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](https://learn.microsoft.com/en-us/power-bi/transform-model/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 flows
The scope/flow arguments here apply just as much to dimensions:
1. If measure A can be filtered by a filter on dimension B, then A can also be sliced by dimension B.
2. If a filter on dimension B flows to A along a particular relationship path, that is usually the same path by which A is sliced by B.
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?
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18280099
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jacobwangxt (via GitHub)" <gi...@apache.org>.
GitHub user jacobwangxt added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
@jklahr:
## Proposal Split
Thanks 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)**
This is currently the most mature piece with the fewest dependencies, so we would like to push it forward first as an independent proposal. For this iteration, we will strictly focus on defining **model-scoped Metric references**.
* **Dataset-scoped Metric references** (along with PR #343) will be deferred to a subsequent proposal.
* Discussions regarding `hidden` properties will also be moved elsewhere, leaving the actual definition to be handled by PR #287.
**2. ①/② Filter (Continuing with narrowed scope)**
We will continue pushing the Filter proposal, but restrict the scope of this iteration entirely to **model-scoped fixed Filters**.
* **Dataset-scoped Filters** and **Parameterized Filters** will be grouped into future proposals.
* Additionally, we are extracting **Relationship Resolution** out of the Filter proposal to avoid coupling filtering logic with path resolution.
**3. ③/④ Shared Dimension (Separate discussion)**
The primary focus here will be clearly defining the **queryable semantics** (e.g., how they participate in Group By, Joins, and aggregations) to ensure semantic completeness.
* **Cross-model composability** involves broader composition mechanisms and will be deferred to a future proposal.
**4. Relationship Resolution (Standalone foundational specification)**
We will build out Relationship Resolution as a standalone specification for cross-dataset referencing. It will not be tied to any single feature proposal; rather, we will push it in parallel to ensure that higher-level features have a unified, reliable contract to depend on when performing path resolution.
**Relationship Resolution is highly complex, so I’ll add the detailed content separately.**
---
## Current Proposal
### 1. ⑤ Metric Reference
Regarding the aggregation and filtering scope of referenced metrics, we need to add the following clarifications:
##### Proposed Content (Original)
> **⑤ `${metric}` — Expression-level metric reference**
> Derived metrics compose existing metrics; filters auto-inherit.
> 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.
##### Your Point (Original)
> 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.
##### Our Response
**Clarifications and Additions on the Aggregation Scope of Referenced Metrics**
1. **Fully adopting the independent aggregation scope principle:** We agree with your core argument—each referenced base metric must be calculated within an independent aggregation context. The **filters bound to its own definition** have the highest priority and cannot be overwritten or tampered with by outer layers. This guarantees the semantic purity of the base metric.
2. **Clarifying the push-down and merge mechanism for derived metric filters:** We need to explicitly state that when derived metrics carry additional filters, these filters should be treated as **global constraints for the current calculation context**. They must be pushed down using `AND` logic and merged into the internal aggregation expressions of every referenced metric.
3. **Distinguishing between two types of filters to avoid ambiguity:**
* **Intrinsic Filter:** The filtering conditions inherent to a base metric's definition (e.g., `cancelled_orders` inherently comes with `status='cancelled'`). This part is strictly isolated and cannot be modified by outer layers.
* **Contextual Filter:** Additional filtering conditions appended by a derived metric or at the query layer (e.g., restricting to `region='APAC'`). This part must penetrate and be `AND`ed to the aggregations of all referenced metrics.
**Conclusion:** The final aggregation filter of a referenced metric = `Intrinsic Filter AND Contextual Filter`. This protects the semantic integrity of the base metric while ensuring the derived metric calculates correctly for a specific business slice.
##### Illustrated Example
The following example demonstrates how the two types of filters work together when calculating the derived metric `apac_conversion_rate`.
```mermaid
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**
* **Green Node (`Contextual Filter`):** This is the part that gets pushed down. It doesn't belong to any single base metric; rather, it is the analytical constraint for the derived metric in this specific scenario. The dashed arrows indicate it is "injected" rather than "overwritten."
* **Orange Nodes (`Intrinsic Filter`):** This is the part that must be protected. It is intrinsic to the base metric's identity, with solid lines indicating inherent ownership.
* **Grey Merge Nodes (`AND`):** The final `WHERE` condition passed to the aggregate function is the intersection of the two. This neither destroys the base definition nor drops the outer constraints.
When writing this rule into the spec, we will add an additional **conflict resolution principle**:
> If a Contextual Filter and an Intrinsic Filter apply mutually exclusive conditions to the same field (e.g., the outer layer requires `status='pending'`, but the base metric inherently requires `status='completed'`), the `AND` merge will result in an empty set. **The system should not throw an error or silently drop either condition.** Instead, it should return 0/NULL, leaving it to the user to identify the filtering conflict. This maintains the determinism and predictability of the semantic model.
---
### 2. ①/② Filter
Regarding the standalone Shared Filter node, we need to add the following context:
1. Forcing a Shared Filter into a field's `label` attribute is not a good idea. The `label` does not formally define filter behaviors and cannot express the auto-mounting and inheritance of a metric filter.
2. A standalone `filters` node explicitly defines the Filter as an object, unifying its referencing, validation, scoping, and inheritance rules.
We agree with your core concern about "avoiding conceptual overlap," but **retaining a standalone `filters` node as the primary mechanism for named, reusable filters remains the better choice**. Here is why:
1. **Field Labels cannot carry the full semantics of a filter**
Fusing a shared filter into a field's `label` attribute is not a sound approach. In the spec, `label` is strictly used for display names and metadata descriptions; it lacks the capacity to formally define a Filter. More importantly, it cannot express core mechanisms necessary for metric filters, such as auto-mounting, scope inheritance, and cross-dataset `join_path` resolution. Using it as a substitute for a standalone node essentially forces an incomplete container to assume responsibilities far beyond its design boundaries.
2. **A standalone node provides a unified and complete object model**
A standalone `filters` node elevates filters to a first-class object, giving them an explicit identity. This ensures that reference resolution, syntax validation, scoping rules, and inheritance logic can be uniformly defined under a single, clear contract, rather than being scattered across implicit field attribute conventions. While this introduces an additional top-level concept, it buys semantic completeness and implementability.
3. **The real solution to conceptual overlap is defining clear boundaries, not merging**
We agree the spec should not allow authors to have two equivalent ways of writing the same thing. Therefore, we should adopt the following delineation to eliminate ambiguity:
* **Standalone `filters` node:** The **sole standard mechanism** for all named, reusable business filters.
* **Metric-level `filters` field:** Exclusively used for declaring references to shared filters and defining non-reusable inline filtering conditions. It no longer bears the responsibility of defining named shared filters.
By drawing these boundaries, the `filters` node and the metric filter field each serve a distinct purpose. This preserves the expressive power of an independent object model while eliminating author ambiguity regarding "where to define a shared filter."
---
### 3. ③/④ Shared Dimension
#### 1. Additional Context
Some context from actual systems we are building:
1. Shared dimension values are provided by knowledge mining systems. The source can be either user-declared enum values or values queried directly from tables.
2. A Shared Dimension is responsible for storing and reusing business semantics; relationships are solely responsible for correlating datasets.
3. Our current real-world pain point is primarily redundant definitions, followed by cross-model reuse. We want to define a dimension once in a public model and have other models reference it, rather than creating a dummy dataset for every shared field.
4. We have already implemented an initial version of cross-model Shared Dimensions, but we are holding this back for a future proposal.
#### 2. Single-Model vs. Cross-Model Reuse
##### Proposed Content (Original)
> **③ `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.
##### Your Point (Original)
> 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.
> So this doesn't really give you a single source of truth for dimension semantics.
##### Our Response
1. The ultimate goal is cross-model reuse, but this proposal intentionally targets in-model reuse first.
2. We will scope "single source of truth" strictly to the semantic model scope to avoid overstating the current capability.
3. Cross-model namespaces, resolution, version management, and missing reference behaviors require a dedicated proposal.
4. In our actual environments involving cross-team and cross-model scenarios, shared dimensions are typically defined in a common model and referenced by others; however, we are deferring the definition of this referencing protocol for now.
#### 3. Does Shared Dimension provide a queryable consistent dimension?
##### Proposed Content (Original)
> 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.
##### Your Point (Original)
> Need 1, shared metadata. `description`, `ai_context` and enum values written once instead of per-field.
> Need 2, a shared queryable dimension. One dimension any metric can be grouped by, whichever fact it came from.
> ③/④ delivers Need 1 and not Need 2.
##### Our Response
1. The design goal explicitly supports *both* Need 1 and Need 2. Shared Dimensions are used both to manage metadata centrally and to declare consistent query semantics across datasets.
2. **Need 1** is fulfilled directly by the centralized definition. `datatype`, `description`, `ai_context`, and enum values are defined once, and all fields bound via `dimension.ref` share this definition.
3. **Need 2** is also baked into the semantics of `dimension.ref`. Fields referencing the same Shared Dimension are treated as a single logical dimension. When a query groups by this Shared Dimension, the compiler resolves the corresponding bound field within the dataset of each metric, completes the aggregation locally, and then aligns the results based on the unified dimension values. This expresses a semantic binding between fields and business dimensions, not a physical relationship between datasets.
4. When a query uses a Shared Dimension to group multiple fact metrics, the compiler must resolve the bound field for each metric in its respective dataset and perform the calculation within that local aggregation scope. The compiler then aligns the results from each metric according to the Shared Dimension's unified domain, rather than directly joining multiple fact tables.
5. Query results for a Shared Dimension should use unified dimension names, data types, and value semantics. The underlying physical field names can differ (e.g., `transactions.status` and `orders.order_status`), as long as they reference the same Shared Dimension and meet compatibility requirements.
6. To satisfy **Queryable semantics**, the following conditions must be met:
* Every dataset participating in the query must be able to uniquely resolve to a field bound to that Shared Dimension.
* The data types of the bound fields must be compatible with the Shared Dimension's `datatype`.
* For phase one, bound fields must use identical physical value encodings.
* If multiple candidate fields exist in the same dataset, the compiler is not allowed to make an implicit choice.
* If a metric's dataset lacks a corresponding binding, that metric cannot be grouped by this Shared Dimension.
7. For cases with mismatched physical encodings (e.g., `CANCELLED` vs. `C`), an explicit value mapping mechanism is required. Until that mechanism is formalized, such bindings should be treated as incompatible rather than being implicitly converted by the compiler. To keep this proposal manageable, explicit value mapping will be deferred to a later proposal.
8. The Validator must check for binding uniqueness, datatype compatibility. If there are missing bindings, ambiguous candidates, type conflicts, it must return a clear validation error. User SHOULD guarantee value domain consistency.
**Example Illustration:**
In the example below, both `transactions.status` and `orders.order_status` are bound to the Shared Dimension `order_status`. A query needs to view both transaction amount and order count grouped by order status.
```mermaid
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:
1. `order_status` is a logical dimension, not a physical dimension table that must exist. It centrally defines the name, datatype, description, and enum values.
2. `transactions.status` and `orders.order_status` have different physical names but are declared as the same business dimension via `dimension.ref: order_status`.
3. When a query groups by `order_status`, the compiler resolves the bound field for each Metric independently:
* `transaction_amount` belongs to `transactions`, so it uses `transactions.status`.
* `order_count` belongs to `orders`, so it uses `orders.order_status`.
4. The two Metrics are calculated independently within their respective datasets and aggregation scopes. The compiler must not join `transactions` directly with `orders`, as that could lead to row fan-out and double-counting across fact tables.
5. Once aggregations are completed on both sides, the results are aligned using the Shared Dimension's unified values. In this example, `PAID` and `SHIPPED` yield results on both sides; `CANCELLED` only appears on the order side.
6. If a dimension value does not exist on one side, it explicitly returns `NULL`.
7. The Validator must check that:
* Each Metric's dataset uniquely identifies a field bound to `order_status`.
* The datatypes of bound fields are compatible with the Shared Dimension.
* No single dataset contains multiple indistinguishable bound fields.
* Cross-fact metrics can be aligned at the same granularity.
#### 4. On `conformed_from`
##### Your Point (Original)
> Proposal: make conformance declarative rather than adding a dimensions registry.
> `conformed_from:`
> `- transactions.status`
> `- orders.order_status`
> It covers both needs. Metadata is written once, and the result is a real dimension entity, so cross-fact grouping works.
##### My Thought
`conformed_from` elevates field-level business concepts into a dataset construction rule. We believe this abstraction is too heavy: expanding the sourcing semantics of a dataset just to describe a shared field couples logical dimensions, data generation, and relationship inference all together. Shared Dimensions provide a more direct way to express business concepts.
1. The intent here is to express the binding between a field and a business Dimension, not to create a new dataset.
2. Fields referencing the same Dimension naturally belong to the same Join and Group By dimension semantically. The exact query rules should be defined by the Shared Dimension spec.
3. Phase one will only support fields with identical value encodings.
4. Value-mismatched scenarios like `CANCELLED` vs. `C` require explicit mapping, but we are excluding them from this round until we have concrete use cases and complete rules.
5. Future designs will need to address mapping locations, conflict resolution, and bidirectional translation, which cannot be left to the implementation to guess.
**the abstraction level of `conformed_from` maybe is too heavy and unsuitable as the core mechanism for shared dimensions**. Here is our reasoning and alternative approach:
**1. Our concern with `conformed_from`: Misaligned abstraction level**
`conformed_from` essentially elevates a field-level business concept binding into a construction rule for datasets. To describe the semantics of "multiple fields represent the same business dimension," it extends the dataset source definition. This forcefully couples three concerns that should remain independent:
* **Logical dimension semantics** (which fields represent the same business concept).
* **Data generation/materialization rules** (how to synthesize a dimension entity from source tables).
* **Relationship inference** (deriving join paths across fact tables).
This coupling makes the definition of a shared dimension impure—it becomes both a semantic declaration and a physical build instruction. When we eventually need to support value mapping, incremental materialization, or external dimension sources, this overloaded structure will be very difficult to extend.
**2. Shared Dimension as a direct business concept binding**
The essence of a shared dimension is **the binding relationship between a field and a business Dimension**, not the creation of a new dataset. We recommend retaining the dual-layer structure proposed: Model-level Shared Dimension + Field-to-dimension binding.
* **Semantic Orthogonality:** A Shared Dimension simply declares "these fields are operationally equivalent in business terms," without dictating how the data is materialized.
* **Natively supports cross-fact grouping:** Fields referencing the same Shared Dimension automatically fall under the same Group By dimension semantically, without needing to materialize an intermediate entity.
* **Better Extensibility:** If we need to support value mapping or external dimension sources in the future, we only need to extend the Shared Dimension object itself, without altering dataset sourcing semantics.
**3. Phased Implementation Boundaries**
To ensure the spec is actionable, we are defining the following phases:
| 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 `role` attribute to uniquely identify the context.
> `${ Dimension('country', role='billing') } = 'United States'`
##### Your Point (Original)
> This mostly goes away if ③/④ is deferred, since `role` is an attribute on a binding that wouldn't exist 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.
##### Our Response
1. I agree to defer the `role` extension.
2. This round will establish the Shared Dimension and base `ref` semantics without introducing the `Dimension()` macro or default role rules.
3. 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:
1. **Split and push independently:** We support the design for Dataset-scoped Filters and Metrics but will extract this content into a separate, standalone proposal.
2. **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.
3. **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.
4. **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.
5. **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.
6. **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.
1. Yes, referencing a hidden metric is fully supported and legal.
2. The `hidden` flag only controls exposure to consumers; it does not impact compilation, dependency resolution, or the ability of other metrics to reference it.
3. While this rule falls outside the core focus of this proposal, we recommend formally defining it in PR #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
1. Parameterized Filters are out of scope for this proposal and will be discussed separately as a future extension.
2. The current proposal only handles fixed, named filters and inline filters defined at compile time.
3. 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 `N` parameter at runtime are explicitly excluded from this proposal.
Let us know your thoughts on these adjustments whenever you have a moment.
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18261247
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "djwaldo (via GitHub)" <gi...@apache.org>.
GitHub user djwaldo added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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 `filters[]` into a Snowflake semantic view, the filter emits as a named `filters:` entry, and Cortex Analyst applies it *AI-optionally*, deciding per query whether it is relevant, rather than on every query the way the ThoughtSpot original was. The filter is present in both models. It is honoured in one and negotiable in the other. The same question asked two ways returns two different numbers, and nothing errors.
That is worth raising here because **the proposal does not currently say whether a filter is mandatory**, and I do not think a `filters` node is safe without it. Two conforming consumers can read the same node and disagree about whether it is a constraint or a hint, which is the interoperability failure the node exists to prevent, reintroduced one level up. Whatever shape lands, a new node, or @jklahr's `label: filter` route, I would ask that the spec state the application semantics explicitly, and that "mandatory" be the default. An advisory filter is a genuinely useful thing, but it should have to say so.
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'
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18227308
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "wanggaohang (via GitHub)" <gi...@apache.org>.
GitHub user wanggaohang added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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:
- Changes become costly. When a business definition changes, every metric that depends on it must be identified and updated accordingly.
- Filters and metrics are often owned by different roles. Business definitions are typically established and maintained by domain experts, who may not be aware of every metric that depends on a particular definition. It is also difficult to ensure that every metric owner updates their metric in a timely manner.
Modeling these filters as `is_xxx` Boolean fields with `label: filter` within a dataset can certainly reduce some duplication. However, I do not think this approach covers every scenario. Some filters span multiple datasets and therefore have no single natural dataset to which they belong. In other cases, the governance owner, change cadence, and lifecycle of a filter may not align with those of any particular dataset; the filter may instead belong to the semantic model or business domain as a whole.
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?
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18246966
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jacobwangxt (via GitHub)" <gi...@apache.org>.
GitHub user jacobwangxt added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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:
```yaml
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)`:
```yaml
# 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.
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18170317
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jacobwangxt (via GitHub)" <gi...@apache.org>.
GitHub user jacobwangxt added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
@christianeu-db
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!
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18282564
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jacobwangxt (via GitHub)" <gi...@apache.org>.
GitHub user jacobwangxt added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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. 😊
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18263901
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jklahr (via GitHub)" <gi...@apache.org>.
GitHub user jklahr added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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](https://github.com/apache/ossie/pull/287), the
define-once/reference-many case works with no schema change at all:
```yaml
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:
```yaml
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](https://github.com/apache/ossie/pull/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`:
```yaml
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.
```yaml
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.
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18180295
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jklahr (via GitHub)" <gi...@apache.org>.
GitHub user jklahr added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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.
```yaml
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](https://github.com/apache/ossie/blob/main/ontology/ontology.md)
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.
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18180771
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "christianeu-db (via GitHub)" <gi...@apache.org>.
GitHub user christianeu-db added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
RE: querying, you should check out [this document](https://docs.google.com/document/d/1EgG5vOlJSubm01S1AGMJ4MKcMcjxBhJHenGKWMr849E/edit?tab=t.0#heading=h.k8nomy2jicfx), 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 (https://github.com/apache/ossie/pull/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 (https://github.com/apache/ossie/pull/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).
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18273710
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "yang85470-afk (via GitHub)" <gi...@apache.org>.
GitHub user yang85470-afk added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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?
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18158689
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "christianeu-db (via GitHub)" <gi...@apache.org>.
GitHub user christianeu-db added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
@jacobwangxt I've put together the PR for the expression language extensions here: https://github.com/apache/ossie/pull/382
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18405651
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jacobwangxt (via GitHub)" <gi...@apache.org>.
GitHub user jacobwangxt added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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.
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18160555
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "christianeu-db (via GitHub)" <gi...@apache.org>.
GitHub user christianeu-db added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
@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
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18293960
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "christianeu-db (via GitHub)" <gi...@apache.org>.
GitHub user christianeu-db edited a comment on the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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:
1. Declaring a filter on a metric, and layering additional context-filters on top
2. Avoiding re-declaring a filter condition
3. Encoding complex filter flows
# TL;DR: problem + solution
| # | Pain point | Proposed solution | Net-new concept | Status / open question |
|---|---|---|---|---|
| 1 | Declare a filter on a metric, then layer context filters on top | Extend the expression language with `FILTER (WHERE ...)`. **Intrinsic** = `FILTER WHERE` baked into the metric definition (`NorthernSales`); **contextual** = `FILTER WHERE` added at query time. Conflict rule (mutually-exclusive filters → NULL/0, never an error) follows from `AND` over an empty set. | Expression-language extension only — no first-class filter object | Ready to spec. Initial restriction: the `FILTER WHERE` clause must not span a different dataset from the base aggregate. |
| 2 | Avoid re-declaring a filter condition | Combine the Section 1 extension with a boolean dimension: declare the condition once (`is_northern`), then reference it — `SUM(sales) FILTER (WHERE is_northern)`. | Reuses boolean dimensions (@jklahr's idea) | Available today. Open question: whether to add a general templatized/parameterized primitive (cf. PowerBI calculation groups). Prefer to solve as general composability, not a filters-only mechanism. |
| 3 | Encode complex filter flows | Design filter flow and dimension slicing **together**, since the scope/path arguments apply to both. Reframe around the *metric*: which filters impact it, which dimensions slice it, which relationship paths apply. | Shared relationship/flow model spanning filters + dimensions | Deferred — needs a joint filter+dimension design. |
# 1. Declaring a filter on a metric / layering
Let's start with expressibility and punt ergonomics to Section 2. One option is to extend the supported expression language to support `FILTER (WHERE ...)` clauses, which are supported by [PostgresSQL](https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES), [SQLLite](https://www.sqlite.org/lang_aggfunc.html), [DuckDB](https://duckdb.org/docs/current/sql/query_syntax/filter), [Spark](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-aggregate.html) and [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Aggregate-Functions.html).
You can declare `SUM(sales) FILTER (WHERE region = 'North')` — call it `NorthernSales`.
In the [Layer 2 query doc](https://docs.google.com/document/d/1uKc3x9NlvV9O9UPdvlnCTDxsc-XWsQrxb0ylG45H-IQ/edit?tab=t.0#heading=h.5vtm2vjqu4mp) and [#354](https://github.com/apache/ossie/pull/354) we propose that single-dataset metrics can be queried with `MEASURE()`. If `MEASURE()` behaves like other aggregates:
`MEASURE(NorthernSales) FILTER (WHERE category = 'Toys')`
gives the sum of sales where `region = 'North' AND category = 'Toys'`. At least one vendor (Databricks' metric views) already ships this as a SQL extension rather than a first-class filter concept.
I think this directly expresses @jacobwangxt's intrinsic/contextual distinction within the expression language:
- **Intrinsic filter** = the `FILTER WHERE` baked into the metric definition
(`region = 'North'` in `NorthernSales`).
- **Contextual filter** = the `FILTER WHERE` layered at query time (`category = 'Toys'`).
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 `FILTER WHERE` clause must not span a different dataset from the base aggregate).
# 2. Avoiding re-declaring a filter condition
Definitely 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 (`is_northern`), then reference it — `SUM(sales) FILTER (WHERE is_northern)`.
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](https://learn.microsoft.com/en-us/power-bi/transform-model/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 flows
The scope/flow arguments here apply just as much to dimensions:
1. If measure A can be filtered by a filter on dimension B, then A can also be sliced by dimension B.
2. If a filter on dimension B flows to A along a particular relationship path, that is usually the same path by which A is sliced by B.
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?
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18280099
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jklahr (via GitHub)" <gi...@apache.org>.
GitHub user jklahr added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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:
```yaml
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:
```yaml
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?
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18182665
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org
Re: [D] [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References [ossie]
Posted by "jklahr (via GitHub)" <gi...@apache.org>.
GitHub user jklahr added a comment to the discussion: [IDEA & PROPOSAL] Ossie Extension: Shared Filters, Shared Dimensions & Metric References
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)](https://github.com/apache/ossie/pull/343) 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.
```yaml
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](https://github.com/apache/ossie/pull/287) adds a `hidden` flag. Combined with ⑤
that gives the intermediate-calculation pattern, which several implementations support:
```yaml
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.
GitHub link: https://github.com/apache/ossie/discussions/342#discussioncomment-18180190
----
This is an automatically sent email for dev@ossie.apache.org.
To unsubscribe, please send an email to: dev-unsubscribe@ossie.apache.org