📐 Data Modelling and Schemas
📐 The Blueprint: Data Modelling
After you have cleaned your data in Power Query, it is loaded into the Power BI Data Model.
What is a Data Model? A Data Model is simply a collection of tables and the relationships (connections) between them. A good data model ensures your reports run incredibly fast and your DAX calculations return accurate numbers.
Think of a poorly modelled database like a giant pile of unsorted books in the middle of a room (a Flat File). It takes forever to find anything. A good Data Model is like a structured Library. You have a central desk that tracks every book checked out (Fact Table), and surrounding shelves that neatly categorize the books by Author, Genre, and Year (Dimension Tables).
🛒 Fact vs Dimension Tables
To build a good model, you must split your tables into two categories:
1. Fact Tables (The "What Happened")
Fact tables record business events, transactions, or observations. They are usually massive and grow every single day.
- Examples: Sales transactions, Website clicks, Daily temperature readings.
- Contents: They contain numerical values (Quantity, Price) and Foreign Keys (e.g.,
CustomerID = 101,ProductID = 55) that link to other tables.
2. Dimension Tables (The "Who, What, Where, When")
Dimension tables describe the business entities. They are usually small and don't change very often.
- Examples: Customers, Products, Store Locations, Calendar/Dates.
- Contents: They contain descriptive text (Customer Name, Product Category) and a unique Primary Key (e.g.,
CustomerID = 101). You use these tables to filter and group the numbers in your Fact table.
| Feature | Fact Table | Dimension Table |
|---|---|---|
| Purpose | Stores events and numbers | Stores descriptive details |
| Size | Massive (Millions of rows) | Small (Hundreds/Thousands of rows) |
| Action | Summarized / Aggregated | Used for Filtering / Slicing |
⭐ Schemas: How to Arrange Your Tables
When you connect these tables together in the Model View, the shape they form is called a Schema.
1. Flat Schema (Denormalized)
Everything is crammed into one giant table. (Like an Excel spreadsheet).
- Pros: Very easy for a human to read.
- Cons: Terrible performance. If a customer's name is repeated 10,000 times for 10,000 purchases, it wastes massive amounts of memory.
2. Star Schema
The gold standard for Power BI! You place your massive Fact table right in the center, and you surround it with your descriptive Dimension tables. When you draw the relationship lines, it literally looks like a Star.
- Pros: Optimized specifically for Power BI's engine. Reports load instantly.
3. Snowflake Schema
An extension of the Star schema. Some Dimension tables branch out into even smaller sub-dimension tables (e.g., a Product table connects to a separate Product Category table). It looks like a snowflake.
- Pros: Saves a tiny bit of storage space by avoiding repeated text.
- Cons: Forces Power BI to do extra "hops" to filter data, making reports slower.
🏋️ Practice Drill
Let's explore the Model View!
Task:
// Try answering these:
1. In Power BI Desktop, click the **Model View** icon (the third icon on the far-left sidebar that looks like boxes connected by lines).
2. If you loaded the appended table from the previous lesson, you will see a box representing that table.
3. If you have multiple tables, you can click and drag them around the canvas. Try dragging your most important table to the center of the screen to start forming the center of your "Star"!
💡 Click for Solutions
Whenever you import a new dataset into Power BI, immediately come to the Model View to see what schema Power BI attempted to auto-detect. Your goal as a developer is to always try and arrange your tables into a clean Star Schema before you start writing formulas!
← 🔀 Combining and Reshaping Data | Next Topic → 🔗 Relationships and Filter Flow