Skip to content

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

Updated on 2026-10-04

Semantic model

The tables, relationships and measures that visuals see as fields.

On this page (8)

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#

  1. What it is — the set of tables in the model and the links (key → key) between them, with cardinality.

  2. When to use it — whenever a dashboard combines more than one table (the vendas fact + the produtos, clientes, filiais, calendario dimensions).

  3. 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 emits LEFT JOIN fact → dimension. There's an interactive visual diagram to draw the links by dragging.

  4. Prerequisites — at least two tables in the prd_aurora_gold dataset with compatible key columns (e.g., vendas.produto_id ↔ produtos.id).

  5. How to validate — in the editor, open the model diagram, link vendas.produto_id to produtos.id; create a bar visual "revenue by produtos.categoria" and confirm that the value appears combining the two tables.

  6. Limits and caveats — relationships are resolved in SQL (there's no in-memory engine like VertiPaq); the physical join is always fact → dimension.

  7. Sample data (synthetic)

    vendas.produto_idvalorprodutos.idprodutos.categoria
    1025010Bebidas
    119011Limpeza
  8. 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#

  1. 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.
  2. When to use it — automatic; it protects any SUM/COUNT/AVG over a 1-N join that would duplicate the measure.
  3. How it works — hopFansOut/joinDuplicatesRows detect 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.
  4. Prerequisites — cardinality declared on the relationship.
  5. How to validate — create a 1-N join that duplicates rows (e.g., filiais linked to vendas by 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.
  6. Limits and caveats — the protection covers aggregations that would duplicate; it doesn't replace good modeling, it only prevents the silent error.
  7. Sample data (synthetic) — filiais (2 rows) × vendas (6 rows) by cidade: summing filiais.meta directly over the join would count the target 3× per branch — that's the blocked case.
  8. 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)#

  1. 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.
  2. 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).
  3. 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.
  4. Prerequisites — a relationship with stable ids (model schema v2).
  5. How to validate — turn on both on 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.
  6. Limits and caveats — it's opt-in; an all-"single" model isn't changed at all (legacy behavior frozen).
  7. Sample data (synthetic) — produtos ↔ vendas with both: filtering vendas.mes = "2026-07" now slices the list of produtos shown.
  8. Evidence — pure detection of propagation cycles/ambiguity covered by a deterministic suite.

Relationship roles (role-playing dimensions)#

  1. What it is — the same dimension linked to the fact several times, each link with a named role (e.g., calendario as "Data do Pedido" (order date), "Data de Entrega" (delivery date), "Data de Pagamento" (payment date)).
  2. 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.
  3. How it works — one relationship is active and the others inactive; the visual chooses the role once (FieldSpec.relationRole) instead of repeating USAR_RELACAO. Roles for the same pair of tables form an automatically detected group.
  4. Prerequisites — multiple relationships between the same pair (one active, the others inactive) and stable ids (model v2).
  5. How to validate — link calendario to vendas by data_pedido (active), data_entrega and data_pagamento (inactive); name the roles; on a visual, switch from "Data do Pedido" to "Data de Entrega" and watch the axis change.
  6. Limits and caveats — a ceiling on roles per model (MAX_RELATION_ROLES).
  7. Sample data (synthetic) — vendas with data_pedido=2026-07-01, data_entrega=2026-07-03, data_pagamento=2026-07-10.
  8. Evidence — role editing and automatic group detection covered by a deterministic suite.

M:N relationship through an explicit bridge#

  1. What it is — resolves an N-N relationship between two tables through a bridge table with an FK to both, without double counting.
  2. When to use it — e.g., produtos ↔ promocoes (one product in several promotions; one promotion with several products) linked by produto_promocao.
  3. 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).
  4. Prerequisites — the bridge table declared on the relationship (BridgeSpec).
  5. How to validate — sum produtos.preco crossing through produto_promocao and confirm that each product counts once per dimension value, not once per bridge row.
  6. Limits and caveats — requires an explicitly declared bridge; N-N without a bridge is treated as ambiguous.
  7. Sample data (synthetic) — produto_promocao: (10→"Julho"), (10→"Inverno"), (11→"Julho"); summing the price from produtos must not triple product 10.
  8. Evidence — bridge resolution and anti-double-counting semantics covered by a deterministic suite.

Calculated columns, hierarchies and data category#

  1. 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.
  2. When to use it — creating margem = receita - custo as a column; building the Região > Estado > Cidade hierarchy for drill-down; marking filiais.uf as a state (UF) for the maps.
  3. 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).
  4. Prerequisites — source columns in the model.
  5. 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.
  6. Limits and caveats — calculated columns are evaluated at materialization; cycles are blocked by a depth limit.
  7. Sample data (synthetic) — filiais: (SP · Sudeste · São Paulo), (RJ · Sudeste · Rio de Janeiro), (MG · Sudeste · Belo Horizonte).
  8. Evidence — hierarchies, sort-by-column, data category and calculated columns covered by a deterministic suite.

Configurable calendars (fiscal, 4-4-5, time zone, locale)#

  1. What it is — a configurable calendar dimension: fiscal year, 4-4-5 (retail), time zone and pt-BR locale.
  2. 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.).
  3. 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/diaSemana mirror the datalake's EXTRACT for pure engine ↔ SQL parity.
  4. Prerequisites — configure the start of the fiscal year and/or 4-4-5 mode and the time zone.
  5. How to validate — set the fiscal year to start on 04/01; confirm that fiscal year 2026 covers 04/01/2026 → 03/31/2027 in a visual by period.
  6. 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.
  7. 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.
  8. 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)#

  1. 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.
  2. When to use it — when several dashboards need the same canonical definition (the same "net revenue" everywhere).
  3. 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 by workspaceId; releases have no update/delete (publishing again creates a new version). Audited.
  4. Prerequisites — an active workspace; an owner/admin role to publish.
  5. How to validate — publish a model, confirm it gets a version and checksum; edit the draft and publish again — it should create version+1 without changing the previous one.
  6. 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.
  7. Sample data (synthetic) — release v1 (checksum sha256:…) with the central measure "Receita"; v2 adds "Ticket médio".
  8. Evidence — release storage, governance (immutable releases) and the dashboard↔model link covered by a deterministic suite.

Related links