Skip to content

Automatic translation — the Portuguese version is the reference. View in Portuguese

Updated on 2026-10-03

Relationships, cardinality and filter direction

How tables connect, why cardinality changes the final number and how the filter propagates between fact and dimension.

On this page (11)

A conceptual page about what happens when two tables connect: the cardinality (how many rows on each side), the filter direction (where the slice propagates) and the anti-fan-out protection (why the product sometimes refuses to sum). The step-by-step configuration is in the semantic model; here we explain why each option matters.

Examples use the synthetic workspace Comércio Aurora Ltda (aurora), tables vendas, produtos, clientes, filiais, calendario.


What a relationship is#

A relationship says that a column in one table points to another table's identifier column: vendas.produto_id → produtos.id. When building the SQL, the generator finds the join path by walking the model graph and emits LEFT JOIN always in the fact → dimension direction.

You draw the links by dragging in the editor's visual diagram. Each line in the diagram carries two decisions: cardinality and filter direction.

Where the diagram is

In edit mode, click Relationships on the editor bar (or Data → Diagram). The canvas gives way to the diagram of the model's tables, and the ribbon shows:

GroupButtonWhat it does
ModelSuggest relationshipsSuggests links based on column names; you accept them one by one
ModelArrangeLays out the table cards in a grid
DataShow dataShows a 100-row sample of the model's tables
ZoomLess · Fit · MoreZooms out, returns to 100% and centers, or zooms in

In the diagram itself, a highlighted line warns you when there's a risk: a sample that violates the declared cardinality, an N-N relationship that needs a bridge table, or two tied paths (the generator refuses the query). When you select two tables, the diagram highlights the actual path the generator uses between them.

Relationships tab with the table diagram and the ribbon with Suggest relationships, Arrange, Show data and zoom
Show data open, with the 100-row sample of a model table

Cardinality: which side is "one" and which is "many"#

CardinalityMeaningAurora example
1-N / N-1One on the dimension side, many on the fact side (the normal case).One produto has many vendas.
1-1One row on each side.filiais ↔ a metadata table per branch.
N-NMany on both sides — requires a bridge table.produtos ↔ promocoes via produto_promocao.

Cardinality isn't decorative: it protects your numbers.

Anti-fan-out protection (why the sum is sometimes refused)

When a 1-N join multiplies rows, a sum on the "one" side would be counted several times. Example: filiais has a meta (target) column; if you join filiais to vendas (one branch, many sales) and sum filiais.meta, the target is counted once per sale — inflated.

ingestia.bi detects this row explosion and blocks the affected aggregation, with an explanatory message, instead of silently delivering the wrong total. It's what Power BI solves by requiring correct modeling; here it's an automatic, grain-aware guard.

Warning

If a sum appears blocked, the problem is almost always the grain: you're summing a measure from the "one" side through a join that duplicates it. Put the measure in the fact table with the correct grain, or sum from the right side of the relationship.


Filter direction#

By default, the filter flows from ONE to MANY (dimension → fact): choosing a category in produtos slices the vendas for that category. That's the behavior you almost always want.

Bidirectional (opt-in)

crossFilter: "both" turns on propagation in both directions for a specific relationship. Useful when filtering the fact also needs to slice the dimension — for example, showing only the products that had sales in the filtered period.

Important details:

  • The join graph is undirected (the physical SQL doesn't change when you turn on "both"); what changes is the filter graph.
  • The model detects propagation cycles and ambiguity and refuses a model whose meaning would be undefined — it never silently generates ambiguous SQL.
  • It's opt-in per relationship: an all-"single" model is not changed at all.

Relationship roles (role-playing)#

When the fact table has several dates (order, delivery, payment), you link the same calendario dimension several times, each link with a named role. One relationship is active, the others inactive; the visual chooses the role ("Data do Pedido" × "Data de Entrega", i.e., order date × delivery date) without rewriting any formula. Roles for the same pair of tables form a group that is detected automatically.

Aurora example: calendario linked to vendas by data_pedido (active), data_entrega and data_pagamento (inactive). In the visual, switching from "Data do Pedido" to "Data de Entrega" changes the axis without touching the measures.


N-N relationship through a bridge#

A many-to-many relationship needs a bridge table with keys to both ends. E.g., produtos ↔ promocoes via produto_promocao. When crossing A → bridge → B, the measure on the opposite side is deduplicated (the bridge is reduced to distinct pairs before summing), avoiding double counting. A measure that lives in the bridge itself sums directly, because its grain is the bridge.

Without a declared bridge, an N-N is treated as ambiguous and refused — again, the product prefers to warn rather than get it wrong.


How to choose, in practice#

  • Not sure? Leave 1-N from the fact to the dimension, single direction. That covers the vast majority of cases.
  • Turn on bidirectional only when you need to slice the dimension by the fact, and confirm that the editor didn't flag any ambiguity.
  • Use roles when there are several dates/keys for the same dimension.
  • Use a bridge for any N-N; never force an N-N without one.

Limits and caveats#

  • The physical join is always fact → dimension; there are no in-memory "virtual" relationships.
  • A ceiling of roles per model (MAX_RELATION_ROLES).
  • Join performance depends on the size of the tables in the datalake.

Related#


Last reviewed: 2026-10-03.

Related links