Home › Power BI data model
What should a Power BI data model look like?
Like a star: one fact table with the events in the middle, dimension tables with the context around it, and relationships running from dimension to fact in a single direction. It is faster, but more importantly the outcome becomes predictable. One wide table with everything in it feels like clarity and behaves like concrete.
The star, briefly
In the middle sits what happened: a sales line, a journal entry, an order, a timesheet record. Around it sits the context: which customer, which product, which date, which department. The facts grow, the context barely does.
The four choices that matter
1. Separate facts from context
Not because the textbook says so, but because otherwise every new subject starts from scratch.
2. Relationships in one direction
From dimension to fact, single. Bidirectional looks easier and makes the answer depend on filter order.
3. A dedicated date table
Contiguous dates, marked as a date table, with your fiscal year and your week numbering.
4. Logic in the right place
Whatever is always true belongs in the source or the load step. Whatever depends on what the user clicks belongs in a measure. See where your logic belongs.
How to spot a model that will bite
- A table with more than fifty columns, half of which nobody uses
- Dates stored as text, or timestamps down to the second in a fact table
- Measures referring to calculated columns referring to other calculated columns
- Two tables describing the same subject because merging looked hard
- A relationship switched off because it produced duplicate rows
Sonny was able to set up the data models quickly and technically and build the reports. The project was completed to full satisfaction.
Half an hour is enough to know whether we fit
No slide deck and no quote at the end. We walk through your sources, your definitions and your biggest frustration.
Book half an hourFrequently asked questions
What should a Power BI data model look like?
Like a star: one fact table with the events in the middle, dimension tables with the context around it, and relationships running from dimension to fact in a single direction. That is not only faster, it makes the outcome predictable, and that matters more.
What is wrong with one wide table?
It feels like clarity and behaves like concrete. The storage engine compresses it less well, filters get more expensive, and the moment a second subject arrives you have to flatten everything again. On top of that the meaning of a column now lives in dozens of places.
What is wrong with bidirectional relationships?
They are convenient while building and expensive at runtime, but the real objection is that the outcome becomes unpredictable. With several bidirectional paths the answer depends on the order in which the engine filters. A wrong number is worse than a slow one.
Do you really need a separate date table?
Yes, always, and it is the cheapest improvement there is. Without your own date table you cannot make a proper comparison with last year, cannot handle a fiscal year that differs from the calendar year, and cannot count weeks the way your business counts them.
Can you rebuild an existing model without redoing everything?
Usually yes. The report pages largely stay; the model underneath is rebuilt and the measures rewritten. Users see the same screen, only fast and with numbers that hold.