Data modelling in Power BI: considerations for a fast, reliable and governed model

Most Power BI reports that are slow, hard to maintain or show figures that do not add up do not have a visualisation problem: they have a model problem. A good model makes queries fly, helps the business understand what it sees and ensures each indicator means the same thing across the organisation. These are the considerations we apply on every project.

1. Start with the star schema

Ralph Kimball's dimensional modelling is still the benchmark for analytics, and the Power BI engine is optimised for it. It has two pieces:

• Fact tables: the business events (sales, orders, claims, hours) with their numeric measures, ideally additive, and the keys to the dimensions. They answer “how much?”.
• Dimension tables: the descriptive context used to filter and group (customer, product, date, territory). They answer “who, what, where, when and how?”.

The rule of thumb: dimensions filter and group; facts are summarised.

Star or snowflake. In a star, each dimension is a single denormalised table; in a snowflake, dimensions are split into several tables (for example, product → subcategory → category). A snowflake saves some space, but multiplies relationships and complicates the model. In Power BI, the default choice is the star: if you have a snowflake at source, flatten it during the load.

2. Define the grain before anything else

The grain is the level of detail of the fact table: “one row per order line”, “one row per policy per day”. It is the first decision, and the most expensive to change later. A grain that is too aggregated prevents answering detailed questions; a mixed one (rows at different levels in the same table) produces incorrect totals. Every dimension must be consistent with that grain.

3. Normalise just enough

Normalisation (first, second and third normal form) prevents duplication and inconsistencies, and is essential in transactional systems and in the layer where data is curated. For consumption, the criterion changes:

• Always atomic data: no fields containing lists (“600111222, 600333444”) and no addresses in a single text field.
• Separate entities when their attributes do not depend on the fact key (the product name does not belong on the order line).
• Do not separate for the sake of it: a hierarchy such as country → region → city goes in a single dimension, unless each level has many attributes of its own.

4. Take care with keys and data types

• Numeric keys rather than text: they take up less space, compress better and join faster.
• Surrogate keys in dimensions, so you do not depend on source system codes, which may change.
• Beware of auto-increment keys generated in the database: if the table is recreated or loaded into another environment, the same row may receive a different key and break validations and migrations. Deterministic keys generated in the load process are better.
• Dates as an integer YYYYMMDD key to relate to the calendar; check the precision (does it include time? time zone?) and standardise on UTC in international integrations.
• The right type for each piece of data: a date stored as text forces conversions and hurts performance; a postcode is text, not a number.

5. Decide how to store history (SCD)

Operational systems overwrite: if a customer changes address, the previous one disappears. To analyse trends, audit or reconstruct the context of a decision, Kimball defines slowly changing dimensions (SCD):

Type	What it does	When to use it
0	Keeps the original value	Immutable attributes
1	Overwrites	Corrections or attributes whose history is of no interest
2	New row for each change, with start and end dates	Full history; the most common in data warehouses
3	Column with the previous value	Only one change and few fields
4	Current table and history table	Very large dimensions where the current values are almost always queried
6 and 7	Strict variants of type 2 with a version and their own physical key	Several changes on the same day, reprocessing or strict auditing

Two practical tips for type 2: use an “infinite” end date (31/12/9999) instead of nulls, which complicate filters, and add a current-record flag rather than relying on a version number that forces you to calculate maximums in every query. And document in the catalogue which attributes keep history and which do not.

6. Governed model and flattened model

There is no single perfect model, but two layers with different roles:

	Governed model (gold layer)	Flattened model (serving layer)
What for	Auditing, corporate core, data curation	High-usage dashboards, immediacy, lower compute cost
How	Star with well-defined entities, quality rules and semantics	Denormalised tables derived from the governed model, with fewer relationships
Risk	More relationships and poorer performance with large volumes	Duplication and possible misalignment

The recommendation: define the governed model as the single source and automatically generate the flattened entities from it, documented and versioned. That way you gain performance without losing a single version of the truth.

7. From star schema to semantic model

On top of the tables, the Power BI semantic model needs:

1. One-to-many relationships, from the dimension to the fact, with single cross-filter direction. Avoid many-to-many relationships and bidirectional filtering except in justified cases.
2. Explicit measures, in a measures table organised into display folders: one base measure per calculation and derived measures for time intelligence, running totals or ratios.
3. Navigation hierarchies (year → quarter → month; country → region → city).
4. Hidden technical columns: keys, control fields and attributes that add nothing to the analysis.
5. Row-level security (RLS) roles assigned to groups, not to individuals.
6. A standard calendar dimension marked as a date table, with the Auto date/time option turned off.

