Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Introduction

Data Modeling, the beginning of making sense out of data. This is one of the most important steps to dealing with your data. This means that your most complex and detailed dashboards will not be appreciated without clean and well done data modeling.

In this guide we will go through

1. Data Modeling
2. All SQL joins
3. Power BI relationships and how they differ from joins
4. Fact and Dimension tables
5. Data Modeling Schemas

What is Data Modeling

Data modeling refers to the process of structuring and organizing data so that it can be analyzed effectively. Think of this as cleaning a messy room and putting everything where it belongs: all books in the shelves, clothes on your hangers and shoes in the shoe-rack.

In Power BI this means: defining tour tables, creating relationships and optimizing performance and accuracy.

Think of it like designing a map: if roads (relationships) are wrong, you’ll never reach the right destination (insights).

SQL Joins

When working with data, it’s rare to find everything you need in one table. In real-world databases, data is split into multiple tables to keep things organized and efficient.

That’s where SQL joins come in.

A SQL join is used to combine data from two or more tables based on a related column (usually a key).

Imagine analyzing a business given these tables:

• A Products table that tells you what is sold
• A Customers table that tells you who is buying
• A Sales table that records transactions

But the Sales table only contains IDs(product id and customer id), not full details (like customer name and product name/product price).

So if you want to answer: “Who bought what?” You must join the tables.

Types of SQL Joins

Let’s break them down. Shall we?

1. INNER JOIN (Only Matching Data)

This join is used when you want to find only records that exist in both tables, in other terms matching rows

LEFT JOIN (Keep Everything from the Left Table)

Also known as the left outer join.
This join is used when you want to return only records in your left table that also exist in your right table. “Show me everything on the left, even if there’s no match in the right.”

RIGHT JOIN (Keep Everything from the Right Table)

Also known as the right outer join.
This join is the polar opposite of the left join. It is used when you want to return everything in the right table that also exist in the left table.

Joins are relating different tables using a related column.
Say you have a table on sales - that has different products sold. One on products - with a list of the products available for sale and another on customers - detailing the unique attributes of your customers.

To relate the tables (get more information not available in our main table -sales) we use the primary key marked(PK) from our dimension tables (customers and products) and find the matching input in the foreign key columns in the fact table (sales)

The process of ralting these table requi