Power BI Data Modelling, Relationships & Joins

Introduction

This week we got started on learning Power BI its uses and how its applied in data analysis. When working with Power BI, the way data is organized and connected has a major impact on how easily reports can be created, how calculations work, and how well a Power BI solution performs.
Data modelling in Power BI involves organizing data into tables and defining relationships between those tables so that the data can be analyzed effectively. A well-designed data model makes reports easier to understand, DAX calculations easier to write, and Power BI solutions easier to maintain and scale.

Data Modelling in Power BI

Data modelling is the process of organizing data into structured tables and establishing relationships between those tables so that Power BI can understand how the data is connected.
Instead of keeping all this information in one huge table, the data can be separated into different tables and connected using common fields.
A well-designed data model is important because it can improve:

• Model readability
• Maintainability
• Filter propagation
• Overall reporting experience
• DAX calculations
• Data organization
• Scalability
• Report performance

There are several ways data can be structured. The three common modelling approaches are flat tables, star schemas, and snowflake schemas.

Flat Tables

A flat table stores all the required information in a single table. For simple datasets, a flat table may be sufficient, but it is generally less suitable for large and complex business intelligence solutions.

Advantages of a Flat Table

A flat table is relatively easy to understand because there are no relationships between multiple tables.
It can be appropriate when there are few repeated values, a quick analysis is required, the analysis is simple and dataset is small.

Disadvantages of a Flat Table

The major problem is data redundancy which can result in repeated data, larger datasets, more difficult maintenance, potential inconsistencies and less flexible modelling.

Star Schema

A star schema separates the data into a main fact table and several surrounding tables. The structure resembles a star, which is where the name comes from. The main fact table contains business events or transactions, while the dimension tables contain descriptive information about those events.
It is particularly useful for business intelligence and reporting solutions where users need to analyze business events across different dimensions.
For example, a company could use a star schema to analyze:

• Sales by product
• Sales by product category
• Sales by customer
• Sales by location
• Sales by month

Advantages of a Star Schema:

• It is generally well suited to Power BI's analytical model.
• It is relatively simple to understand.
• It reduces unnecessary data duplication.
• It supports efficient reporting and analysis.
• It makes DAX calculations easier to manage.
• It provides a clear structure for filter propagation.
• It is easier to maintain and extend. ## Disadvantages of the Star Schema Incorrect relationships or poorly designed keys can result in incorrect calculations or unexpected filtering it also requires a proper understanding of relationships between tables for everything to be correct.

Snowflake Schema

A snowflake schema is similar to a star schema, but dimension tables are further divided into additional related tables.
Instead of storing all product-related information in one dimension table, information can be separated into multiple related tables.
It may be appropriate when the underlying data contains complex hierarchical structures or when dimensions are large and naturally divided into related entities. However, unless it's necessary it's advised to keep the dimensions simpler to make the Power BI model easier to work with.

Advantages of a Snowflake Schema

• Represent complex hierarchies.
• Organize highly structured source data.
• Useful when dimensions contain multiple levels.
• Reduce repeated information within dimensions. ##Disadvantages of a Snowflake Schema It can be more difficult to navigate than a straightforward star schema because of the extra tables, extra relationships and more filter paths. This makes it more complex. It has a steeper learning curve. The report development can be complicated.

Comparing the Three Approaches

The key difference is how the data is organized. A flat table keeps everything together, a star schema separates facts from dimensions, while a snowflake schema takes normalization further by splitting dimensions into additional related tables.
The star schema provides a practical balance between performance, simplicity, and maintainability.
Power BI models consists of tables, keys, relationships, and rules that determine how information flows between those tables.

Fact Tables and Dimension Tables

A fact table stores measurable business events or transactions. It answers questions such as how many?, how much?and how many transactions.
Dimension tables contain descriptive information used to analyze the facts. It answers questions such as who?, when? and where?

Grain or Granularity

The grain describes exactly what one row in the fact table represents. Clearly defining the grain is important because it prevents confusion and incorrect calculations. For example For example, a sales fact table should not have one row representing a whole order in some cases and one row representing an individual product in other cases.

Relationships in Power BI

A relationship connects two tables using a common column. Relationships are necessary because business data is often distributed across multiple tables. For example it shows how a customer relates to a product or different products.

