Data Modelling, Relationship and Joins

Introduction

Data modelling is the process of organizing, structuring and defining relationships between data tables to enable meaningful analysis. Think of it as designing the blueprint of a building without solid foundation, everything on top will be unstable.

Why Data Modelling Matters for Your Power BI Reports

Performance: Well-modelled data leads to faster report refresh times and responsive visuals. Reports that take 5minutes can be reduced to 30 seconds with proper modelling.

Accuracy: Proper relationships ensure your calculations produce correct results, eliminating discrepancies between reports and source data.

Scalability: Good models can grow with you business without requiring complete rebuilds, saving time and resources.

Maintainability: Clean models are easier to update, troubleshoot and handoff to colleagues, reducing technical debt.

Simple Reports: A good model reduces the need for complicated formulas and makes visualizations easier to create.

Data Modelling in Power BI

In power BI, data modelling involves:

• Ensuring data accuracy and consistency.
• Creating relationships between tables.
• Optimizing the structure of data for performance.
• Defining calculated columns and measures.

1: Flat Table

Single table where all the data needed for analysis is stored together rather than being separated to fact and dimension table.

Example:

Advantages

• Simple to create and understand
• Easy to use for small datasets.
• Requires fewer relationships between tables
• Useful for quick analysis

Disadvantages

• DAX measures become complex and error-prone
• Duplicate data everywhere
• Slow report performance as data grow
• Hard to maintain when source data changes

Star schema

A star schema organizes data into one central fact table surrounded by several dimension tables, each directly connected to the fact table by a single relationship.

Advantages

• Simplicity: Easy to understand and navigate for both technical and business users.

• Performance: Fewer joins mean faster queries and report loading times.

• DAX-Friendly: Calculations are straightforward and intuitive.

• Scalability: Easy to add new dimensions without restructuring.

• User Experience: Business users can navigate relationships naturally.

Disadvantages

• More tables: Data is split into fact and dimension tables, so the model can be more complex than a single flat table

• Requires relationship: You need to correctly create and manage relationships between the fact and dimension tables.

• Requires proper planning: You need to understand things like granularity, keys, cardinality and filter direction to build the model correctly

• Can be confusing to beginners: Someone new to Power BI may initially find multiple related tables harder to understand than one flat table.

Example of a star schema:

The "CustomerID" column contains unique values in the DimCustomer table because each customer is represented only once. In this table, "CustomerID" acts as the primary key, uniquely identifying each customer.

However, the same "CustomerID" can appear multiple times in the FactSales table because the fact table records individual sales transactions. A single customer may make multiple purchases, meaning their "CustomerID" will be repeated for each transaction.

For example, if customer C001 makes three purchases, C001 will appear once in the "DimCustomer" table but three times in the "FactSales" table. This creates a one-to-many (1:*) relationship, where one customer in the dimension table can be associated with many sales transactions in the fact table.

This structure allows Power BI to connect customer information with their transactions and perform analysis such as total sales per customer, number of purchases, or average spending.

Snowflake Schema

The snowflake schema normalises dimension tables into sub-dimensions, creating a more complex structure.

STRUCTURE:

• Central fact table.
• Dimension table broken into normalised sub-tables
• More relationships and joins required
• Reduced data redundancy

Example of a snowflake schema

Below is a simplified representation of how a snowflake schema model looks

Difference between a Snowflake and a Star schema

The difference is shown in the image below:

Advantages

• Better for certain data warehousing scenarios
• Improves data integrity through normalization
• Supports detailed hierarchical drill-down

Disadvantages

• Slower query performance due to additional joins
• More complex DAX calculations required
• Harder for business users to navigate and understand
• More complex relationships to manage

Fact Tables and Dimension Tables

Every star (or snowflake) schema is built from two kinds of tables that play very different roles.

Fact Tables

A fact table stores the measurable, numeric business events for report analysis. It usually contains metrics, measurements or business operation facts in addition to foreign keys.

• Typically stored in a fact table: foreign keys to related dimensions, numeric measures (Sales Amount, Quantity, Cost), and sometimes a degenerate identifier such as as a transaction number.

• Grain/ granularity: The grain of a fact table is the level of detail one row represents, for example, "one row per sales order line" versus "one row per order."
  Choosing the grain correctly up front is critical: too coarse a grain loses detail needed for analysis, while too fine a grain increases row counts and file size unnecessarily. All measures in a fact table should be defined at a single, consistent grain.

• Common examples: FactSales, FactOrders, Fact Transactions, FactInventory

Dimension Tables

A dimension table is a database table that stores attributes describing the facts in a fact table.

The dimension plays an important role in its model because they provide reference information about a set of measurable events, found in the fact table.

