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.
Example: Lets say we have a sales table as a fact table, the values it could contain are
- Sales Amount
- Quantity Sold
- Profit
- Number of Orders
Dimension Tables
Dimension tables provide descriptive information about the data in the facts table.
Example: For the sales fact table, dimension tables could be
- Customer table- answers who made a transaction
- Product table - contains information of the products availble
- Date table - when the sale happened
- store table - which store did the sale happen
What is a schema?
A schema is the structure of how tables are arranged and related in a database or data model.
Components of a schema
- Tables – Where data is stored
- Columns (Fields) – The attributes inside tables
- Data Types – Define what kind of data each column holds (text, number, date, etc.)
- Relationships – How tables connect to each other
-
Keys
- Primary keys (unique identifiers)
- Foreign keys (used to link tables)
Types of schemas
1.Star Schema - has one central fact table surrounded by multiple dimension tables
Looks like a star when viewed in the model
2.Snowflakes Schema - This is like a star schema, but dimension tables are split into more sub dimensions
Schema Relationships
PowerBI model view provides a way of establishing relationships between tables so that data can flow correctly across the model.
Relationships explains how tables are connected logically to one another
The connection is done using keys, either:
- Primary key: unique identifier
- Foreign key: references a primary key in another table
Types of relationships
1.One-to-One (1:1)
Each record in Table A matches only one value in Table B and vice versa
Example: A Person can have one National ID, and each National ID belongs to only one Person.
2.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:
- Sales table (Fact table)
- Product table, Customer table and date table are all dimension tables
One Product → can appear in many sales transactions
One customer -> can make many purchases
One date-> Many sales transactions can happen on the same date.
3.Many-to-One (*:1)
This is the same as one-to-many but viewed from the other side.
4.Many-to-Many (:)
Both tables contain duplicate values. This can work but should be used carefully because it may cause ambiguous filtering.
Example:
- Students table
- StudentCourses table
How it works:
One student → many records in StudentCourses
One course → many records in StudentCourses
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
That's all for now, happy modelling
Top comments (0)