You have built a beautiful dashboard with stunning visuals and a coordinated color scheme but your reports are slow, your numbers do not add up and users are losing confidence and trust. The culprit? A poorly designed data model. In Power BI, your visuals are only as good as the relationships and schemas behind them.
What is Power BI?
While Power BI is often classified as a visualization tool, the reality is that Power BI is not a single tool but rather part of a collection of interrelated tools and services that form a complete business intelligence ecosystem. (Deckler, 2025). Business Intelligence is leveraging data to make better decisions.
Most organizations may not store all their information in a single table. Client details may exist in one database, sales transactions in another and inventory detail in a third. Before meaningful reports and dashboards can be created, scattered data must be organized and connected. Power BI achieves this through data modeling, schemas, joins and relationships. Together, these concepts form the foundation of efficient data analysis by ensuring data is accurate, consistent and easy to explore.
Understanding data modelling
What is data modelling?
Data modelling can be explained as the process of organizing data into a logical structure to allow better querying and analysis all while reducing redundancy and maintaining integrity. It involves identifying tables, defining relationships, selecting appropriate schemas and ensuring data is structured and ready for reporting.
Think of it like moving and settling in in a new kitchen:
- Raw data: Utensils, supplies and kitchen utilities are clattered and mixed in moving boxes.
- Data modelling: Arranging utilities in labeled containers and kitchen cupboards, grouping similar items together and creating a system to find things quickly.
- Power BI Report: The meal you cook in your now organized kitchen.
What data modeling does for you.
- Creates structure: Turns messy data into logical tables.
- Establishes relationships: Defines how tables connect to one another.
- Enables calculations: Makes DAX formulas work correctly.
- Improves performance: Reports run faster.
- Ensures accuracy: Consistent numbers that everyone can trust.
Without proper data modeling, you are likely to experience slow reports, memory crashes where reports struggle to refresh or maintenance nightmare where every change breaks a logic somewhere else.
Core components of data modeling
1. Tables
Tables are the containers for your data. Each table holds related information and it is therefore recommended that each table should represent a single concept, that is, avoid mixing a table that has client details with a table that has inventory details.
2. Columns and Data types.
Columns are the attributes/ fields within a table. Columns can either have textual data, numeric data or date/time data.
Power BI recognizes different data types like:
Number types: Decimal number, Fixed decimal, Whole number etc.
Text type.
Date types: Date, Date/Time, Time
Other types: Binary.
3. Keys
Keys are special columns that create connections between tables.
- Primary key (PK): This is a unique identifier for each row. A table usually has one primary key.
- Foreign key (FK): References a primary key in another table and can have duplicates. They create the connection.
Now while tables give us structure, relationships give us context. An orders table tells us what was sold and a clients table tells us who bought it. Only when these tables are connected through relationships can we truly understand who our best customers are what products they love and how our business is growing over time.
Relationships
A relationship is a connection between two tables that tells Power BI how they relate to each other. Relationships allow data to flow between tables enabling an analyst to analyze a business as a connected whole rather than isolated pieces.
Cardinality describes how many rows in one table can link to rows in another table.
Types of Cardinality
i. One to One (1:1)
A row in Table A connects to at most one row in Table B e.g. Each person can have only one passport and each passport is assigned to only one person.
Diagram showing a one-to-one relationship from Database.Guide is used for illustrative purposes. Source: What is a One-To-One Relationship?
ii. One to Many(1:*)
A single row in Table A can connect to multiple rows in Table B but a row in Table B only connects to one row in Table A.
iii. Many to Many(:)
Multiple rows in Table A relate to multiple rows in Table B e.g. A book can have multiple authors and an author can write many books.
Diagram showing a many to many relationship from Database.Guide is used for illustrative purposes. Source: What is a Many-To-Many Relationship?
Power BI can only use one relationship at a time by default.
- Active relationship: The default connection Power BI uses automatically(Shown as a solid line).
- Inactive relationship: A connection that exists but is turned off by default(Shown as a dashed line).
Think of active vs inactive relationships like the default route on your Google Maps versus the alternative routes you can take. The map always uses the default route (active relationship) unless you specifically tell it to use an alternative (inactive relationship). Both routes exist but only one is used automatically.
While relationships connect tables in the model joins merge tables in Power Query.
Joins
Joins combine data from two or more tables into a single table during data preparation.
Common type of joins.
- Left Outer: Keep all rows from the left table and matching ones from right.
- Right Outer: Keep all rows from the right table and matching ones from left.
- Full Outer: Keep all rows from both.
- Inner: Returns only matching rows in both.
- Left Anti: Returns only rows from the left table that have no match in the right table.
- Right Anti: Returns only rows from the right table that have no match in the left table.
Why Joins are important.
Helps in reducing duplication.
Helps to save storage space.
Helps to improve data consistency.
Helps make databases easier to maintain.
Joins vs Relationships
When to use joins
When:
You need to add columns to an existing table.
You are bringing in lookup data.
You are flattening a schema.
You need one combined table for a specific purpose.
When to use relationships
When:
You want to analyze data across multiple tables.
You need dynamic filtering across tables : Slicers and filters to affect related tables.
You're building reports and dashboards.
You are creating a star schema.
You want to avoid duplicating data e.g. when you need to use the same dimension across multiple facts.
Now that we understand how to combine tables using joins and how to connect them using relationships, a bigger question emerges:
How should we organize all these tables?
Think of it this way: Joins are like connecting individual LEGO bricks (table to table). Relationships are like the instructions that tell you which bricks connect. But a schema is the overall blueprint that shows you exactly how every piece fits together to create something meaningful.
Schema
A schema is a blueprint of your data model. It defines:
What tables you have.
What columns each table contains.
How tables relate to each other.
What rules govern the data.
1. Star schema
A star schema is a data modeling design where one central fact table is surrounded by multiple dimension tables like a star.
The Anatomy of a star schema
Fact table
Usually at the center and surrounded by dimension tables.
Contains numbers you want to analyze (measures).
Contains foreign keys linking to dimensions.
Dimension tables
Usually the lookup or reference tables.
Contain descriptive attributes.
Provide context to the fact table.
Why a star schema is recommended in Power BI
1. Better Performance
When you query a flat table, Power BI has to:
Scan through every column that might be needed, apply filters to text values which is computationally expensive, evaluate complex filtering conditions and process large amounts of data just to find what you need.
With a star schema:
The engine knows exactly which columns are in which tables.
It can apply filters to dimension tables first and scan the fact table for the filtered subset.
The query optimizer can use the relationship structure to build efficient execution plans and the result is that queries can run in milliseconds instead of seconds or minutes.
2. Simpler data model
A star schema is easy to understand because all dimension tables connect directly to the central fact table. This straightforward structure makes it easier for both developers and report users to navigate the model.
3. Easier report creation
Power BI builds visuals by combining measures from the fact table with descriptive attributes from the dimension tables.
Because all these tables connect directly to the fact table, creating charts, slicers and dashboards becomes much simpler.
In Conclusion:
A well designed data model is the difference between a dashboard that delights and one that disappoints. It transforms scattered, disconnected data into a trusted source of truth that drives better decisions. Whether you are connecting tables through relationships, combining them with joins or structuring them into a star schema, every decision you make in the modeling phase translates through to every visual, every calculation and every user interaction.
In the end, Power BI is only as powerful as the data model it sits on. Build it well and everything else becomes simpler. Build it poorly and no amount of visual polish can save it. Therefore, before you build your next dashboard, map out your tables, identify your keys, define your relationships and choose your schema. Your future self and your users will thank you.
Top comments (1)
This is impressive. Good work, especially in the way you used screenshots to demonstrate the process.