Data Modelling, Relationships & Joins

INTRODUCTION

Power BI is widely used for transforming raw data into meaningful insights, interactive reports, and business intelligence dashboards. However, the quality of the analysis depends not only on the visualizations created but also on how the underlying data is structured and connected. A poorly designed data model can lead to inaccurate results, complicated calculations, inefficient reports, and difficulties when the model needs to be expanded or maintained.

Data modelling provides the foundation for organizing data in a way that supports efficient analysis. Power BI offers different approaches to modelling data, including flat tables, star schemas, and snowflake schemas. Understanding the differences between these approaches helps developers and analysts select a structure that is appropriate for their reporting requirements and data environment.
This article explores these concepts from both a theoretical and practical perspective.
It examines different data modelling schemas, fact and dimension tables, relationships and cardinality, filter directions, and the different types of joins available in Power Query.

Data Modelling

Data modelling in Power BI is the process of organizing data from one or more sources into a structured model that defines how tables, columns, and relationships work together.

A data model determines how Power BI understands the relationships between different datasets and how those datasets can be used for reporting, visualization, filtering, and calculations.

A good data model contributes to:

-Reporting: Tables and relationships allow users to combine information from different business areas in a single report.

-Analytics: A properly structured model makes it easier to analyze trends, patterns, and relationships between business measures and descriptive information.

-DAX calculations : DAX measures depend heavily on relationships and filter context. A clear model makes calculations easier to write, understand, and maintain.

-Performance: Reducing unnecessary columns, duplicate data, and complicated relationships can reduce the amount of data Power BI has to process.

-Scalability: A well-designed model can accommodate additional data, dimensions, and business requirements without requiring the entire model to be redesigned.

• Maintainability: Separating facts from descriptive information makes the model easier to understand, troubleshoot, and modify.

Schemas

We have different types of Schemas:

1. Flat Table Schema This is a data model in which all relevant information is stored in one table.

Here we are merging all the tables for it to become one.

Advantages of Flat Table
The main advantages of a flat table include:

1. Simplicity – The structure is easy for beginners to understand.
2. Easy data loading – Data can often be imported directly without creating multiple relationships.
3. Simple reporting – Basic charts and aggregations can be created quickly.
4. Straightforward filtering – All fields are available within the same table.

Disadvantages of Flat Table

1. Data duplication – Customer, product, and branch information may be repeated for every transaction.
2. Larger model size – Repeated text values and attributes can increase the amount of data that needs to be stored.
3. Poor scalability – As the dataset grows, maintaining one large table becomes more difficult.
4. Limited reusability – The same customer or product information cannot easily be reused across different fact tables.

A flat table may be appropriate when:

The dataset is relatively small.
The analysis is simple.
There is only one major business process.
The data does not require complex relationships.
The model is being used for a quick analysis or prototype.

1. Star Schema

This is a data model where there is a fact table containing all the details from all the tables , surrounded by dimensional tables.

Advantages of Star Schema

1. Clear structure.
2. Easy to understand.
3. Good for reporting.
4. Reduces unnecessary duplication.
5. Works well with DAX.
6. Easier to maintain.

Disadvantages of Star Schema

1. Requires understanding of relationships.
2. More tables than a flat model.
3. Requires proper modelling

It is appropriate to use star Schema when:

• Required to do Business intelligence

• Creating Dashboards

• Sales reporting

• Financial reporting

• Large analytical datasets

1. Snowflake Schema.

This is a data model where there is a fact table containing all the details from all the tables , surrounded by dimensional tables and the dimensional tables are split into different categories.

Advantages of Snowflake Schema

1. Reduces duplication
2. More normalized
3. Can be useful for complex hierarchical data
4. Makes some large dimensions more manageable

Disadvantages of Snowflake Schema

1. More relationships
2. More complicated model
3. More complicated filtering
4. Can make DAX and navigation harder to understand
5. When appropriate

Useful where dimensions contain multiple hierarchical levels or significant repeated information.

FACTS AND DIMENSIONAL TABLES

A fact table is a table in a data model that stores business events or transactions and the numerical values associated with those events.

• It tells us what happened and records the measurable values of that event.

fact table contains:

1. Keys such as CustomerID, ProductID, and DateID
2. Measures/numeric values such as Quantity and SalesAmount
3. Information about the business event, such as the sale

A dimension table is a table that stores descriptive information or attributes used to provide context to the events recorded in a fact table.

• It tells us who, what, when, where, or other characteristics related to a business event.

