The semantic model is where you describe how your tables connect and what
the numbers mean, once, so that every visual speaks the same language.
It lives per dashboard (modelJson) and can also live centralized per
workspace with immutable versions. It's the foundation that prevents "every chart with a
different definition of revenue".
Examples use the synthetic workspace Comércio Aurora Ltda (workspace aurora).
Tables and relationships#
-
What it is — the set of tables in the model and the links (key → key) between them, with cardinality.
-
When to use it — whenever a dashboard combines more than one table (the
vendasfact + theprodutos,clientes,filiais,calendariodimensions). -
How it works — each relationship has a ONE side and a MANY side and a cardinality
1-1 | 1-N | N-1 | N-N. When building the SQL, the generator finds the join path by searching the graph (joinPath) and emitsLEFT JOINfact → dimension. There's an interactive visual diagram to draw the links by dragging. -
Prerequisites — at least two tables in the
prd_aurora_golddataset with compatible key columns (e.g.,vendas.produto_id↔produtos.id). -
How to validate — in the editor, open the model diagram, link
vendas.produto_idtoprodutos.id; create a bar visual "revenue byprodutos.categoria" and confirm that the value appears combining the two tables. -
Limits and caveats — relationships are resolved in SQL (there's no in-memory engine like VertiPaq); the physical join is always fact → dimension.
-
Sample data (synthetic)
vendas.produto_id valor produtos.id produtos.categoria 10 250 10 Bebidas 11 90 11 Limpeza -
Evidence — relationships and join-path engine covered by a deterministic suite and by a relationships contract; the model's SQL is frozen by the reference test suite, with an identical result on every run.
Cardinality and anti-fan-out protection#
- What it is — cardinality has a real effect: when a join multiplies rows, sums would be inflated — the generator refuses the aggregation instead of delivering a wrong number.
- When to use it — automatic; it protects any SUM/COUNT/AVG over a 1-N join that would duplicate the measure.
- How it works —
hopFansOut/joinDuplicatesRowsdetect the row "explosion" along the join path; the affected aggregation is blocked with an explanatory message. It's what Power BI solves by requiring correct modeling; here it's an automatic guard. - Prerequisites — cardinality declared on the relationship.
- How to validate — create a 1-N join that duplicates rows (e.g.,
filiaislinked tovendasby a non-unique key) and try to sum a column from the table on the ONE side; confirm the refusal with a warning instead of an inflated total. - Limits and caveats — the protection covers aggregations that would duplicate; it doesn't replace good modeling, it only prevents the silent error.
- Sample data (synthetic) —
filiais(2 rows) ×vendas(6 rows) bycidade: summingfiliais.metadirectly over the join would count the target 3× per branch — that's the blocked case. - Evidence — fan-out detection (row explosion along the join path) covered by a deterministic suite; behavior pinned by the reference test suite.
Bidirectional filter direction (opt-in)#
- What it is — by default the filter flows from ONE to MANY (dimension → fact).
crossFilter: "both"turns on propagation in both directions for that relationship. - When to use it — when filtering the fact also needs to slice the dimension (e.g., showing only products that had sales in the filtered period).
- How it works — the join graph is undirected (the physical SQL doesn't change when you turn on "both"); the filter graph is directed. The model detects propagation cycles and ambiguity and refuses models whose meaning would be undefined — it never silently generates ambiguous SQL.
- Prerequisites — a relationship with stable ids (model schema v2).
- How to validate — turn on
bothon a relationship, create a second propagation path between the same pair and confirm that the editor flags ambiguity/a cycle instead of silently accepting it. - Limits and caveats — it's opt-in; an all-"single" model isn't changed at all (legacy behavior frozen).
- Sample data (synthetic) —
produtos↔vendaswithboth: filteringvendas.mes = "2026-07"now slices the list ofprodutosshown. - Evidence — pure detection of propagation cycles/ambiguity covered by a deterministic suite.
Relationship roles (role-playing dimensions)#
- What it is — the same dimension linked to the fact several times, each link with
a named role (e.g.,
calendarioas "Data do Pedido" (order date), "Data de Entrega" (delivery date), "Data de Pagamento" (payment date)). - When to use it — when the fact table has several dates (or several keys to the same dimension) and you want to choose which one to use per visual.
- How it works — one relationship is active and the others inactive; the visual
chooses the role once (
FieldSpec.relationRole) instead of repeatingUSAR_RELACAO. Roles for the same pair of tables form an automatically detected group. - Prerequisites — multiple relationships between the same pair (one active, the others inactive) and stable ids (model v2).
- How to validate — link
calendariotovendasbydata_pedido(active),data_entregaanddata_pagamento(inactive); name the roles; on a visual, switch from "Data do Pedido" to "Data de Entrega" and watch the axis change. - Limits and caveats — a ceiling on roles per model (
MAX_RELATION_ROLES). - Sample data (synthetic) —
vendaswithdata_pedido=2026-07-01,data_entrega=2026-07-03,data_pagamento=2026-07-10. - Evidence — role editing and automatic group detection covered by a deterministic suite.
M:N relationship through an explicit bridge#
- What it is — resolves an N-N relationship between two tables through a bridge table with an FK to both, without double counting.
- When to use it — e.g.,
produtos↔promocoes(one product in several promotions; one promotion with several products) linked byproduto_promocao. - How it works — when crossing A → bridge → B, the measure on the opposite side is deduplicated (the bridge is reduced to distinct pairs before summing); a measure that lives in the bridge itself sums directly (its grain is the bridge).
- Prerequisites — the bridge table declared on the relationship (
BridgeSpec). - How to validate — sum
produtos.precocrossing throughproduto_promocaoand confirm that each product counts once per dimension value, not once per bridge row. - Limits and caveats — requires an explicitly declared bridge; N-N without a bridge is treated as ambiguous.
- Sample data (synthetic) —
produto_promocao: (10→"Julho"), (10→"Inverno"), (11→"Julho"); summing the price fromprodutosmust not triple product 10. - Evidence — bridge resolution and anti-double-counting semantics covered by a deterministic suite.
Calculated columns, hierarchies and data category#
- What it is — columns derived by formula (row by row), hierarchies of levels for drill-down, and a data category (e.g., geography/state) per column.
- When to use it — creating
margem = receita - custoas a column; building theRegião > Estado > Cidadehierarchy for drill-down; markingfiliais.ufas a state (UF) for the maps. - How it works — calculated columns chain together with cycle protection (limited depth); hierarchies define the drill order; the data category guides visuals (maps) and formatting. There's sort-by-column (sort months by number).
- Prerequisites — source columns in the model.
- How to validate — create the hierarchy
filiais.regiao > filiais.uf > filiais.cidade, put it on a bar visual and use the drill button to go down one level. - Limits and caveats — calculated columns are evaluated at materialization; cycles are blocked by a depth limit.
- Sample data (synthetic) —
filiais: (SP · Sudeste · São Paulo), (RJ · Sudeste · Rio de Janeiro), (MG · Sudeste · Belo Horizonte). - Evidence — hierarchies, sort-by-column, data category and calculated columns covered by a deterministic suite.
Configurable calendars (fiscal, 4-4-5, time zone, locale)#
- What it is — a configurable calendar dimension: fiscal year, 4-4-5 (retail), time zone and pt-BR locale.
- When to use it — a company whose fiscal year doesn't start in January, or that closes in retail 4-4-5 periods; the basis of time intelligence (YTD, etc.).
- How it works — it works with the civil date (never UTC), avoiding the
10 p.m. December 31 closing bug. The fiscal year is labeled by the calendar year in which it
starts; 4-4-5 uses the "closest" (NRF) rule and the 53rd week goes entirely to the
last period.
semana/diaSemanamirror the datalake'sEXTRACTfor pure engine ↔ SQL parity. - Prerequisites — configure the start of the fiscal year and/or 4-4-5 mode and the time zone.
- How to validate — set the fiscal year to start on 04/01; confirm that
fiscal year 2026covers 04/01/2026 → 03/31/2027 in a visual by period. - Limits and caveats — the locale comes from a fixed table (not
Intl) to guarantee identical bytes between the engine and SQL; a new language requires a new table. - Sample data (synthetic) — a sale on 12/31/2025 at 10:00 p.m. (time zone America/Sao_Paulo) falls into the correct civil year/period, not the following year.
- Evidence — the calendar (pure engine) and its translation to SQL covered by a deterministic suite, with pure engine ↔ SQL parity verified.
Semantic model centralized per workspace (immutable releases)#
- What it is — the definition of tables/relationships/measures can live once per workspace, with immutable releases versioned by checksum, consumed by N dashboards by reference.
- When to use it — when several dashboards need the same canonical definition (the same "net revenue" everywhere).
- How it works — the editor writes to the draft (
draftJson); publishing freezes a copy as a release (version = max+1, sha256 checksum). Anti-IDOR: every query filters byworkspaceId; releases have no update/delete (publishing again creates a new version). Audited. - Prerequisites — an active workspace; an owner/admin role to publish.
- How to validate — publish a model, confirm it gets a
versionand checksum; edit the draft and publish again — it should createversion+1without changing the previous one. - Limits and caveats — the "wiring" that makes dashboards consume the central version by reference is still in progress. The model content is opaque JSON at this layer.
- Sample data (synthetic) — release v1 (checksum
sha256:…) with the central measure "Receita"; v2 adds "Ticket médio". - Evidence — release storage, governance (immutable releases) and the dashboard↔model link covered by a deterministic suite.