Skip to content

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

Updated on 2026-10-04

Dimensional modeling concepts

Facts, dimensions, grain, star schema and column types — the ingestia.bi modeling vocabulary.

On this page (9)

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:

RoleWhat it holdsIn the Aurora example
FactThe business's measurable events — one row per occurrence, with numbers that add up.vendas (one row per item sold: valor, quantidade, custo).
DimensionThe 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 valor gives 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 (bringing categoria and departamento into produtos) when you can.

In the Aurora example, the star layout is:

code
        calendario
            |
clientes — vendas — produtos
            |
         filiais

The 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.id
  • vendas.cliente_id → clientes.id
  • vendas.filial_id → filiais.id
  • vendas.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 calendario dimension and enables time comparisons.
  • Geographic data category — marking filiais.uf as 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#


Last reviewed: 2026-09-08.

Related links