Power Bi: Data Modelling, Relationships and Joins

Introduction

• Picture this measures that return the wrong totals, slicers that don't filter what you expect, and reports that slow down as data grows, this can usually be traced back to how tables were shaped and connected. Most Power BI issues that look like DAX problems are mostly caused by flaws in the data model.

Data Modelling

• Data modelling in power bi is the process of organizing your data into tables and defining how those tables relate to each other so you can analyze it accurately and efficiently. Think of it as drawing the blueprint for your data before you build anything on top of it.
• You may be wondering why a well designed data model matters. The key reasons are explained below:
  1. Reporting and accuracy - correct relationships ensure visuals pull the right data and also prevents duplicating values in reports.
  2. Performance - well structured model reduces query complexity and speeds up reports load time with quicker dashboard interactions.
  3. Analytics - selecting the appropriate data model with the right relationships minimizes logic errors, ensuring that every derived metric produces accurate insights.
  4. DAX Calculations - a good model makes it easy to write, interpret, and maintain calculations since the calculations rely heavily on good relationships in order to function correctly.
  5. Maintainability - makes it easy to edit, and troubleshoot errors when need be without the hustle of making changes in individual records
  6. Scalability - clean models handle growing data volumes well without major rework ensuring that new data can be integrated smoothly.

Data Modelling Approaches in Power Bi

• Refers to the process of organizing tables and defining the relationships between tables so that you can use Power Bi to analyze, filter and summarize data correctly .
• Reasons for structuring are:
• Clean business structure- splits data and puts it in places based on the business it self
• To ensure information shared on Power Bi can enable it work efficiently and correctly so that you get fast performance and nice response
• I will describe the three main approaches frequently used:

Flat Table

• It refers to a single table that stores all the data together, both the facts and descriptive context with no relationships to other tables.
• It is mostly appropriate when dealing with a small simple dataset where data does not require complex relationships The table above represents a flat table containing the details of customers who bought products in a store

Advantages of Flat Table

1. It is simple to build and understand.
2. It is easy to import and load data since its a single table.
3. No relationships created hence no relationship errors.

Disadvantages of a Flat Table

1. High data redundancy due to repeated values across rows.
2. DAX functions are difficult to manage since data is not separated.
3. Slower performance on large datasets due to the file size and lack of compression efficiency.

Star Schema

• It is represented by one fact table connected to dimension tables that are directly connected to the facts table.
• The main structure is that it radiates outward like a "star" comprises of a fact table with several dimension tables surrounding it, each having a particular relationship. The diagram represents an example of a star schema where the Sales is the fact table while the other tables are dimensional tables

Advantages of Star Schema

1. Gives a clean way to filter data that is a clean filter path.
2. Makes it easy to use DAX functions since data is well stored in different tables.
3. Makes it easy to build visuals (charts) since numbers are all found in facts table while the labels are in dim table.

Disadvantages of Star Schema

1. Can have data redundancy within dimension tables when they are not properly normalized
2. Requires the user to have knowledge on building and managing relationships for efficient use.
3. Requires the user to understand concepts on grains, keys, cardinality and filter direction in order to build the model correctly.

• The star schema is mostly appropriate to use on standard business reporting scenarios such as sales operations

Snowflakes Schema

• It is similar to a star schema however, the dimension tables are further broken down into smaller sub dimension tables giving more information about them.
• It creates a branching "snowflake-like" structure since the fact table connects to primary dimension tables which link further to smaller tables. The diagram represents a snowflakes schema where the Sales is the fact table while the rest are dimension tables where product and customer tables have sub-dimensional tables

Advantages of Snowflakes Schema

1. Reduces data redundancy through normalization of all the tables across multiple levels.
2. Makes it easy to edit data since changes are made in the respective tables in stead of interfering with the whole dataset.
3. Organizes complex hierarchies clearly for large datasets.

Disadvantages of Snowflakes Schema

1. Increased query complexity due to more table and joins required for instance an error in one dimension table affects the entire dataset.
2. It can be harder to write and understand DAX calculations due to the added table layers.
3. It is more complicated to understand and work with.

• The snowflakes schema is most appropriate to use for complex hierarchical data with many sub-levels.

Facts and Dimension Tables

Facts Tables

• It is a table that stores quantitative records of a business events or transactions. It tends to describe what happened.

