Schemas and Data Modelling in Power BI: A Beginner-Friendly Step-by-Step Guide

Source: Dev.to

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)

What Is Data Modelling in Power BI?

Data modelling is the process of:

• Organising tables
• Defining relationships between tables
• Structuring data so Power BI can analyse 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

Fact Tables and Dimension Tables

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.

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

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

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 ❄️

Star Schema (Highly Recommended)

What Is a Star Schema?

A star schema has:

• One central fact table
• Multiple dimension tables directly connected to it

It looks like a star shape.

Why Star Schema Is Best in Power BI

• ✔ Faster performance
• ✔ Simpler DAX formulas
• ✔ Accurate aggregations
• ✔ Easy to understand

Power BI’s engine (VertiPaq) is optimised for star schemas.

Step‑by‑Step: Creating a Star Schema in Power BI

Step 1 – Load Your Data

1. Open Power BI Desktop.
2. Click the Home tab → Get Data.
3. Choose your source (Excel, CSV, SQL, etc.).
4. Click Load.

Step 2 – Check Tables in Data View

1. Click the Data icon (table) on the left panel.
2. Open each table and identify:
   • Fact table (contains numeric columns)
   • Dimension tables (contain descriptive columns)

Step 3 – Go to Model View

• Click the Model icon (diagram) on the left.
• You will see each table displayed as a box.

Step 4 – Create Relationships

1. Drag ProductID from Product_Dim.
2. Drop it onto ProductID in Sales_Fact.
3. In the Create Relationship window, set:
   • Cardinality: One to Many (1:)*
   • Cross filter direction: Single
   • Ensure the dimension table is on the 1 side.
4. Click OK.

Repeat the same process for the other dimensions (Date, Customer, Location).

Snowflake Schema

What Is a Snowflake Schema?

A snowflake schema is a variation where dimension tables are normalised and connected to other dimension tables, forming a “snowflake” pattern.

Why … (section truncated in the original source)

Note: The original content ends abruptly after “Why S”. The remainder of this section should be added to complete the discussion of snowflake schemas.

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.

Understanding Relationships in Power BI

Cardinality

• One‑to‑Many (1:*) – most common and recommended
• Many‑to‑Many – avoid unless absolutely necessary

Filter Direction

• Single Direction – recommended
• Both Directions – can cause wrong totals

Rule: Filters should flow from Dimension → Fact.

Why Good Data Modelling Is Critical

Performance

Accuracy

Poor modelling can cause:

• Double counting
• Incorrect totals
• Misleading reports

Correct modelling ensures:

• Accurate aggregations
• Reliable business decisions

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.

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.