• ** What is typically stored in a dimension table** : a unique key column, descriptive text attributes (names, categories, regions), and sometimes hierarchies
• Common examples: DimCustomer, Dim product, DimDate, DimLocation.

Measures vs Descriptive Attributes

Measures are quantitative values that can be aggregated-the raw numbers in your data that you want to analyze.

Example of measures:

• Sales amount ($)

• Quantity sold (units)

• Revenue

• Transaction Count

• Page views

• Click count

Attributes

Are descriptive characteristics or properties of data entities. They provide additional information about the data but aren't typically aggregated.

Examples:
Customer attributes:

• Name
• Email
• Phone number
• Address

Product Attributes:

• Color
• Size
• Brand -Category

Below is a practical example of of FactSales and its Dimension:

Relationship in Power BI

Power BI allow users to create data models by establishing relationships between related tables.

These relationships connect tables through common columns and allow filter and calculations to work across the model. Power BI can automatically detect some relationship, while others can be created or modified manually.

Type of Table Relationship

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

A value in one table can be related to multiple rows in another table. This is one of the most common relationship types in PowerBI.

• Example: One row in DimCustomer(CustomerID =1000) relates to many rows in FactSales

when not to use: Avoid forcing a one to many relationship between two tables that are actually both at a "many" grain - that scenario calls for a many-to-many relationship or a bridge table instead.

2.One to One (1:1)

Is a relationship setting that describes how two tables are related to each other. To put it simply, it means that for each unique value in one table, there is exactly one corresponding unique value in the other table, and vice versa.

Imagine you have two tables: One containing a list of students and their Students IDs, and another containing a list of courses and their courses IDs. If you establish a one-to-one cardinality relationship between these tables based on the student ID and Course ID, it means each student is enrolled in one and only one course, and each course is taken by one and only one student.

If both tables could simply be combined into one without redundancy, a one-to-one relationship is usually unnecessary complexity, merging them is often cleaner.

3: Many-to-Many (:) Relationship

Refers to a relationship between two tables where multiple values in one table can be associated with multiple values in another table.

• Example: Imagine you have two tables in your Power BI project: One for "students" and another for "courses." If a student can enroll in multiple courses, and a course can have multiple students, you have a "Many-to-Many" relationship. In this scenario, each student is associated with multiple courses, and each course is associated with many students.

When not to use:
Many-to-Many relationship should not be used as a quick fix for poor key design; can produce double-counted aggregation if not modelled carefully, and a proper bridge table is usually a safer alternative.

Keys, Cardinality and Integrity

Keys

Relational databases form the backbone of modern data management, ensuring data integrity and seamless relationships between datasets.
Two critical concepts that make this possible are Primary Keys and Foreign Keys.

Primary Key

A primary key is a column (or a set of columns) in a table that uniquely identifies each row in that table . It ensures that no two rows have the same identifier, and it cannot contain null values.

Key characteristics of Primary Key:

• Uniqueness: Ensures no duplicate values

• Non-nullability: Guarantees that every row has a valid identifier.

Foreign Key

A Foreign Key is a column (set of columns) in one table that references the Primary Key of another table. It establishes a relationship between the two tables and enforces referential integrity, ensuring that the data in the foreign key column matches data in the referenced primary key column.

Key Characteristics of Foreign Key

• References a Primary Key in another table.

• Ensures relationships between data sets.

• Prevents invalid entries by enforcing constraints.

Why Do We Need Primary Keys and Foreign Keys

1. Data Integrity

• A primary Key ensures that every record is unique and identifiable.

• Foreign Keys ensures that relationships between tables are valid, preventing orphaned records or invalid references.

2. Avoid Data Redundancy

By splitting data into multiple tables and using keys to connect them, we avoid duplicating data unnecessarily.

3. Ease of Querying Data
Keys make it easy to write queries that join tables and retrieve related data efficiently.

4. Enforcing Business Rules
Foreign keys enforce rules such as "a student cannot enroll in a course unless they exist in a student's table"

Unique Values

a dimension's key column should contain unique values so that Power BI can use it as the “one” side of a one-to-many relationship; this is precisely why CustomerID appears once per customer in DimCustomer but many times in FactSales — DimCustomer stores one description of each customer, while FactSales logs every transaction that customer generated.

Cardinality

Cardinality specifies how the rows in one table are related to the rows in another table.
Cardinality is a crucial concept in data modelling because it helps define how data from different tables can be combined and used in visualizations and calculations.

Imagine that you have a big box with only red cups and blue cups, you have a cardinality of two because there are two different kinds. But if you have a box with red, blue green and yellow cups, you have a cardinality of four because there are of different kinds.

In Power BI, cardinality is important because it helps you understand how diverse your data is. It tells you how many unique values there are in a column or a set of data. This information is useful for creating charts, graphs and reports to see patterns and make decisions.