Relationship Cardinality

Cardinality describes how many records in one table can be related to records in another table. Power BI supports several relationship types such as:

• One-to-Many (1:*)- This is normally the preferred relationship between a dimension and a fact table. For example, A customer can make many sales, but each sales record belongs to one customer.
• One-to-One (1:1)- A one-to-one relationship means that each record in one table corresponds to only one record in the other table. For example, Every employee has their own unique employee details such as their email or ID.
• Many-to-Many (:)- A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another table for example, students can enroll in many courses, and each course can have many students. Many-to-many relationships can be useful, but they should be handled carefully because they can create ambiguous filtering and unexpected results.

Primary Keys and Foreign Keys

A primary key uniquely identifies a record in a table, for example employee ID, Student ID.
A foreign key is a column used to reference a record in another table for example, a customer ID column can be found in different tables since it's possible for customers to make multiple purchases. The relationship works because the dimension contains unique values while the fact table can contain repeated values.

Referential Integrity

Referential integrity means that foreign-key values should correspond to valid records in the related dimension. If the column does not exist in the dimension, the model may produce unexpected results when filtering or calculating data.
Ensuring that keys are clean and valid is therefore an important part of data preparation.

Active and Inactive Relationships

An active relationship is the default relationship used when filters travel between tables. Only one relationship between the same two tables can normally be active at a time. An inactive relationship can be used in specific DAX calculations when required.

Filter Direction

Relationships can determine how filters move between tables.

• Single-Direction Filtering- In a typical star schema, filtering usually flows from the dimension to the fact table. This is generally the preferred approach for a star schema because the filter path is clear and predictable.
• Bidirectional Filtering- This is when filters flow in both directions. It is recommended to use the single-direction filtering unless there is a specific reason to use bidirectional filtering.

Joins in Power Query

Power Query provides a feature called Merge Queries, which allows tables to be joined during the data transformation stage therefore relationships in the Power BI model are not the same thing as joins in Power Query:

• ** Left Outer Join**- This keeps all records from the left table and matching records from the right table.
• ** Right Outer Join**- This keeps all records from the right table and matching records from the left table.
• ** Full Outer Join**- This keeps all records from both tables. ALL LEFT records + ALL RIGHT records
• ** Inner Join**- This keeps only records that have a match in both tables. Inner joins are useful when we only want records that exist in both datasets.
• ** Left Anti Join**- This returns records that exist in the left table but do not have a match in the right table. This can be useful for data quality checks and finding missing relationships.
• ** Right Anti Join**- This returns records that exist in the right table but do not have a match in the left table.

Power Query Joins vs Power BI Relationships

A Power Query merge combines information from two tables during the data preparation/transformation stage. A Power Query merge can physically bring columns from one table into another table.
A relationship in Power BI is created later in the data model. Instead of physically combining the tables, we keep them separate. This means creating a relationship in Power BI does not physically merge the tables together.
The relationship tells Power BI how filters and calculations should move between the tables.

When Should You Use a Merge Instead of a Relationship?

A merge can be appropriate when information genuinely belongs together or when data transformation requires combining columns. However, excessive merging can create very large and complicated flat tables. For analytical reporting, keeping fact and dimension tables separate is often preferable.

Why Keep Fact and Dimension Tables Separate?

• Reduced Redundancy
• Easier DAX-Measures can be written against a clear fact table while dimensions provide filtering context.
• Better Model Readability- A developer can easily understand which tables contain facts and which contain dimensions.
• Easier Reporting
• Better Scalability

Conclusion

Data modelling is one of the foundations of building effective Power BI solutions. Creating attractive visualizations is only one part of the reporting process. If the underlying model is poorly designed, calculations can become difficult, filters can behave unexpectedly, and maintaining the report can become increasingly complicated. For a typical business intelligence project, I would recommend a star schema. A well-designed star schema provides a relatively simple model with clear relationships between fact and dimension tables. This can support efficient filtering and analytical queries. And use one-to-many relationships between dimensions and the fact table and single-direction filtering, where filters flow from the dimension to the fact table. I would avoid unnecessary bidirectional relationships because they can introduce ambiguous filter paths and make the model harder to understand. Many-to-many relationships would also only be used when the business requirement genuinely requires them. Where possible, a bridge table can be used to create a clearer model.