• The facts table mainly contains:

  1. The foreign keys linking it to other dimension tables for example CustomerId, ProductId
  2. Numeric data that can be used to perform calculations using DAX functions such as Sum, Count and Average
  3. Dates marking when the event/transaction occurred.

• It is important to note that the duplicates in the facts table are not removed because every record in it is unique.

• It is usually the largest table in the model since it grows continuously with new transactions.

Examples of a Fact Table

1. FactSales — contains sales amount, quantity sold, discount, date key, product key, customer key
2. FactOrders — contains order value, order quantity, order date, customer key
3. FactTransactions — contains transaction amount, transaction type, date, account key The above shows a simple fact table showing sales made

Dimension Table

• It stores attributes that describes the context about the business events/transactions. That is, it explains the who, what, where, and when behind the facts.
• The dimension table mainly contains:
  1. Text based descriptive fields eg names, categories and labels
  2. Primary keys which are the unique identifiers which connect to the fact table as foreign keys.
• Always remove duplicate records in the dimension table to avoid data redundancy.
• Attributes are used for filtering, grouping, and slicing data.

Examples of a Dimension Table

1. DimCustomer — contains customer name, age, gender, location
2. DimProduct — contains product name, category, brand, color, size The above shows a dimension table showing customer details

Granularity/Grains

• It is the lowest level of detail represented by a single row in a data table describing what each record of a table consists of.
• The granularity of the table below indicates that each record represents a unique store, described by its storeId, storeName, city, and country of location.

Relationships in Power Bi

• Refer to connection between two tables that defines the rules, helping Power Bi combine data from both tables in the visual layer(front end).
• We establish relationships by matching primary keys of a dimension table to the corresponding foreign key in a facts table.
• In order to build relationships, you have to define the keys, the cardinality(values inside the keys) and filter direction.

Why are relationships necessary?

1. Avoids data duplication eg a product's category will not be repeated in every sales row.
2. Easier maintenance for instance you update a customer's address once not in every transaction row.
3. Enables cross table analysis by allowing you to filter data by different categories.

Cardinality In Data Modelling

• Refers to how data in one table relates to data in another table based on the numerical count of the matching rows.
• There are two main types:
• Column cardinality which is the uniqueness of data in a single column
• Relationship cardinality describes how many instances one entity can be associated another entity.

One-to-One (1:1)

• It exists when a single row in a table only links to one row in another table and vice versa.
• A real life example is where one person can hold only one active passport at a time and that specific passport belongs to only one person.

One-to-Many (1:*)

• It is the most common type of relationship used.
• It exists when a single row in a table matches multiple rows in another table.
• The "one" side is typically the dimension table (Products, Customers) while the "many" side is the fact table (Sales, Transactions). The above illustration implies that one customer in the dimension table can have multiple purchases in the sales table.

Many-to-Many (:)

• It exists when several rows in one table can link to multiple rows in another table.
• The main challenge is that Power BI struggles to determine clear filter paths for this relationship type which leads to unexpected aggregation errors.
• The best solution for this is creation of a separate bridge/join table containing unique values of the linking ID from both original tables.
• A real world example is a school scenario where one student can take many courses and one course can have many students. Connecting Students and Courses directly isn't ideal, since it creates ambiguous filtering. Instead, a bridge table (course roster)is introduced to link them, turning the single many to many relationship into two well defined one to many relationships.

Key Concepts In Power Bi

• Primary keys - a column that uniquely identifies each record in a table, no two rows can share one key and it can't be null.
• In DimCustomer table, CustomerID is the primary key — one row per customer, each ID appears exactly once.
• Foreign keys - a column in one table that refers to the primary key of another table.
• In FactSales, CustomerID is a foreign key referencing DimCustomer table ID. Unlike in the dimension table, this same CustomerID can and should appear many times in FactSales, because a customer places many orders.
• Unique values - describes a column where no value repeats.
• In a one to many relationship, the "one" side needs unique values, while the "many" side can repeat those values multiple times.
• Referential integrity - is the rule that a foreign key value must always correspond to an existing primary key value in the referenced table, orphan keys result to blank rows or missing matches in your dataset.
• Active and Inactive Relationships - for power bi, two tables can be connected through more than one path but only one relationship can be "active" at a time to avoid ambiguity.
• Active relationships are shown as a solid line in the model view while inactive ones are shown as a dashed line.

