HomeAboutProjectsHow I workContact
03Data modelling

ModelThe model is decided before opening Power BI.

It’s the step nobody sees, and everything people do see depends on it. Here I decide what each row represents, what shape the tables take and how each number is computed.

SQLBigQueryStar schema
00What modelling is

Modelling is arranging the dataso every question has one single answer.

The problem

✗They ask if we hit the budget. The table says 20%.

✓We sold $8,000 of $10,000: that’s 80%. The table lies.

↓ First: what is each row?

Grain

✗The monthly budget repeats on every row.

✓Each row is one receipt line. The budget leaves the table.

↓ What’s left splits in two.

Fact table

✗Numbers and descriptions, all together.

✓What happened stays here: quantity and amount.

↓ Each sale’s context is missing.

Dimension tables

✗“Downtown” and “Coffee” repeat across thousands of rows.

✓Each value, once: when, where, what and who.

↓ Now they need joining.

Keys and relationships

✗The tables don’t talk, and the budget is still $40,000.

✓A key joins them. The budget arrives once: $10,000.

↓ Only the calculation is left.

Measures

✗The 20% was stored in the table.

✓The measure computes with what you filter: $8,000 ÷ $10,000 = 80%.

✓ Done: one single answer.
“Did we hit the October budget?”
Sales$8.000
÷
Budget$40.000
=
20 %
00Why it matters

Everyone looks at the flowers.The model is the trunk.

Same data, same dashboard. The only thing that changes is the model.

When…
1You ask if you hit it
2You filter by store
3You open the report
4Someone asks for a change
5You ask Copilot
6Whoever built it leaves
✗No model
20%The budget got summed 4 times
BreaksEach visual filters differently
14 sToo many columns
DaysThe reports break
Makes it upIt can’t read the tables
StuckOnly they got it
✓With a model
80%One single answer
WorksIt filters every visual
1 sOnly what’s needed
MinutesIt changes in one place
AnswersIt reads the model
ContinuesClear without explaining
00What I do and don’t

What I don’t do,and what I do.

1Where I start
✗I don’t
Chart→Patch→Patch

From the visuals, then patch

✓I do
Decision→Model→Chart

From the decision

2The grain
✗I don’t
3-oct$8.500
oct$40.000

Mix grains

✓I do
1041Coffee$5.000
1041Croissant$1.500

One line, and I test it

3The schema
✗I don’t

Data Vault for one Excel

✓I do

Star schema

4Ratios
✗I don’t
AVERAGE(Margin %)25 %

Sum or average percentages

✓I do
DIVIDE(Margin, Sales)13 %

DIVIDE measures

5Transformations
✗I don’t
1 Change type2 Remove rows… 38 steps

In Power Query, step by step

✓I do
SELECT … FROM stagingAt the source

At the source, with SQL

00The six questions

Six questionsdecide the model.

Click, or scroll to say “Yes”. The map shows where you are, and you can go back to any point.

There are 16 possible models.This one is yours. Tap a dot to go back.
1/6This decides: Model

Will the question come back?

Or is it a one-off?

2/6This decides: Data quality

Does the data pass data quality?

Primary key · referential integrity · range checks · reconciliation

3/6This decides: Where it lives

Is there a warehouse?

BigQuery, for example

4/6This decides: Schema

How many business processes are there?

Selling, restocking, buying, collecting

5/6This decides: Dimensions

Is everything at the same grain?

Monthly budget vs daily sales

6/6This decides: Measures

Are all columns additive?

Can they be summed without lying?

This branch ends here

Ad-hoc query

One SQL query in BigQuery and done. I don’t build a model for something that won’t come back.

This branch ends here

Back to Review

I don’t build on data that doesn’t pass. I fix it at the source and tell the data owner.

Your pathTap the map to go back
dim_datedim_productdim_storedim_customerdim_monthfct_stockfct_salesSUMDIVIDE · LASTNONBLANK
SELECT store, SUM(amount) AS sales
FROM stg_sales
GROUP BY 1;
storesales
Centro$8.000
Norte$6.200

