Data Modelling, Relationships & Joins in Power BI

Data Modelling in Power BI

Data modelling in Power BI is the process of connecting separate data sources, defining logical relationships between entities, establishing directional filter propagation paths and configuring metadata to transform raw transactional records into an analytical engine.
Data Modeling helps in:

• Reporting & Analytics: Enables seamless aggregation across diverse business dimensions without requiring complex manual query rewrites.
• DAX Calculations: Simplifies DAX expression logic, reducing the reliance on filter-overriding functions like CALCULATE, FILTER, or CROSSFILTER.
• Scalability & Maintainability: Allows effortless additions

Comparison of Architectural Schemas

Introduction

Schema refers to the logical structure of a database or data model that defines how tables are organized and related. In Power BI, schemas are used to optimize data storage, retrieval, and reporting.
There are three primary types of schemas commonly used in Power BI:

Flat Table Schema

This is a single denormalized data table containing all transactional attributes, metrics, keys, and lookup descriptors within one wide structure. It organizes all information, such as transactions, customer names, and product details, into a single, wide table with no external relationships.

Structure: All transactional measures and descriptive text fields are repeated for every row.
Advantages: Very simple to set up, requires zero data modeling effort and works well for small datasets.
Disadvantages: Massive data redundancy, bloated file sizes and high risk of update anomalies.
Appropriate For: Increases memory consumption and slows down performance on large datasets because repeated text bloats file size.
Performance & Complexity: Very low structural complexity but severely degrades performance and memory efficiency as row counts grow.
A Flat Table Schema is as below:

Star Schema

A star schema is a more advanced approach to structuring and organizing quantitative, or measurable, data in Power BI. It allows for multiple tables to be connected through one central table, known as the fact table. The fact table is linked to dimension tables that contain records such as customers, employees, dates, and marketing campaigns etc.

Advantages of using Star Schema in Power BI

• Reduces Data Redundancy: By separating facts and dimensions into distinct tables.
• Boosts Query Performance: More efficient querying and aggregation of data, as dimension tables are typically smaller and can be indexed to improve performance.
• Easy to Understand: The fact table serves as the focal point, and all other tables are linked to it through clear relationships

Disadvantages of using Star Schema in Power BI

• Lacks Flexibility: Actions like adding new dimensions or modifying existing ones may require extensive changes to the schema.
• Struggles with Complexity: May not be suitable for handling complex relationships between dimensions.

Star schema is structured as shown below:

Snowflake Schema

A snowflake schema is an extension of the star schema. It breaks down the dimension tables into multiple related tables, forming a hierarchical structure that resembles a snowflake. This process, known as normalization, reduces data redundancy and improves data integrity.

Advantages of using Snowflake Schema in Power BI

• Efficient Storage: Reduced data redundancy through normalization.
• Improved Data Integrity: Better data integrity due to more focused tables.
• Offers Scalability: Greater flexibility in handling complex relationships between dimensions, as new tables can be easily added to accommodate new relationships.

Disadvantages of using Snowflake Schema in Power BI

• Complex Data Analysis: More difficult to perform data analysis because of the extra relationships.
• Challenging to Understand and Manage: The schema is harder to understand and maintain due to its complexity.
• Slower Queries: Multiple join operations may be required to retrieve data from the related tables, resulting in slower query performance.

Snowflake schema structure is as in the sample image below.

Fact Tables and Dimension Tables

Separating tables into Facts and Dimensions is the foundation of dimensional modeling. Fact tables capture business measurements, events, and transactions, while Dimension tables store the context, descriptive attributes, and filtering hierarchies.

• Fact tables consists of the measurements, metrics, or facts of a business process. In other words, they hold quantifiable measurable data. They represent numerical business actions that can be summed, averaged, or counted. Example: order id, product id, product price, quantity.

• Dimension tables are typically textual fields and provide descriptive attributes related to fact data. They contain textual attributes, categorical groupings, hierarchies and primary surrogate keys. Dimension tables are linked to the fact table and include things such as date, employee, sales, and product data.

The below image shows a practical example of how a single Fact Table can be linked to several Dimension Tables.

Relationships in Power BI

Relationships establish logical connections between physical tables in the data model based on matching key columns. They govern how filter context propagates across tables when report visuals evaluate.

Relationship Cardinalities

• One-to-Many (1:) / Many-to-One (:1) A single distinct key value in the dimension table maps to zero, one, or many identical key instances in the fact table. Practical Example: DimCustomerCustomerKey connected to FactSalesCustomerKey. This is the recommended primary relationship pattern for analytical modeling.