• This table doesn't tell us how much a customer purchased. Instead, it describes the customer.

• A fact table normally contains three important types of information.

1. Foreign keys-That connect the fact table to dimension tables.
2. Measures-These are numerical values that can be aggregated or analyzed.
3. Business events-The fact table represents something that happened.

• A Dimension table normally contain descriptive attributes.

1. ProductID identifies the product.
2. ProductName describes it.
3. Category describes it.
4. Brand describes it.

• These attributes allow users to slice, filter and group the information in the fact table.

Measures and Descriptive Attributes

-Measures represent numeric values that can be analyzed.

• Descriptive Attributes describe the business entities involved.

Grain / Granularity of a Fact Table
-The grain of a fact table defines what one row in the fact table represents.

• It refers to description of what each record in a table consists of. Example: One row should be able to represent one product sold in one sales transaction/ one patient from coming in to the end of the diagnosis.

The grain helps determine:

1. What each row represents
2. What can be summed
3. How measures should be calculated
4. What relationships are appropriate
5. How the fact table should be designed

• Clearly defining the grain of a fact table helps prevent double-counting and ensures that calculations and relationships accurately represent the underlying business process.

Illustration of star and snowflake schemas.
Snowflake Schema- we divide the dimensional tables into sub categories
Then connect to each.

Star Schema- we create dimensional table that can be linked wwith the fact table using the foreign keys.

RELATIONSHIPS IN POWER BI

• A relationship in Power BI is a connection between two tables that allows Power BI to associate related records using a common column, usually a key column.

• In a well-designed Power BI model, information is often distributed across multiple tables rather than stored in one huge table.

• Relationships therefore allow Power BI to:

1. Connect related tables
2. Propagate filters
3. Perform calculations across tables
4. Analyze transactions using descriptive attributes
5. Build visualizations from multiple tables
6. Maintain a structured data model

Relationship Cardinality

• Cardinality describes how many records in one table can correspond to records in another table.

• Power BI commonly uses:
  

One-to-Many (1:*)
One-to-One (1:1)
Many-to-Many (:)

• Cardinality is important because it tells Power BI how the data on both sides of a relationship behaves.

One-to-Many Relationship (1:*)

• This is the most common relationship you'll encounter in a Power BI star schema.

• A one-to-many relationship means that one record in one table can be associated with multiple records in another table.
  The notation is:
  1 : *
  The 1 represents the table where the relationship column contains unique values.
  The
  *
  represents the table where the same value can appear multiple times.

• We can Use it when:

1. The "one" side contains unique values.
2. The "many" side can contain repeated values.
3. One entity can have multiple transactions.

DimCustomer → FactSales
DimProduct  → FactSales
DimDate     → FactSales
DimLocation → FactSales

One-to-One Relationship (1:1)

• A one-to-one relationship means that one record in one table corresponds to exactly one record in the other table. -For a One to One relationship to work , remove duplicates, since the record should only show one relationship for that specific customer or patient.

-It can be useful when information about the same entity has been separated into two tables.
1:1 Relationship , shouldn't be used when:
If the two tables contain essentially the same type of information and always have the same grain, it may be simpler to combine them during data preparation instead of maintaining a 1:1 relationship.

Many-to-Many Relationship (:)

• A many-to-many relationshipoccurs when multiple records in one table can be associated with multiple records in another table.

Example: A patient can visit the hospital many times , or a student can take multiple courses.

In our below example, a Patient can visist the hospital many times and when visiting many times , he/she can be treated by different doctors.

Primary and Foreign Keys

In the relationships used in Power BI, we have Primary Keys and Foreign Keys.

A primary key is a column that uniquely identifies each record in a table while a foreign key is a column that references a primary key in another table.

These are very important when defining relationships in Power BI.

Unique Values

• For a normal 1:* relationship:One side must have unique values and the many side can have repeated values.

• If the supposed "one" side contains duplicates, Power BI cannot correctly treat it as the unique side of a normal one-to-many relationship.

Referential Integrity

Referential integrity means that values in a foreign-key column should correspond to valid records in the related primary-key column.

Active and Inactive Relationships

• An active relationship is the relationship Power BI uses by default when calculating and filtering data. It is normally shown as a solid line in Model View.

• An inactive relationship exists in the model but isn't used automatically for normal filtering. It is normally displayed as a dashed line.

Filter Direction

• Filter direction determines how filters move between related tables in a Power BI data model.

Single-Direction Filtering

