DEV Community

michael akello
michael akello

Posted on

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

#beginners #data #sql #tutorial

Table of Contents

  1. What is Data Modeling?
  2. Schemas, Relationships, and Joins (Overview)
  3. SQL Joins.
    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • FULL OUTER JOIN
    • LEFT ANTI JOIN
    • RIGHT ANTI JOIN
  4. Power BI Relationships
    • Cardinality (1:M, 1:1, M:M)
    • Active vs Inactive Relationships
    • Cross-Filter Direction
    • Joins vs Relationships

1. What is Data Modeling?

According to IBM, data modeling is the process of creating a visual representation of an information system to show how data elements relate to each other.

Simply put:
Data modeling is the process of identifying, organizing, and defining the types of data a business collects—and how those data points relate.
It uses Diagrams, Symbols, and Table structures to show how data is captured, stored, and used.

Why Data Modeling Matters

A well-defined data model helps to:

  • Understand data requirements
  • Ensure proper structure for reporting
  • Align data with business goals

- Maintain data integrity and consistency

2. Schemas, Relationships, and Joins (Overview)

A database schema is a structural blueprint (tables, fields, keys) defining how data is organized, while relationships define how tables connect (e.g., foreign keys). Joins are SQL operations that combine data from these related tables. Together, they ensure data integrity and facilitate querying complex data structures.

These three concepts work together:

Schemas → The blueprint (tables, fields, keys)

Relationships → Logical connections between tables (via keys)

Joins → The method used to combine data from tables

3. SQL Joins.

Joins combine data from multiple tables using a common column (called a join key).

Example Scenario:
a school dataset with table Student joined to table Marks.

3.1 INNER JOIN

Returns only matching records from both tables.

Student ∩ Marks

    👉 Use case: Show students who have marks recorded.
Enter fullscreen mode Exit fullscreen mode




3.2 LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table + matching rows from the right.

All Students + Matching Marks

👉 Use case: Show all students, even those without marks.
Enter fullscreen mode Exit fullscreen mode




3.3 RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table + matching rows from the left.

All Marks + Matching Students

👉 Use case: Identify marks that don’t have a matching student record.
Enter fullscreen mode Exit fullscreen mode




3.4 FULL OUTER JOIN

Returns all rows from both tables, matching where possible.

Students ∪ Marks

👉 Use case: Audit mismatches between datasets.
Enter fullscreen mode Exit fullscreen mode




3.5 LEFT ANTI JOIN

Returns rows from the left table with no match in the right table.

Students NOT IN Marks

👉 Use case: Find students with no recorded marks.
Enter fullscreen mode Exit fullscreen mode




3.6 RIGHT ANTI JOIN

Returns rows from the right table with no match in the left table.

Marks NOT IN Students

sql Joins

Top comments (0)