• One-to-One (1:1) A single row in Table A maps to exactly one corresponding row in Table B. Practical Example: DimUser[UserID] connected to DimUserProfile[UserID]. Usage: Rarely needed. One should prefer merging both tables into a single table in Power Query, unless separating highly sensitive security attributes.

• Many-to-Many (:) In this kind of relationship, neither table contains unique key values; keys repeat across both sides of the relationship. Practical Example: Linking sales targets set at a high product category level directly to transactional sales containing product line items. Usage guidance: It is advisable to avoid this relationship where possible. It introduces non-deterministic visual calculations. Use a table with two 1:* relationships instead.

Key Relational Concepts

• Primary Key (PK): A column in a dimension table containing strictly unique values that definitively identify each row (e.g., DimCustomer[CustomerKey]).
• Foreign Key (FK): A column in a fact table that references a Primary Key column in a dimension table (e.g., FactSales[CustomerKey]).
• Unique Values: A strict requirement for the '1' side of any 1:* relationship; duplicate values on the 1 side, trigger modeling errors.
• Referential Integrity: Ensures that every foreign key value in a fact table correctly matches an existing primary key in the corresponding dimension table.
• Active vs. Inactive Relationships: Only ONE active relationship path can exist between two tables to prevent ambiguous paths. Additional relationships (e.g., FactSales[OrderDate] vs FactSales[ShipDate] linking to DimDate[Date]) must remain inactive and activated dynamically using USERELATIONSHIP() in DAX.

Filter Direction & Propagation

When a user selects an item in a slicer or visual, Power BI establishes a Filter Context. This filter automatically propagates along active relationships between related tables.

Single-Direction vs. Both/Bidirectional Filtering

• Single-Direction Filtering (1 -> *): Filters propagate exclusively from the '1' side (Dimension) to the '*' side (Fact). Selecting DimProduct[Category] = 'Bikes' automatically filters FactSales down to rows containing bike sales. Selecting values in FactSales does NOT filter DimProduct.

• Both / Bidirectional Filtering (<->): Filters propagate in both directions across the relationship. Filtering FactSales automatically filters DimProduct, and vice versa.

Risks of Bidirectional Filtering: Bidirectional relationships can create circular, ambiguous filter paths in non-trivial models. They cause unexpected measure calculation results, prevent efficient query plan caching, and significantly degrade execution performance. The best practice is to keep relationships single-direction and activating cross-filtering explicitly via DAX (CROSSFILTER) only when required.

Joins in Power Query

Power Query utilizes Merge Queries to physically combine two tables side-by-side during the ETL extraction and transformation phase. Rows are matched based on specified key columns from both tables.

There are several types of Joins and to explain them better it would be good to use a practical example for ease of understanding and reference.
Practical Scenario Datasets
Consider two sample tables, Customers (Left Table) and Orders (Right Table) as show in the image below:

Join Type Breakdown:

• Left Outer Join: Retains ALL rows from the Left table (Customers) and matching rows from the Right table (Orders). Unmatched right attributes appear as null.
• Right Outer Join: Retains ALL rows from the Right table (Orders) and matching rows from the Left table (Customers). Unmatched left attributes appear as null.
• Full Outer Join: Retains ALL rows from BOTH tables. Unmatched rows on either side are filled with null values.
• Inner Join: Retains ONLY rows where matching keys exist in BOTH tables (e.g., CustomerID 1 and 2).
• Left Anti Join: Retains ONLY rows from the Left table that have NO match in the Right table (e.g., CustomerID 3 - Charlie).
• Right Anti Join: Retains ONLY rows from the Right table that have NO match in the Left table (e.g., OrderID 103 - CustID 4).

Power Query Joins vs Power BI Relationships

Understanding the architectural distinction between merging tables in Power Query and creating relationships in the Power BI Data Model is essential for efficient Power BI design.

Recommended Power BI Architectural Standard

After doing deep research, I strongly recommend a Star Schema utilizing Single-Direction One-to-Many (1:) relationships.
**Justification*:

• Query & Report Performance: Maximizes VertiPaq columnar dictionary encoding and segment compression, ensuring visuals render in sub-seconds.
• DAX Simplicity: Measures evaluate over clean, single-direction filter propagation paths, eliminating the need for complex filter-overriding functions.
• Model Readability: Provides an intuitive visual structure where fact tables sit in the center surrounded by clean dimension lookup tables.
• Scalability: New transactional fact tables (e.g., FactBudget or FactForecast) can immediately integrate with existing dimensions without refactoring.
• Maintainability: Prevents circular relationship paths, ambiguous filter routes, and unnecessary memory consumption.

Learning is a continuous thing and I am learning, leave a comment on sites I can use to learn in this journey of becoming a Data Scientist