• With single-direction filtering, the filter normally travels from the one side of the relationship to the many side.
  Like mostly in star Schema.

• Single-direction filtering is generally easier to understand because the model follows a clear path.

After Creating the Fact and dimensional tables, we will therefore define the filtering direction
Dimension → Fact

DimPatient    ──►
Dimdiagnosis  ──► FactSales
DimProcedure  ──►
Dimdepartments──►

Both/Bidirectional Filtering

• With bidirectional filtering, filters can travel in both directions between related tables.

The relationship should behave like:

Dimdepartments ◄────► Factvisits

• Bidirectional filtering can be useful in certain scenarios, particularly when the analytical requirement genuinely requires filters to propagate in both directions.

However, it should not automatically be used for every relationship.
There may be multiple routes through which a filter can travel between tables.

Disadvantages of Bidirectional

1. This can create ambiguous filter paths, making it difficult for Power BI to determine which filtering route should be used.
2. Bidirectional relationships make the model more difficult to understand and troubleshoot.

Please note: Use single-direction filtering by default and use bidirectional filtering only when there is a clear analytical requirement.

Joins in Power Query

A join combines information from two tables based on a matching column.
In Power Query, joins are performed using , Merge Queries.

To Join Tables>> Click transform data>> it will open power Query.
Identify the table>>click merge queries>>choose the table tou want to join with>> then choose the type of join you will need.

Left Outer Join
A Left Outer Join keeps all records from the left table and adds matching records from the right table.
If there is no match, the right-side information is returned as blank.

In our example we are joining the Products table with the sales table.

Right Outer Join
A Right Outer Join keeps all records from the right table and adds matching records from the left table.
This join is useful when the right-hand table represents the records that must all be preserved.

Full Outer Join
A Full Outer Join keeps all records from both tables, whether they match or not.
If there is no match , it will join and whats not visible on both tables will read as null.

Inner Join
An Inner Join keeps only records that exist in both tables.
This is useful when the analysis requires only records that exist in both datasets.

Left Anti Join

A Left Anti Join returns records that exist in the left table but do not have a matching record in the right table.

Right Anti Join
A Right Anti Join returns records that exist in the right table but do not have a matching record in the left table.

# Power Query Joins vs Power BI Relationships

Power Query Merge

A Power Query merge is a data transformation operation.
It takes two tables and combines information based on matching columns.
This is a transformation done before the data is loaded into the Power BI model.

Power BI Relationship
A relationship does not physically merge the tables.Instead, the tables remain separate (Fact and dimensional tables) and are connected through relationships.
A relationship is generally preferable when you are building a BI model with fact and dimension tables.

Keeping the fact and dimensional Table separate is useful because each table has a clear purpose.

## Recommended Power BI Model

For a typical business intelligence project, I would recommend a Star Schema as the default modelling approach.

The recommendation is based on the separation of business events from descriptive information.

Recommended Relationships
One-to-many (1:*) relationships

Recommended Filter Direction
Single-direction filtering from dimensions to the fact table.

Data → Power Query → Tables → Relationships → Filters → DAX/Reports

CONCLUSION
Designing an effective Power BI solution requires more than simply importing data and creating visualizations. A well-designed data model provides the foundation for accurate analysis, efficient reporting, and long-term maintainability. This article has examined the key concepts involved in building such a model, including flat tables, star schemas, snowflake schemas, fact and dimension tables, relationships, filter directions, and Power Query joins.

Among the modelling approaches discussed, the star schema provides a practical structure for most business intelligence projects. By separating measurable business events into fact tables and descriptive information into dimension tables, it reduces unnecessary data redundancy while making the model easier to understand, maintain, and scale. One-to-many relationships between dimensions and fact tables, combined with single-direction filtering, also provide predictable filter propagation and simplify analysis.

It is also important to distinguish between Power Query merges and Power BI relationships. A merge is a data transformation operation used to physically combine information during the data preparation stage, while a relationship connects separate tables within the Power BI data model without physically combining them. Understanding when to use each approach helps prevent unnecessary merging and preserves a clear fact-and-dimension structure.

Ultimately, there is no single modelling approach that is appropriate for every dataset. The choice should consider the size and structure of the data, reporting requirements, performance, scalability, and maintenance needs. However, for a typical Power BI business intelligence solution, a well-designed star schema with appropriate relationships and controlled filter propagation provides a strong foundation for building reliable, understandable, and scalable analytical reports.

https://github.com/SusanKiptoo/Data-modelling-Relationships-Joins