Data modeling in Power BI involves creating a semantic model (also called a dataset or data model) that connects tables through relationships, enabling analysis, calculations, and visualizations. A semantic model combines tables from Power Query, column metadata, and defined relationships. These relationships allow filters and aggregations to propagate correctly across tables, turning raw data into actionable insights.
Data modeling in power BI emphasises thinking in terms of fact tables and dimension tables, drawing from dimensional modeling principles.
Fact and Dimension Tables
Fact tables contain numeric data about business processes (e.g., sales, inventory, or hours worked). Each row represents an event, with columns for measures like quantities, amounts, or rates. Example: A "Hours" table with columns for TotalHoursBilled, HourlyRate, and HourlyCost.
Dimension tables store descriptive attributes about entities (people, places, things). They provide context for facts, with rows for each unique entity and columns like names, dates, locations, or categories. Examples: A "People" table (employee details) or "Calendar" table (dates, months, years).
It is recommended that we identify facts and dimensions early in design, using a bus matrix (a Kimball technique) to map which dimensions connect to which facts. This creates an organized overview of grain (detail level), key measures, and relationships.
Good modeling starts by identifying facts (what to measure) and dimensions (how to analyze them).
Relationships
Relationships link tables so calculations work across them. In Power BI's Model view, create them by dragging columns or using Manage relationships.
Key concepts of relationships
1.Cardinality:
One-to-many (or many-to-one): Preferred; unique values on the "one" side (often dimensions) link to multiple rows on the "many" side (facts).
One-to-one: Rare, for unique matches.
Many-to-many: Allowed but avoided due to complexity and resource use.
- Cross-filter direction
Single: Filtering flows one way (typically dimension → fact).
Both: Bidirectional; use sparingly to avoid ambiguity.
- Active vs. inactive: Only one active path between tables; inactive (dashed lines) for alternatives in calculations.
Power BI often autodetects relationships based on column names and data.
Schemas: Star and Snowflake
Star Schemas are the output of a practice known as Dimensional Modelling. The objective is to design data models that are optimised for reporting and analysis.
It's not as scary as it sounds! Dimensional modelling seeks to model the business domain and capture semantics to structure the data in way that reflects the real world.
A Star Schema (like any schema for tabular data) is composed of tables, which are composed of columns. Relationships exist between the tables.
In a Star Schema there are two types of table:
Dimension tables capture key attributes of a record in the fact table - allowing you to answer questions such as:
When? exploring points in time, or grouping data over time periods such as days, months, years.
Who? by representing organisations, departments or even individual people.
What? identifying specific products or services.
Where? understanding relationships to physical and virtual locations.
Fact tables store the numerical data / transactions that you want to aggregate, group, filter, slice and dice (using the dimensions above) to deliver a specific actionable insight.
The simplest Star Schema has one fact table and a small number of dimension tables. Each of the dimension tables has a "one to many" relationship with the fact table. When arranged on an entity relationship diagram, this leads to a star-like arrangement, hence the name Star Schema! This is illustrated below:
Power BI and Star Schemas?
Power BI is optimised to work with Star Schemas. What it boils down to is:
- Lower total cost of ownership - in three ways:
Data models that are simple. They have a smaller number of tables and relationships. Therefore, they are easier to understand and evolve.
Memory requirements are minimised - the data structures in a star schema are well suited to the column-store approach used by the VertiPaq engine at the heart of Power BI. This means that is able to compress the data minimising the memory footprint.
Compute requirements are minimised - a star schema is optimised for analytical workloads, queries over the data involve very few joins across tables, therefore the amount of compute required to return analytics is reduced.
Better user experience - better performance when querying and interacting with the data.
Maximising value from data - by unlocking all of the features in Power BI such as DAX measures or interactive features in visuals.
The data model that underpins every Power BI report implements the Star Schema as tables and relationships. DAX based measures are then layered on top of this data model to generate the analytics that are surfaced in the report.
This concept is illustrated below:
Importance of Good Data Modelling
Good data modelling is critical to the success of any Power BI solution.
- Performance
- Well-designed models, especially star schemas, allow Power BI to efficiently compress data and execute queries quickly, even with millions of rows
- Accuracy
- Clear relationships ensure that filters, slicers, and aggregations behave as expected. This prevents common issues such as double counting or incorrect totals.
- Usability
- A clean model makes it easier for report authors and business users to understand the data and build visuals confidently, supporting true self-service analytics.
- Maintainability
- Simple models are easier to extend, troubleshoot, and adapt as business requirements change.
Conclusion
In Power BI, the data model is the foundation of all reporting and analysis. Separating data into fact and dimension tables, using a star schema, defining clear relationships, and avoiding unnecessary complexity are essential best practices. Investing time in good data modelling results in faster reports, accurate insights, and more reliable decision-making across the organization.
Top comments (0)