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:
| Group | Button | What it does |
|---|---|---|
| Model | Suggest relationships | Suggests links based on column names; you accept them one by one |
| Model | Arrange | Lays out the table cards in a grid |
| Data | Show data | Shows a 100-row sample of the model's tables |
| Zoom | Less · Fit · More | Zooms 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.
Cardinality: which side is "one" and which is "many"#
| Cardinality | Meaning | Aurora example |
|---|---|---|
1-N / N-1 | One on the dimension side, many on the fact side (the normal case). | One produto has many vendas. |
1-1 | One row on each side. | filiais ↔ a metadata table per branch. |
N-N | Many 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-Nfrom 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#
- Semantic model
- Dimensional modeling concepts
- Row and filter context
- Tutorial: star schema
- The editor ribbons
Last reviewed: 2026-10-03.