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.
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.
Grain
✗The monthly budget repeats on every row.
✓Each row is one receipt line. The budget leaves the table.
Fact table
✗Numbers and descriptions, all together.
✓What happened stays here: quantity and amount.
Dimension tables
✗“Downtown” and “Coffee” repeat across thousands of rows.
✓Each value, once: when, where, what and who.
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.
Measures
✗The 20% was stored in the table.
✓The measure computes with what you filter: $8,000 ÷ $10,000 = 80%.
Everyone looks at the flowers.The model is the trunk.
Same data, same dashboard. The only thing that changes is the model.
What I don’t do,and what I do.
From the visuals, then patch
From the decision
| 3-oct | $8.500 |
| oct | $40.000 |
Mix grains
| 1041 | Coffee | $5.000 |
| 1041 | Croissant | $1.500 |
One line, and I test it
Data Vault for one Excel
Star schema
Sum or average percentages
DIVIDE measures
In Power Query, step by step
At the source, with SQL
Six questionsdecide the model.
Click, or scroll to say “Yes”. The map shows where you are, and you can go back to any point.
Will the question come back?
Or is it a one-off?
Does the data pass data quality?
Primary key · referential integrity · range checks · reconciliation
Is there a warehouse?
BigQuery, for example
How many business processes are there?
Selling, restocking, buying, collecting
Is everything at the same grain?
Monthly budget vs daily sales
Are all columns additive?
Can they be summed without lying?
Ad-hoc query
One SQL query in BigQuery and done. I don’t build a model for something that won’t come back.
Back to Review
I don’t build on data that doesn’t pass. I fix it at the source and tell the data owner.
- The branch ends here: the other questions aren’t needed.
SELECT store, SUM(amount) AS sales FROM stg_sales GROUP BY 1;
| store | sales |
|---|---|
| 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
- primary key
- referential integrity
- range checks
- reconciliation
What one row representsis the first decision.
From fine you can go up; from coarse you can’t go down.
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.
| receipt | product | amount |
|---|---|---|
| 1041 | Coffee | $5.000 |
| 1041 | Croissant | $1.500 |
| 1042 | Coffee | $2.500 |
✓What sells together and each product’s margin
✗Slow with years of history
| day | store | amount |
|---|---|---|
| 3-oct | Centro | $8.500 |
| 3-oct | Norte | $6.200 |
| 4-oct | Centro | $9.100 |
✓Which day and store sold most, and fast
✗What was on each receipt
| month | store | budget |
|---|---|---|
| oct | Centro | $10.000 |
| oct | Norte | $9.000 |
| nov | Centro | $11.000 |
✓Budget against actual, month by month
✗Anything by day
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.
Star schema
One fact table in the center, with its dimension tables around it.
BI and Power BI. Recommended by Microsoft and Kimball.
Systems that record operations.
Galaxy schema
Several fact tables sharing dimensions.
Comparing processes: sales against stock or budget.
Projects with a single process.
Snowflake schema
A star schema with dimensions split into more tables.
Large warehouses, or a dimension shared at another grain.
Power BI, if it’s just to “tidy up”: more joins and slower.
OBT · One Big Table
Everything in one wide table, no joins.
Direct SQL, data science and exports. Runs well in BigQuery.
The final delivery in Power BI.
3NF
Normalized tables, no repeated data.
Transactional systems (OLTP): tills, ERP, CRM.
Reporting directly on it.
Data Vault
Hubs, links and satellites to keep the full history.
Large companies: many sources, audit and a data engineering team.
Small companies or few sources.
Graph
Nodes and edges, for multi-hop questions.
Fraud, networks and recommendations.
What two joins can solve.
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.
id_vta: 1041
fec: "03/10/26"
prod: " café "
monto: "$ 5.000"Where the sale is born: the till, ERP or CRM. I don’t design it: I learn to read it.
id_vta: 1041
fec: "03/10/26"
prod: " café "
_loaded_at: 04-oct 02:00An exact copy, with its load date. If something goes wrong, you come back here.
sale_id: 1041
date: 2026-10-03
product: CAFÉ
amount: 5000It cleans and joins the sources. The Review phase lives here.
fct_sales
date_key: 1 · store_key: 1
prod_key: 1
amount: 5000Ready for business questions: fact and dimension tables.
Sales =
SUM(fct_sales[amount])
Budget % = DIVIDE(…)Power BI: relationships, measures, RLS and names that people and AI read the same way.
What you see: visuals, a title with the conclusion and a decision. Ask an AI, and it answers from the same measures.
Six common mistakesand how they get fixed.
Power Query steps
SQL in staging
Stored column
DIVIDE measure
Bi-directional
One-to-many
JOIN between facts
Conformed dimensions
Auto date/time
Marked dim_date
total_monthly_rev
“Revenue”
What I get askedand how I solve it.
Most frequent first. Tap a request.
I measure before touching anything, and cut what weighs.
!Check keys and calculated columns first.
Two fact tables, with month as a shrunken dimension.
!That a day filter doesn’t touch the budget.
RLS on the dimension: they log in and see their own, no filtering.
!It only flows through active relationships.
A script loads it into BigQuery and validates it.
!If the columns change, it warns by itself.
Periodic snapshot and a semi-additive measure.
!Never sum balances across months.
Role-playing dimension: one dim_date per role.
!Rename columns: “Ship year”.
SCD Type 2 in the warehouse: each change, one row.
!It’s solved at the source, with a surrogate key.
Bridge table between customers and accounts.
!Subtotals don’t add up to the total.
I tidy the model and add descriptions.
!No good model, no good answer.
Someone else gets it without me.And the totals match.
Modelling ends when anyone can open the model and get the same number.
- ✓Totals equal the sourceNet sales: 412 in the model and 412 in accounting.
- ✓One grain per fact tableOne receipt line. The budget, in its own table.
- ✓Star schema, one-to-manyFilters travel one way.
- ✓Every measure, with a name and a descriptionPeople read it. So does the AI.
With the model ready, it gets analysed. Next comes Analyze.





