Make Relationship Cardinality Explicit

[mullinsms]

OSI models relationships primarily through join keys (entities) and inferred join behavior. This works well for many star-schema cases, but it leaves key relationship semantics implicit, which can make SQL generation and portability across semantic layers (e.g., Coginiti, Cube, AtScale, BI tools) either lossy or error-prone.

Suggested enhancement
Introduce an optional relationship block (or relationship annotations) that can be attached to an entity pair / join key mapping, such as: cardinality: one_to_one | one_to_many | many_to_one | many_to_many

[PioMen]

To your point, maybe this should be even extended by custom joining criteria? Currently there is from_column and to_column. In some joins you need to add custom logic (like join on pk + date (either current or between other dates).

[christianeu-db]

There is #4 covering more complex relationship conditions. It's definitely worth upvoting that item

[djwaldo]

+1 from the ThoughtSpot converter work (#285), with a data point: this isn't hypothetical loss. Every shipped converter is already working around it. The Databricks converter derives cardinality from from/to orientation, and ThoughtSpot has four cardinality values and five join types that orientation alone cannot encode, so we stash both in custom_extensions. No other tool can see them. An optional type/cardinality on Relationship (defaulting to today's inferred behavior for backward compatibility) would let converters stop hiding semantics that affect generated SQL.

[MarioDeFelipe]

Field evidence in favor of making cardinality explicit and first-class:
Tested, we generate an ontology from a live SAP landscape and project it into agent platforms. Same model, but two production-style targets: one carries cardinality natively on its relationship construct; the other has no field for it anywhere in its relationship schema
Result: we verified the schema itself, not just the docs — so a rule as basic as "a customer has many sales areas; each sales area belongs to one customer" survives only as prose inside a description, which an agent may read or may not.
The lesson we took: when cardinality is explicit in the interchange, a platform that can't represent it becomes a documented, visible limitation. When it's implicit, the same gap becomes a silent per-project discovery — and silent is the expensive kind once agents act on the model.

One request from the closed-world side of the house: keep the declaration cheap for sources that always know their cardinality (SAP and ERP-class models never guess it), so emitters can declare rather than infer. Open for discussion

[fanruan-tuco]

According to the Core Metadata Specification (https://github.com/apache/ossie/blob/main/core-spec/spec.md#L177-L189), from is described as the “many side” and to as the “one side.” However, the YAML specification
(https://github.com/apache/ossie/blob/main/core-spec/spec.yaml#L143-L156) states that relationships may be either many-to-one or one-to-one.

This wording seems inconsistent because a one-to-one relationship has no “many side.” Would it be more accurate to define from as the referencing side and to as the referenced side, while modeling cardinality separately with
an explicit property?

[christianeu-db]

Looking through the comments, advanced relationship functionality is a good topic for the language working group. Let me synthesize (please correct me where I have read this wrong).

Many BI tools can write SQL that safely computes measures against dimensions from other tables at different grains even without cardinality. Using the layering framing in this document, the SQL generation logic encoded by the Layer 3 (dimensional) document should be able to safely orchestrate this de-duplication while the Layer 2 (metrics) document proposes adding this functionality to SQL itself. Perhaps one output of Ossie is to make sure these techniques for avoiding chasm/fan traps are well published.

Given this framing, there are a few ways that relationship cardinality can be valuable:

SQL Generation (@djwaldo): You should be able to significantly simplify multi-table queries if you have cardinality (or referential integrity) metadata.
Ontological metadata (@MarioDeFelipe): Cardinality is useful for encoding how the business concepts fit together (e.g. a salesperson covers multiple regions). Can you tell more about your use case? An interesting question is should this metadata live at the level 2/3 Layer or the Level 4 (Ontology) Layer of Ossie.
Dimension / filter flow: Cardinality determines relative granularities between datasets. These granularities are useful for determining - for a measure - what are the best paths to reach dimensions in the graph / which filters should impact a given measure. In some tools, this flow behavior is encoded by cardinality / layout while other approaches (e.g. DAX) provides a rich language for controlling dimension / filter flow. The question is should we use cardinality or another attribute to encode this flow logic?
@fanruan-tuco: you flagged the M:1 v. 1:1 language issue. That should be closed up - I think the documentation is trying to focus more on relative cardinality between datasets for the purpose of query generation but is adding confusion.

I’d also be interested in more details on @djwaldo’s join types. This could be related to language support for cross-table measures. For example, a metric which left joins is different from one that inner joins. The former captures all row values (e.g. count of open tickets) while the latter is a filtered measure (e.g. count of tickets that have an assignee)

[MarioDeFelipe]

@christianeu-db your read of my point is right.

I think cardinality belongs in both places, because in each place it means something different.

At the ontology layer it is a business rule: A sales area belongs to one customer. That stays true whether or not anyone runs a query, and whether or not the tables get reorganised next year. At the dimensional/metric layer it describes the data as physically laid out, which is what you need for join safety and path selection.

What I would add from the ERP side is the direction it travels. In SAP, cardinality is not discovered from the data — it is declared in the model and enforced when the record is written. We know it before any table exists. Inferring it from keys (§6.4) is the right default when nobody knows, but it should not be the only route if its known. Let sources that know declare it, and let it flow downward.

The two can also disagree. The business rule says one customer per sales area, but the data may hold exceptions from an old migration. That is worth knowing about. If both layers share one attribute, you have to pick a winner, and you lose the signal. I would rather see a typed error than a silent choice — the same point I made on #246.

On filter flow, I would keep it separate. Cardinality is a fact about the business. Filter flow is a decision about how you want queries to behave, and two teams can make different decisions over the same data. If one attribute carries both, a consumer cannot tell which part describes the business and which part describes whatever tool the model came from.

My use case, since you asked. #310 has an SAP Business Partner model. Customer is a role that a Business Partner plays: one partner can play several roles, and each role belongs to exactly one partner. No table layout expresses that — it is a relationship between concepts, not between datasets. That is my clearest example of cardinality that only exists at Layer 4. Below it, a Customer has many sales areas, each identified by customer plus sales organisation, distribution channel, and division. Payment terms hang off the sales area, not the customer. So "what are this customer's payment terms" has no single answer, and an agent only knows that if the model says so.

One last thing on your left join versus inner join example. It assumes the missing case is NULL. In SAP data, it usually is not — it is an initial value like '' or '00000000'. That value is returned, the row comes back, and the missing assignee looks like a real key value. It does not change your point, but it means the join distinction is only as good as the source's ability to declare its sentinel values.