Introduction
When beginners start using Power BI, most focus on visuals like charts and reports. However, a data professional knows that great dashboards are built on great data models.
You should note that Data modelling is the foundation that determines whether your reports are fast, accurate, and scalable, or are they confusing and unreliable.
In this article, we will learn about data modelling from the beginning in a way that is easy to understand. We will be covering:
- What SQL is and why joins exist.
- All SQL joins with real-life examples and syntax.
- Power BI relationships and how they work.
- The difference between joins and relationships.
- Fact versus dimension tables.
- Data modelling schemas.
- Role-playing dimensions and common modelling problems.
- Create models inside Power BI step by step.
What is Data Modelling?
Data modelling is the process of organising data tables so they can work together efficiently for analysis and reporting.
Instead of storing everything in one massive table, we structure data into connected tables using keys and relationships.
Think of data modelling as if organising a library. You have books which are like facts and categories which are like dimensions, and the library system is like the relationships connecting them.
From this analogy, we can conclude that the model reduces duplication, improves performance, and makes analysis easier
What is SQL?
SQL (Structured Query Language) is a language used to communicate with databases.
It allows us to:
- Retrieve data.
- Combine tables.
- Filter records.
- Analyse datasets.
One of SQL’s powerful features is JOINS.
What are Joins?
A JOIN combines rows from two tables based on a related column, an ID. For example, we have a customers table and a sales table. We join them to know which customer made a purchase, or maybe which sale was made in what store.
SQL Joins Explained
1. INNER JOIN
What it does
Returns only matching records in both tables.
INNER JOIN Venn Diagram
Real-life Example
Show customers who actually placed orders.
SQL Syntax applied.
SELECT *
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;
Results of merged tables after executing INNER JOIN.
2. LEFT JOIN (LEFT OUTER JOIN)
What it does
Returns ALL records from the left table and matching ones from the right.
Example
Show all customers, even those without orders.
(LEFT JOIN Venn Diagram)
Syntax
SELECT *
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID;
Final Results after executing (LEFT JOINT)
3. RIGHT JOIN
What it does
Returns all records from the right table and matches from the left.
Example
Show all orders, including those missing customer details.
RIGHT JOIN Venn Diagram
Syntax
SELECT *
FROM Customers c
RIGHT JOIN Orders o
ON c.CustomerID = o.CustomerID;
4. FULL OUTER JOIN
What it does
Returns all records from both tables, whether matching or not.
Example
Audit missing data between systems.
FULL OUTER JOIN Venn Diagram
Syntax
SELECT *
FROM Customers c
FULL OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID;
Merged tables after executing FULL OUTER JOIN
5. LEFT ANTI JOIN
What it does
Returns records from the left table that have no match in the right table.
Example
Customers who have never placed orders.
LEFT ANTI JOIN Venn Diagram
Syntax
SELECT *
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;
Results after executing LEFT ANTI JOIN
6. RIGHT ANTI JOIN
What it does
Returns records from the right table without matches in the left.
Example
Orders with missing customers.
(RIGHT ANTI JOIN Venn Diagram)
Syntax
**SELECT**
**FROM** Customers c
**RIGHT JOIN** Orders o
ON c.CustomerID = o.CustomerID
**WHERE** c.CustomerID IS NULL;
(Results after executing RIGHT ANTI JOIN)
Creating Joins in Power BI (Power Query)
Unlike SQL, Power BI creates joins visually.
Step-by-Step
- Open Power BI Desktop
- Click Transform Data ➡️ Power Query Editor
- Select a table
- Click Home ➡️ Merge Queries
- Select the second table
- Choose matching columns
- Select Join Type:
- Left Outer
- Right Outer
- Full Outer
- Left Anti
- Right Anti
Then,
- Click OK
- Expand columns
Power BI Relationships
After loading data, Power BI connects tables using Relationships instead of joins.
Types of Relationships
1. One-to-Many
Most common relationship.
Example:
- One customer-to-many orders
2. Many-to-Many
Occurs when both tables contain duplicates.
For example:
Students and Courses.
3. One-to-One (1:1)
Rare; both tables contain unique keys.
Cardinality
Cardinality defines how tables relate either :
- One-to-Many
- Many-to-One
- Many-to-Many
- One-to-One
This you Set inside Manage Relationships
Active vs Inactive Relationships
- Active relationships are used automatically in visuals.
-
Inactive are used via DAX (
USERELATIONSHIP()).
Example:
Order Date vs Ship Date.
Cross Filter Direction
Controls filtering flow we have Single direction which is recommended and Both directions which you should use carefully.
Creating Relationships in Power BI
Method 1: Model View
- Open Model View
- Drag the key column between tables
Method 2: Manage Relationships
- Home ➡️ Manage Relationships
- New
- Select tables and columns
- Set cardinality and filter direction
Joins vs Relationships (Key Difference)
| Joins | Relationships |
|---|---|
| Combine tables physically | Keep tables separate |
| Done in Power Query | Done in Model View |
| Creates new table | Logical connection |
| Happens during load | Happens during analysis |
Note that Joins prepare data and Relationships enable analysis.
Fact and Dimension Tables
Fact Tables
Contain measurable events.
Examples:
- Sales Amount
- Quantity Sold
- Transactions
Characteristics:
- Large
- Contains numeric values
- Contains foreign keys
Dimension Tables
Provide descriptive context.(Atrributes)
Examples:
- Customer
- Product
- Date
- Location
Characteristics:
- Text attributes
- Smaller tables
Data Modeling Schemas
1. Star Schema ⭐️
A fact table in the center is connected to dimensions. This is recommended for business reporting dashboards. Benefits include performance and easy understanding
(Star schema diagram)
2. Snowflake Schema
Dimensions are split into related tables. This is used in normalized enterprise systems. It has its Pros which include redundancy but can include more complex queries.
(snowflake diagram)
3. Flat Table (DLAT – Denormalized Large Analytical Table)
All data is stored in one table. This is used for small datasets or quick prototypes. It's a simple setup, but can result in poor scalability and slow performance.
Role-Playing Dimensions
A single dimension is used multiple times. For example, a date table can be used as the order date, ship date, and delivery date. The solution is to create multiple relationships (one active).
Common Data Modeling Issues
- Circular relationships
- Many-to-many misuse
- Bidirectional filtering confusion
- Duplicate keys
- Missing dimension tables
Conclusion
Data modeling is not just a technical step but the architecture behind every reliable Power BI report.
Understanding joins teaches how data is combined.
Understanding relationships teaches how data communicates during analysis.
Understanding schemas teaches how data scales.
Master these concepts, and Power BI stops being just a visualisation tool and becomes a powerful analytical engine.
Try opening one of your old Power BI files today, switch to Model View, and redesign it using a Star Schema. You might be surprised how much faster and clearer your reports can become.
If this article helped you understand Power BI data modeling better, share it with someone who is learning, and start talking about good ways to work with data
Happy modelling!
Top comments (1)
Good articles that covers core concepts