Skip to content

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

Updated on 2026-09-08

Tutorial: build a star schema

Step by step to link a sales fact table to its dimensions and get correct numbers in any cross-tab.

On this page (8)

By the end of this tutorial you'll have a working star schema: the vendas (sales) fact table linked to the produtos, clientes, filiais and calendario dimensions, ready to slice revenue by any attribute without inflating the numbers.

If the terms "fact", "dimension" and "cardinality" are new to you, first read Dimensional modeling concepts and Relationships and cardinality.

Example workspace: Comércio Aurora Ltda (workspace aurora, dataset prd_aurora_gold).

Prerequisites#

  • An active workspace and the tables in the prd_aurora_gold dataset (or demo mode).
  • Tables with compatible key columns (see below).
  • A role with permission to edit the model (see Roles (RBAC)).

The example tables#

TableRoleKey
vendasFact (grain: order item)produto_id, cliente_id, filial_id, data
produtosDimensionid, categoria, marca
clientesDimensionid, segmento, cidade
filiaisDimensionid, uf, regiao, cidade
calendarioTime dimensiondata, ano, mes, trimestre

Step by step#

  1. Open the model diagram

    In the dashboard editor, open the semantic model diagram (see Semantic model).

  2. Confirm the fact table

    Check that vendas is at the center. It holds the numbers you'll add up (valor, quantidade, custo).

  3. Link products

    Drag from vendas.produto_id to produtos.id. Cardinality N-1 (many sales to one product), filter direction single (products → sales).

  4. Link customers and branches

    Repeat: vendas.cliente_id → clientes.id and vendas.filial_id → filiais.id, both N-1 / single.

  5. Link the calendar

    Link vendas.data → calendario.data (N-1 / single). This relationship is what enables time comparisons.

  6. Mark the geographic category

    In filiais, mark uf as a geographic category (UF, Brazilian state) to enable the Brazil maps.

  7. Validate with a visual

    Create a bar chart "revenue by produtos.categoria" and confirm that the value crosses vendas × produtos.

Sample data (synthetic)#

vendas:

produto_idcliente_idfilial_iddatavalor
10112026-07-01250
11222026-07-0290

produtos: (10, Bebidas, Marca A), (11, Limpeza, Marca B).

Expected result#

  • "Revenue by category" shows Bebidas=250, Limpeza=90.
  • Switching the axis to filiais.regiao or clientes.segmento works without reconfiguring anything — that's the payoff of the star.
  • Summing a dimension column that the join would duplicate is blocked with a warning, rather than delivered inflated (anti-fan-out protection).

Common mistakes#

  • Linking by the wrong column: make sure the fact table's key points to the dimension's identifier column (id, data), not to some arbitrary attribute.
  • N-N without a bridge: if two tables are many-to-many (e.g., products × promotions), you need a bridge table — see Relationships and cardinality.
  • Summing a target on the "one" side: if summing a dimension column gets blocked, the problem is the grain; sum from the correct fact table.

Next steps#

Related#


Last reviewed: 2026-09-08.

Related links