Filter Direction

• Filter propagation shows that relationships between tables control how a choice made in one table affects the data shown in related tables that is, when data is filtered in a table the changes also affect the other table.
• In power bi, there are two main types of filters namely: Single-Direction Filtering
• Here filters move from the "one" side of the relationship to the "many" side, but not the other way. The above model demonstrates a single filter from ProductsTable to SalesTable. When a user filters a product such as "milk" from ProductTable, Power BI narrows ProductsTable down to that product (milk) and passes this filter across to SalesTable. As a result, any chart or total based onSalesTable, such as total revenue or units sold, now only shows data for the "milk" product.

Both/Bidirectional filtering

• Here filters move in both directions from the dimension table to the fact table, and back again from the fact table to the dimension table.
• Though this may seem convenient for filtering, it poses possible problems such as:
  1. Confusing paths – With multiple bidirectional relationships, Power BI may not know which route a filter should take, leading to blocked relationships or unexpected results.
  2. Harder to follow – It becomes difficult to predict how selecting one value affects the rest of the report, unlike single-direction filtering, which always flows the same way.
  3. Slower performance – Checking filters in both directions takes more processing power, which can slow down larger reports.

Joins in Power Query

• Joins combine two tables using matching columns, the matching columns are identified using primary and foreign keys.
• In Power Query joins are performed using merge queries tool. To demonstrate this I will use two table, the first one a CustomerTable and the second one an OrdersTable

CustomersTable

OrdersTable

Left Outer Join

• Keeps all the rows from the first table and the matching rows from the second table
• Records retained- all of Customers, plus matching Orders data. Unmatched customers get blanks (null) for the order columns.

Right Outer Join

• Keeps all the rows from the second table and only the matching rows from the first table
• Records retained- all the data from Orders, plus matching Customers data. Unmatched orders get blanks for the customer columns.

Inner Join

• Keeps only the rows where the key exists in both tables.
• Records retained- just the matched intersection hence no blanks anywhere in the result.

Full outer Join

• Keeps every row from both tables, matching where possible and filling blanks where there is no match on either side.
• Records retained- the union of both tables that is everything from Customers and everything from Orders.

Left Anti Join

• Keeps only the rows in the first table that have no match in the second table.
• Records retained- customers with zero orders and no columns from Orders are brought in at all since there is nothing to match.

Right Anti Join

• Keeps only the rows from the second table that have no match in the first table
• Records retained- Orders that do not belong to any known customer in the Customers table. This is useful for spotting orphaned or bad data.

Power Query Joins vs Power BI Relationships

• A merge combines data from two tables during the data preparation stage in Power Query meaning it physically combines data while a relationship connects two separate tables in the data modelling stage. It does not physically combine the tables.
• You chose merging over relationships when the two tables describe the same entities eg customer_email and customer_info while yo chose to build relationships if the tables describe different entities eg sales and customer_info.
• Excessive merging affect the data model by making it larger, more complicated, and harder to maintain due to:
  • More data redundancy since the same information is repeated many times
  • Poor readability as it becomes harder to understand what each column represents.
  • Harder maintenance as changes to one type of information may require changes to a large table.

Recommended Power BI Model

• For a real world business intelligence project, I would have the Star Schema as my modelling approach of choice while for the relationship choice, I would use the One to Many relationship with Single direction filtering. This is because of the following reasons:
• Query and report performance- a well designed star schema model works efficiently on power bi because of its ability to keep dimensions relatively wide and the fact table focused on transactional data.
• DAX simplicity- measures are easier to write because dimensions provide clear filtering paths to the fact table
• Data redundancy- descriptive attributes are stored once in each dimension, not repeated in the fact table.
• Scalability - new dimensions or additional fact tables can be added without redesigning the entire model with ease.
• Maintainability- separating business events from descriptive information makes it easier to modify dimensions and fact tables independently.
• Ease of creating reports- report developers can drag fields from dimensions into slicers and filters and use measures from fact tables for calculations.
• Model Readability- a star schema is easy to understand since the user can immediately identify which tables contain facts and which contain descriptive attributes.

Conclusion

• Effective Power BI modelling is the foundation of a reliable and maintainable report. A well-designed star schema, clear relationships, and properly structured fact and dimension tables simplify DAX, improve performance, and support accurate business insights. Ultimately, a strong data model turns raw data into trusted decisions