Schemas and Data Modelling in Power BI

Introduction

Data modelling is the process of structuring data into tables and defining relationships between them so it can be efficiently analyzed and produce accurate insights.A schema refers to the logical structure that organizes how these tables are arranged and connected within a data model.
In this article, we will be exploring data modelling and schemas in PowerBI and understand how they help build efficient and reliable reports.

Understanding Tables in a Data Model

When data is loaded into Power BI, it doesn’t just sit there as one big sheet. It’s organized into tables, and each table has a specific role in the model.
Tables hold different types of information. Some store measurable values like sales and revenue, while others store descriptive information like customer names, product categories, or dates. Understanding the purpose of each table is the first step to building a strong data model.

Fact Tables vs Dimension Tables

This is one of the most important concepts in data modelling.

Fact Tables

Fact tables contain the numbers you want to analyze. These are measurable, numeric values.

Examples:

• Sales Amount
• Quantity Sold
• Profit
• Number of Orders Dimension Tables

Dimension tables provide descriptions about the facts.

They answer questions like:

• Who? → Customer table
• What? → Product table
• When? → Date table
• Where? → Region table

What is a schema?

A schema is the structure of how tables are arranged and related in a database or data model.
In Power BI, the most important schema you’ll hear about is the:-

• Star Schema One central fact table Multiple surrounding dimension tables Looks like a star when viewed in the model

• Snowflakes Schema - This is like a star schema, but dimension tables are split into more tables

Schema Relationships

PowerBI model view provides a way of establishing relationships between tables so that data can flow correctly across the model.
Relationships connect tables using keys.

Types of relationships

• One-to-One (1:1)
  Each value in Table A matches only one value in Table B.

• One-to-Many (1:*)
  This is the most common type.
  One value in a dimension table connects to many rows in a fact table.
  Example:
  One Product → Many Sales

• Many-to-One (*:1)
  This is the same as one-to-many but viewed from the other side.

• Many-to-Many (:)
  Both tables contain duplicate values. This can work but should be used carefully because it may cause ambiguous filtering.

Common Data Modeling Mistakes

• Using one big flat table for everything
• Creating too many many-to-many relationships
• Not using a proper Date table
• Leaving unused columns in the model

Conclusion

Data modelling is the foundation of every good Power BI report. Visuals may be what users see, but the structure behind the scenes is what makes everything work correctly.
By understanding schemas, fact and dimension tables, and relationships, you build reports that are faster, cleaner, and easier to maintain