1. Introduction
Data modelling is one of the most important skills when working with Power BI. Even if your data is correct, poor data modelling can lead to slow reports, incorrect numbers, and confusing visuals.
This article explains, from scratch, how schemas and data modelling work in Power BI. It is written so that a beginner can follow step by step, click by click, and build a correct model.
You will learn:
- What data modelling means in Power BI
- Fact tables vs Dimension tables
- Star schema and Snowflake schema
- Relationships (one-to-many, cardinality, filter direction)
- Why good modelling improves performance and accuracy
- How to create a clean data model in Power BI (step-by-step)
2. What Is Data Modelling in Power BI?
Data modelling is the process of:
- Organizing tables
- Defining relationships between tables
- Structuring data so Power BI can analyze it efficiently
Think of data modelling like designing the foundation of a house. If the foundation is weak, everything built on top will fail.
Why is Good Data Modelling Critical?
• Performance: Well-designed models load faster and respond to queries more quickly
• Accuracy: Proper relationships ensure your calculations and aggregations are correct
• Scalability: Good models can handle growing data volumes without performance degradation
• Maintainability: Clean models are easier to update and troubleshoot
• User Experience: Fast, accurate reports lead to better business decisions
In Power BI, data modelling is done mainly in:
- Model View (Relationships diagram)
- Data View (Table structure)
3. Fact Tables and Dimension Tables
3.1 Fact Tables
Fact tables are the central tables in your data model that contain quantitative data (measures) about your business processes. Think of them as "what happened" tables.
A fact table stores measurable business data (numbers).
Examples of facts:
- Sales amount
- Quantity sold
- Profit
- Yield
Characteristics of a fact table:
- Contain numerical measures (sales amount, quantity, cost, profit)
- Usually have many rows (can contain millions of records)
- Contain foreign keys that link to dimension tables
- Represent business events or transactions
- Are typically narrow (fewer columns) but very long (many rows)
Example: Sales_Fact table
A Sales fact table might contain:
• SaleID (Primary Key)
• DateKey (Foreign Key to Date dimension)
• ProductKey (Foreign Key to Product dimension)
• CustomerKey (Foreign Key to Customer dimension)
• StoreKey (Foreign Key to Store dimension)
• Quantity (Measure)
• SalesAmount (Measure)
• Cost (Measure)
• Profit (Measure)
3.2 Dimension Tables
A dimension table stores descriptive information (context).
Examples of dimensions:
- Date
- Product
- Customer
- Location
Characteristics of dimension tables:
- Contain text or categories
- Smaller than fact tables
- Used for filtering and grouping
Example: Product_Dimension table
4. What Is a Schema?
A schema is the structure that defines how fact and dimension tables are connected.
In Power BI, the two most common schemas are:
- Star Schema ⭐ (recommended)
- Snowflake Schema ❄️
5. Star Schema (Highly Recommended)
5.1 What Is a Star Schema?
A star schema has:
- One central fact table
- Multiple-dimensional tables directly connected to it
It looks like a star shape.
5.2 Why Star Schema Is Best in Power BI
✔ Faster performance
✔ Simpler DAX formulas
✔ Accurate aggregations
✔ Easy to understand
Power BI’s engine (VertiPaq) is optimized for star schemas.
5.3 Step-by-Step: Creating a Star Schema in Power BI
Step 1: Load Your Data
- Open Power BI Desktop
- Click Home tab
- Click Get Data
- Choose your source (Excel, CSV, SQL, etc.)
- Click Load
Step 2: Check Tables in Data View
- On the left panel, click the Data icon (table icon)
- Click each table
- Identify:
- Fact table (numeric columns)
- Dimension tables (descriptive columns)
Step 3: Go to Model View
- Click the Model icon (diagram icon) on the left
- You will see tables as boxes
Step 4: Create Relationships
- Drag ProductID from
Product_Dim - Drop it onto ProductID in
Sales_Fact - Power BI opens the Create Relationship window
Set:
- Cardinality: One to Many (1:*)
- Cross filter direction: Single
- Make sure the dimension is on the 1 side
Click OK
Repeat for:
- Date
- Customer
- Location
6. Snowflake Schema
6.1 What Is a Snowflake Schema?
A snowflake schema is when:
- Dimension tables are connected to other dimension tables
6.2 Why Snowflake Is Less Ideal in Power BI
❌ Slower performance
❌ More complex relationships
❌ Harder DAX formulas
Power BI works better when dimensions are denormalized (flattened).
📌 Best Practice: Convert snowflake schemas into star schemas where possible.
7. Understanding Relationships in Power BI
7.1 Cardinality
- One-to-Many (1:*) → Most common and recommended
- Many-to-Many → Avoid unless absolutely necessary
7.2 Filter Direction
- Single Direction (recommended)
- Both Directions (can cause wrong totals)
📌 Rule: Filters should flow from Dimension → Fact
8. Why Good Data Modelling Is Critical
8.1 Performance
Bad model:
- Slow visuals
- High memory usage
Good model:
- Fast reports
- Efficient calculations
8.2 Accuracy
Poor modelling can cause:
- Double counting
- Incorrect totals
- Misleading reports
Correct modelling ensures:
- Correct aggregations
- Reliable business decisions
9. Common Beginner Mistakes (Avoid These)
❌ Multiple fact tables directly connected
❌ Many-to-many relationships everywhere
❌ Snowflake schemas without need
❌ Using fact tables as filters
✔ Always design with a star schema mindset
10. Conclusion
Data modelling is not optional in Power BI—it is essential.
To summarize:
- Use fact tables for numbers
- Use dimension tables for descriptions
- Build star schemas
- Use one-to-many relationships
- Keep filter direction single
When your data model is clean, everything else becomes easier—DAX, visuals, and performance.
Top comments (0)