This is a conceptual page: it explains the vocabulary you'll find in the semantic model — facts, dimensions, grain, star schema and column types. If you already know these terms from Power BI, you'll recognize everything; the difference is that here modeling is resolved in datalake SQL (there's no in-memory engine like VertiPaq).
The examples use the synthetic workspace Comércio Aurora Ltda (workspace aurora,
datasets prd_aurora_bronze / prd_aurora_silver / prd_aurora_gold), with the tables
vendas (fact), produtos, clientes, filiais and calendario (dimensions).
Why model (and not just "join tables")#
Without a model, each chart repeats the same business decision in a different
way: one sums valor, another sums valor_liquido, a third forgets to
exclude returns. The result is the classic "every report with a different revenue
number".
Modeling is deciding once how the tables connect and what the numbers mean, so that every visual speaks the same language. It's the foundation of the semantic model and of central measures.
Fact × dimension#
Every dimensional model separates two table roles:
| Role | What it holds | In the Aurora example |
|---|---|---|
| Fact | The business's measurable events — one row per occurrence, with numbers that add up. | vendas (one row per item sold: valor, quantidade, custo). |
| Dimension | The context that describes the events — who, what, where, when. | produtos, clientes, filiais, calendario. |
Rule of thumb: if you sum or count it, it's a fact; if you group or filter
by it, it's a dimension. "Revenue by category by month": revenue is a measure from the
vendas fact; category comes from the produtos dimension; month comes from the
calendario dimension.
Granularity (grain)#
The grain is what one row of the fact table represents. In vendas, the grain is "one item of
an order". Defining the grain is the most important decision in the model, because:
- Every fact measure is summed at that grain. Summing
valorgives total revenue because each row is one item. - Mixing grains inflates numbers. If you join
vendas(item grain) with a targets table (branch/month grain), summing the target over the join would count it several times. ingestia.bi detects this row explosion (fan-out) and refuses the aggregation instead of delivering an inflated total — see Relationships and cardinality.
Note
Keep one grain per fact table. If you need numbers at different grains (items and monthly targets), use two fact tables linked to the same dimensions, not a forced join.
Star schema × snowflake#
- Star (recommended): a fact table at the center, surrounded by dimensions, each dimension linked directly to the fact. It's simple, quick to understand and what the SQL generator handles best.
- Snowflake: dimensions normalized into sub-tables (e.g.,
produtos→categorias→departamentos). It works, but each extra hop is one more join. Prefer flattening the dimension (bringingcategoriaanddepartamentointoprodutos) when you can.
In the Aurora example, the star layout is:
calendario
|
clientes — vendas — produtos
|
filiaisThe step-by-step build is in the tutorial Star schema.
Keys and relationships#
A dimension links to the fact table through a key: a fact column points to the dimension's identifier column.
vendas.produto_id→produtos.idvendas.cliente_id→clientes.idvendas.filial_id→filiais.idvendas.data→calendario.data
Each relationship has a cardinality (1-1, 1-N, N-1, N-N) and a filter
direction. These concepts have a real effect on the final number — read
Relationships and cardinality.
Column types#
The catalog classifies each column, and that guides visuals, formatting and what AI can see:
- Numeric — sum, average, count; the basis of fact measures.
- Text / categorical — becomes an axis, legend and filter (
produtos.categoria). - Date / time — links to the
calendariodimension and enables time comparisons. - Geographic data category — marking
filiais.ufas a state (UF) feeds the Brazil maps. - PII (personal data) — columns marked as PII are masked in the secure view and never go out to AI (see Row-level security (RLS) and the AI pages).
Besides the source columns, you create calculated columns (derived row by
row, e.g., margem = valor - custo) and measures (reusable aggregations, e.g.,
Receita = SOMA(valor)). The difference between calculating per row and aggregating in
context is the subject of Row and filter context.
When NOT to worry about modeling#
- A single table, no cross-referencing: one flattened fact table (a wide spreadsheet) already answers most simple charts — model relationships only when you combine tables.
- Throwaway exploration: for a one-off question, the text-to-SQL wizard solves it without requiring a model.
Limits and caveats#
- There's no in-memory columnar engine (VertiPaq); relationships become joins in SQL and the physical join is always fact → dimension.
- N-N requires an explicit bridge table; without a bridge, the relationship is treated as ambiguous and refused.
- The latency, cost and volume of each query depend on the size of your tables in the datalake.
Related#
- Semantic model
- Relationships and cardinality
- Row and filter context
- Tutorial: star schema
- Measures, formulas and KPIs
Last reviewed: 2026-09-08.