Calculated columns or measures. A calculated column is materialised row by row and is used to filter, slice or categorise; a measure is calculated in the context of each visual and is the option for aggregations, ratios and indicators. Wherever possible, calculated columns are created during the load (ETL), not in the model.

8. Performance: what makes the biggest difference

• Remove unused columns and reduce cardinality: GUID identifiers, free text and dates with full time are what weigh the most.
• Narrow dimensions and facts with only the keys and measures needed.
• In DAX, use variables, DIVIDE instead of /, COUNTROWS instead of COUNT and SELECTEDVALUE instead of VALUES when you expect a single value.
• In reports, fewer visuals per page and hierarchical slicers only when they add value.

Tools to measure instead of guessing:

Tool	What for
Power BI Desktop Performance analyzer	See which visual or measure slows down a page and extract its DAX query
DAX Studio	Run and profile DAX queries and find bottlenecks
VertiPaq Analyzer	See the size and cardinality of each table and column
Tabular Editor	Bulk editing, scripts and the Best Practice Analyzer with Microsoft's best practice rules
Bravo	Format DAX, analyse the model and generate calendars

9. A single catalogue of indicators

The technical model is not enough if each department calculates “its own” sales. A corporate KPI catalogue rests on a few simple principles:

• Uniqueness: each indicator exists only once, with an identifier.
• Source of truth: the official system and dataset for each one.
• Closed definition: formula, filters, period, grain and permitted breakdowns.
• Accountability: who defines the data and who is accountable for the result.
• Versioning and frequency: controlled changes and a publication calendar.
• Few but good: between 15 and 25 corporate indicators; the rest, by department.

An indicator only exists if it has a complete record and has passed technical, methodological and business validation. We explain this in detail in metadata management and data quality.

10. Standards that save time

• Naming conventions: Dim_ for dimensions, Fact_ for facts and measure names that make business sense, with no duplicates or cryptic acronyms.
• Templates: a corporate .json theme and a .pbit template with base pages, navigation and reusable tooltips. They speed up development, unify the user experience and make it easier to onboard new team members.
• Document and version the model like any other code.

11. Choose the underlying technology wisely

The logical model is the same, but each platform calls for nuances:

Platform	What to bear in mind
Relational databases (SQL Server, Oracle, PostgreSQL)	Indexes, partitioning, execution plans and load windows, because reads and writes compete
Cloud warehouses (Fabric, Snowflake, BigQuery, Redshift)	Cost is driven by compute: denormalise judiciously and distribute data to avoid massive reads
Tabular engines (Power BI, Analysis Services)	Columnar compression: cardinality and the number of columns matter more than the number of rows
Big data and semi-structured data	Normalising JSON in real time can slow down ingestion; it is flattened later, in the consumption layer

12. Architectures: where the model fits

The Power BI model is the last piece in a chain: operational systems, integration layer (formerly ODS, now bronze and silver layers), warehouse or lakehouse with the governed (gold) layer and, finally, the semantic model. Approaches such as Data Mesh (decentralising responsibility by domain) or Data Fabric (a unified governance layer over distributed environments) change who owns each data product, but not the rules of dimensional modelling: facts, dimensions, grain and shared semantics remain the foundation.

Common mistakes

• Importing tables exactly as they are in the source system and relating them in Power BI like a transactional model.
• One fact table with everything: measures from different processes and mixed grains in the same table.
• Bidirectional relationships “to make it work”, which create ambiguity and slow things down.
• Calculating in DAX what should come pre-calculated from the load.
• Using the automatic date hierarchy instead of a calendar dimension.
• Duplicating the same measure under different names in different reports.

Checklist before publishing a model

• [ ] The grain of each fact table is documented.
• [ ] Dimensions are denormalised and have a surrogate key.
• [ ] There is a calendar dimension marked as a date table and Auto date/time is turned off.
• [ ] There are no unjustified many-to-many or bidirectional relationships.
• [ ] Technical columns are hidden and unused ones removed.
• [ ] Measures are in a measures table, with display folders and formatting.
• [ ] Names follow the agreed naming conventions.
• [ ] Row-level security is defined and assigned to groups.
• [ ] The Best Practice Analyzer shows no relevant violations.
• [ ] Indicators correspond to records in the KPI catalogue.
• [ ] The model is documented and versioned.

At Bidatia we design and optimise semantic models in Power BI and Microsoft Fabric, from the governed model to the dashboard, and we train teams to maintain them.