Mastering Power BI Data Modelling: Joins, Schemas & Relationships.

Introduction

In Power BI, data is your raw material. However, raw data alone cannot answer complex business questions. To transform scattered table to actionable insights, a structure or blueprint is needed. Data modelling is the important process that determines how data can be analyzed, how accurate reports are, and how well the solution performs. Data modelling relies on Joins, Schemas and Relationships to simplify report development.

What is Data Modelling and Its Importance in Power Bi?

Data modelling is the process of identifying and defining how multiple tables connect and interact. It acts as a structural foundation of your reports, allowing different tables to communicate so that you can accurately filter, analyze and visualize data. Data modelling is important because;

• Lightning-fast performance- improves on speed and performance of the calculations and visuals.

• Accuracy- The calculations are accurate and consistent.

• Scalability- A clean data allows one to easily plug in new data sources without having to rebuild existing reports.

• Intuitive User Experience- Users can navigate through categorized dimensions and clearly defined metrics.

Joins in Power BI

Joins, also known as Merging Queries, are operations used to combine multiple tables into a single table based on a common column or relationship. Joins is mainly performed in Power Query Editor using the Merge Queries feature.

Types of Joins

1. Left Outer Join- Returns all rows from the left(first) table and only the matching rows on the right(second) table. If there is no match it places a null. For example, keeping all your sales transactions and bringing matching store details, even if a store hasn't made a sale.
2. Right Outer Join- Returns all rows from the right(second) table and only the matching rows on the left(first) table. If there is no match it places a null. It is the opposite of left outer join.
3. Full Outer Join- Returns all rows from both tables, combining where there are matches and placing null where there are no matches. For example, emptying two completely different junk drawers into a single large bin.
4. Inner Join- Returns only the rows that have matching values in both tables. For example, a VIP Lounge where one can enter if your name is on both the guest list and ticket holder list.
5. Left Anti Join- Returns only the rows from the left table that do not have matching row in the right table. For example, finding which customers registered for an event but never showed up.
6. Right Anti Join - Returns only the rows from the right table that do not have matching row in the left table.

Data Schema

A Schema is the blueprint or structural design that defines how data is organized, cleaned and stored in a database. It outlines the tables, fields, relationships, views and other elements within a database. The schema defines the specific rules that govern the data, including its types, constraints and relationships.

Tables and Keys in a Schema

Fact Table
A fact table is a central table in data warehouse which stores quantitative, numerical data about events, transaction or business processes. It is usually long and narrow. A fact table contains:

• Measures-Numerical data like quantities sold, profit margin, sales amount or duration.

• Foreign Keys-IDs that link fact table to dimension table like customer key, Product Key or Date Key.

Dimension Table
A dimension table stores qualitative data that describe the items in the fact table. It provide the who, where, why, which and when context of the fact table data. They are usually short and wide.
Primary Key
A primary key is a column that uniquely identifies each row. Here are rules of a primary key:

• No duplicates

• No null values

• One primary key per table
  Foreign Key
  A foreign key is a column in one table that uniquely points primary key of another table. It establishes logical link between the data and prevents disconnected records.

Schema Designs

Star Schema
Star Schema is used for storing and analyzing large amounts of data. It features a central fact table connected by several dimension table like a star.

• Uses-Business Intelligence, data warehouse and analytical dashboards.

• Pros-Easy to understand and fast for running aggregations, filters and business reports.

• Cons-Introduces data redundancy and is not ideal for rapid, everyday transactional update.

Snowflake Schema
A Snowflake schema is a multi dimensional data model where a central fact table is surrounded by normalized dimension tables, which split into sub-dimension tables.

• Uses-Financial reporting, logistics & transportation and e-commerce & retail supply chains.

• Pros-Saves storage, easier data maintenance and better for complex hierarchical data.

• Cons-Slower query performance and design is hard to build and maintain.

Flat Schema
A flat schema is a 2-D array in which every column contains the same type of data and the elements in the row are related to each other. It is like a table or a spreadsheet.

• Uses-No SQL database, log management, and real-time streaming data.

• Pros-Fast read performance, simple queries and seamless integration with BI tools.

• Cons-Consumes significantly more storage and makes data update tedious.

Relationships in Power BI

A relationship in Power BI connects two tables based on a shared column. It allows one to calculate metrics, filter or slice across multiple tables without merging them.

Cardinality
Cardinality defines the relationship between rows in different tables.

Types of Cardinality Relationship

• One-to-Many- This is the most common type, where one record in the primary table is linked to many records in the other table. For example, one customer can make many orders.

• One-to-One- A single record in the first table relates exactly to one record in the second table. For example, one citizen has one national ID record.

• Many-to Many- Records in both tables can relate to multiple tables in the other table. For example, one student can take many courses and one course can have many students.

Cross-filter Direction
Cross-filter direction determines how filters propagate between the related tables in the data model.

Types of Cross-filter Direction

• Single(one-way)- This is default setting where filters flow from the dimension table to the fact table.

• Both(Bi-directional)- Filters propagate in both directions.

Active vs Inactive Relationship

• Active- This is the default relationship used by Power BI and the tables are connected by a solid line.

• Inactive- It is represented by a dashed line.

Conclusion

A strong data model is the foundation of efficient reporting, accurate analytics, and scalable Power BI solutions. Understanding joins, schemas and relationships allows you to build reports that are both performant and easy to maintain.