The answer, and it’s closed. Nothing left to maintain.

What didn’t matchcompletion_pct reaches 340%

What it affectsBudget completion

What I needThe data owner to fix it at the source

See how I review →
  • primary key
  • referential integrity
  • range checks
  • reconciliation

    00The grain

    What one row representsis the first decision.

    From fine you can go up; from coarse you can’t go down.

    What project do you have?

    What sells together only shows inside each receipt.

    Each line carries its product and cost.

    Nobody looks at the receipt, and it loads far faster.

    It’s planned by day and store: that’s the grain.

    The budget only exists monthly. It goes in its own fact table.

    One receipt line✓Use this
    receiptproductamount
    1041Coffee$5.000
    1041Croissant$1.500
    1042Coffee$2.500
    1,200,000 rows a year

    ✓What sells together and each product’s margin

    ✗Slow with years of history

    One day per store✓Use this
    daystoreamount
    3-octCentro$8.500
    3-octNorte$6.200
    4-octCentro$9.100
    11,000 rows a year

    ✓Which day and store sold most, and fast

    ✗What was on each receipt

    One month per store✓Use this
    monthstorebudget
    octCentro$10.000
    octNorte$9.000
    novCentro$11.000
    360 rows a year

    ✓Budget against actual, month by month

    ✗Anything by day

    00The schema

    I use star schema.Everything else, only with a reason.

    I know them all. I use very few, and it’s good to know why.

    ✓What I use
    Exceptions, only with a reason
    For other kinds of systems: I don’t use them
    dim_datedim_productdim_storedim_customerfct_sales
    How common in BI
    ✓I use itBy default

    Star schema

    What it is

    One fact table in the center, with its dimension tables around it.

    Used in

    BI and Power BI. Recommended by Microsoft and Kimball.

    Not used in

    Systems that record operations.

    dim_datedim_productdim_adim_bfct_salesfct_stock
    How common in BI
    ✓I use itWith several processes

    Galaxy schema

    What it is

    Several fact tables sharing dimensions.

    Used in

    Comparing processes: sales against stock or budget.

    Not used in

    Projects with a single process.

    dim_datedim_storedim_productdim_categorydim_familydim_brandfct_sales
    How common in BI
    !ExceptionOnly with a reason

    Snowflake schema

    What it is

    A star schema with dimensions split into more tables.

    Used in

    Large warehouses, or a dimension shared at another grain.

    Not used in

    Power BI, if it’s just to “tidy up”: more joins and slower.

    one_big_tabledate, store, product, qty, amount…
    How common in BI
    !ExceptionExports and one-off analysis

    OBT · One Big Table

    What it is

    Everything in one wide table, no joins.

    Used in

    Direct SQL, data science and exports. Runs well in BigQuery.

    Not used in

    The final delivery in Power BI.

    t1t2t3t4t5t6t7t8t9
    How common in BI
    ×I don’t use itI can read it

    3NF

    What it is

    Normalized tables, no repeated data.

    Used in

    Transactional systems (OLTP): tills, ERP, CRM.

    Not used in

    Reporting directly on it.

    hubhublinksatsatsatsat
    How common in BI
    ×I don’t use itI know it

    Data Vault

    What it is

    Hubs, links and satellites to keep the full history.

    Used in

    Large companies: many sources, audit and a data engineering team.

    Not used in

    Small companies or few sources.

    How common in BI
    ×I don’t use itI know it

    Graph

    What it is

    Nodes and edges, for multi-hop questions.

    Used in

    Fraud, networks and recommendations.

    Not used in

    What two joins can solve.

    00Where it lives

    The same sale,at six stops.

    Data travels from the system to your screen, and changes shape at each stop. I work at the end of the road.

    Another teamSometimes meMy zone
    What the sale looks like hereid_vta: 1041
    fec: "03/10/26"
    prod: " café "
    monto: "$ 5.000"
    What happens here

    Where the sale is born: the till, ERP or CRM. I don’t design it: I learn to read it.

    Shape3NF
    Who does itSoftware engineering
    What the sale looks like hereid_vta: 1041
    fec: "03/10/26"
    prod: " café "
    _loaded_at: 04-oct 02:00
    What happens here

    An exact copy, with its load date. If something goes wrong, you come back here.

    ShapeSin modelo
    Who does itData engineering
    What the sale looks like heresale_id: 1041
    date: 2026-10-03
    product: CAFÉ
    amount: 5000
    What happens here

    It cleans and joins the sources. The Review phase lives here.

    Shape3NF o Data Vault
    Who does itData engineering
    What the sale looks like herefct_sales
    date_key: 1 · store_key: 1
    prod_key: 1
    amount: 5000
    What happens here

    Ready for business questions: fact and dimension tables.

    ShapeStar o galaxy schema
    Who does itAnalytics engineer, or me
    What the sale looks like hereSales =
    SUM(fct_sales[amount])

    Budget % = DIVIDE(…)
    What happens here

    Power BI: relationships, measures, RLS and names that people and AI read the same way.

    ShapeStar schema
    Who does itMe
    What the sale looks like hereVentas$8.00080 % del budget
    What happens here

    What you see: visuals, a title with the conclusion and a decision. Ask an AI, and it answers from the same measures.

    Shape—
    Who does itMe
    00Common mistakes

    Six common mistakesand how they get fixed.

    A messy Excel

    Power Query steps

    SQL in staging

    It’s versioned, runs by itself and every report benefits.
    A percentage

    Stored column

    DIVIDE measure

    In a real model that column weighed 16%. As a measure it weighs zero.
    A filter that doesn’t reach

    Bi-directional

    One-to-many

    Bi-directional opens ambiguous paths and slows everything down.
    Crossing sales and stock

    JOIN between facts

    Conformed dimensions

    Facts meet through their dimensions. Otherwise rows get duplicated.
    The calendar

    Auto date/time

    Marked dim_date

    It creates a hidden table per date. Only 10 of 40 models turned it off.
    The name

    total_monthly_rev

    “Revenue”

    The business reads it, and so does Copilot. Only 5 of 40 models had descriptions.
    00What I get asked

    What I get askedand how I solve it.

    Most frequent first. Tap a request.

    What I get asked most
    Often
    Less often, but it happens

    I measure before touching anything, and cut what weighs.

    DAX StudioPerformance Analyzer

    !Check keys and calculated columns first.

    Two fact tables, with month as a shrunken dimension.

    SQLModel view

    !That a day filter doesn’t touch the budget.

    RLS on the dimension: they log in and see their own, no filtering.

    Modeling › Manage roles

    !It only flows through active relationships.

    A script loads it into BigQuery and validates it.

    PythonSQL en staging

    !If the columns change, it warns by itself.

    Periodic snapshot and a semi-additive measure.

    SQLLASTNONBLANKVALUE

    !Never sum balances across months.

    Role-playing dimension: one dim_date per role.

    Views en SQL

    !Rename columns: “Ship year”.

    SCD Type 2 in the warehouse: each change, one row.

    dbt snapshots

    !It’s solved at the source, with a surrogate key.

    Bridge table between customers and accounts.

    SQLModel view

    !Subtotals don’t add up to the total.

    I tidy the model and add descriptions.

    Home › Prep data for AI

    !No good model, no good answer.

    00How I know I’m done

    Someone else gets it without me.And the totals match.

    Modelling ends when anyone can open the model and get the same number.

    I’m done when…
    1. ✓
      Totals equal the sourceNet sales: 412 in the model and 412 in accounting.
    2. ✓
      One grain per fact tableOne receipt line. The budget, in its own table.
    3. ✓
      Star schema, one-to-manyFilters travel one way.
    4. ✓
      Every measure, with a name and a descriptionPeople read it. So does the AI.
    dim_datedim_productdim_storedim_customerfct_sales

    With the model ready, it gets analysed. Next comes Analyze.