Integrity

Referential Integrity

Is a rule that ensures that the relationships between tables in a relational database remain valid and consistent. It guarantees that a foreign key value always point to an existing valid record inn another table, thus preventing orphaned records or broken links within the data.

Referential integrity ensures that every foreign key value has a corresponding primary key value in the related table.
For example, if a foreign key in a table references a Customer ID in a customer table, referential integrity ensures that the customer ID exists.

Why Referential Integrity Matters

Inconsistent or invalid relationships between tables can lead to various issues in data analysis, reporting, and application functionality. Without referential integrity:

• Orphaned record may exist, leading to in accurate data insights.

• Applications that rely on relational data can encounter errors or unexpected behavior.

• Data quality deteriorates, making it harder to maintain trust in the system.

Active and Inactive Relationships

Active relationship

The active relationship is the primary relationship that is used by Power BI to calculate and filter data automatically.

Inactive Relationship

An inactive relationship is a secondary relationship activated with the Power BI DAX function temporarily whenever there is a need to perform a specific calculation.

Filter Direction

It refers to the way filters propagate between related tables in your power BI data model.

• Single Direction
  Filter flow in one direction, from one table to another.

• Both Direction (Bi-Directional)
  Filter flow in both directions between the two tables.

Why Filter Direction Matters

It impacts how your visuals and calculations behave in Power BI. It determines:

1. Data Filtering:
   Which rows are included or excluded in your reports based on related data .

2. Performance:
   Single-Direction filters are generally more efficient, while bi-directional filters can increase complexity and computational load.

3. Cross-Filtering relationships:
   Whether interactions between tables are undirectional or bidirectional.

When to use Bi-Directional Filtering
Useful in scenarios like:

• Many-to-Many relationship

• Creating detailed reporting where multiple table need simultaneous filtering.

• Solving specific modelling challenges where single-direction filters don't suffice.

Key Takeaways

• Use Single-Direction filters by default for simplicity and performance.

• Apply bi-directional filters only when necessary and with caution.

• Always test your model to ensure filter directions work as intended without causing errors or performance degradation.

Joins in Power Query

Power query is a key tool for Power BI users. It help transform and combine data from different sources. With its simple features, it facilitates cleaning, formatting, and restructuring data before intergration into Power BI.

Aa major function of the power query is the ability to perform joins between tables.
Joins are crucial for uniting data from various sources. They allow you to create coherent and useful data for your analyses.

Different Types of Joins in Power Query

Power Query allows combining data from different sources. There are several types of joins. Each type has its own uses.

1.Left Outer Join

The left outer join includes all record from the left table. Records without a match have null values for the right table.

2.Right Outer Join

The right outer join includes all records from the right table. Records without a match have null values for the left table.

3.Full Outer Join

The full outer joins combines the results of left and right outer joins. It includes all records from both tables. Records without a match have null values.

4.Inner Join

The inner join is widely used. It combines records from two tables by a common key
Only matching row included.

5.Left Anti Join

It returns only the contents that are contained in the lef table.

6.Right Anti Join

It returns only the contents that are contained in the right table.

Power Query Joins vs Power BI Relationship

Merging in Power Query and creating a relationship in the data model solve similar-sounding problems — combining information from two tables — but they work at different stages of the Power BI workflow and have very different consequences for the model.

Excessive merging pushes a model back toward the flat-table pattern discussed in Section 1: every merge duplicates columns from one table across another, increasing redundancy and file size while making the model harder to maintain (a change to a customer's name would need to be re-merged everywhere it was copied). Keeping fact and dimension tables separate and joined only by relationships preserves the storage efficiency of the star schema, keeps each table focused on a single business concept, and lets Power BI's engine optimise filtering between them — something it cannot do once the data has already been flattened into one table by a merge.

In short: use a merge in Power Query only when a query genuinely needs columns from two sources combined at the row level (for example, enriching a staging table before it is split into proper fact and dimension tables); use a relationship in the model for everything else.

Recommended Power BI Model

For a typical business intelligence project, a star schema, connected by one-to-many relationships filtering in a single direction from dimensions to the fact table, is the recommended design.
A good Power BI model often follows the Star Schema. That means:

• One central fact table

• Several surrounding dimension tables

All relationships are one-to-many, going from dimension to fact

Why do we like this structure?

✅ Better performance

✅ Easier to understand and visualize

✅ Works well with how Power BI handles filter context and measures

Think of it like this: your fact table holds the numbers (sales, events, transactions), while your dimension tables give those numbers meaning (products, dates, customers).

Avoid the “Snowflake Schema” unless you really need it. It might look neat with all those normalized layers, but it adds complexity, slows things down, and makes debugging harder.

When in doubt, keep it simple. A star is all you need.