Introduction
Data does not always come in a structure that is ready for analysis.
For example, sales information may be stored separately from customer details, product information, dates and locations.Before creating reports, visuals or doing calculations in Power BI, it is important to understand how these different tables should be organised and connected.
Data Modeling
Data modelling is the process of organising data into a structure that makes it easier to analyse and report on.
A well-designed data model helps Power BI understand how information from different tables is connected, allowing filters, calculations and reports to work correctly. The way a model is designed can also affect the performance, scalability and maintainability of a Power BI report.
Flat Table
A flat table is a simple way of organising data where most or all the information is stored in one table.
This is common in spreadsheet tools such as Microsoft Excel, where a dataset may contain multiple types of information in the same table.
For example, in an e-commerce business, one table could contain information such as the Order ID, date, customer name, customer email, address, product name, product category, unit price, quantity and total sales amount. Each row may represent a transaction or an individual product within a transaction.
While this structure can be easy to understand and work with, problems can start to appear as the data grows.
Customer and product information may be repeated across many rows. For example, a customer who has placed several orders may have their name, email and address repeated for every transaction.
This repetition increases the size of the dataset and can make it more difficult to maintain.
If customer or product information needs to be updated, the same change may need to be made in multiple records.
As the dataset becomes larger, a flat table can also become less efficient and more difficult to manage compared to a properly structured data model.
| Order Id | Date | Customer name | Customer email | Address | Product name | Product Category | Unit Price | Quantity | Total Sales Amount |
|---|---|---|---|---|---|---|---|---|---|
| S001 | 25/08/2005 | Joseph Kamau | josephk@email.com | Nairobi | Mouse | Electronics | Kes 250 | 10 | Kes 2500 |
| S002 | 26/08/2005 | Mitchell Faraja | mitchellf@email.com | Mombasa | Laptop | Electronics | Kes 20000 | 2 | Kes 40000 |
| S003 | 27/08/2005 | Joseph Kamau | josephk@email.com | Kisumu | Keyboard | Electronics | Kes 500 | 5 | 2500 |
Advantages
- Easy to understand initially
- Simple for very small datasets
- No relationships required
- Quick to create
Disadvantages
- Repeated data
- Larger storage requirements
- Difficult to maintain
- Less scalable
- Can create unnecessary redundancy
The Limitations of a Flat Table
Consider an e-commerce dataset where sales transactions, customer information and product details are all stored in one large worksheet.
If a customer named Joseph Kamau buys ten different products, his name, email address and address may be repeated across all ten records.
This creates duplication of information. It can also create problems when information needs to be updated. For example, if Joseph changes his address and only some of his records are updated, the dataset wil contain different addresses for the same customer. This can affect the accuracy and consistency of the data.
As the dataset grows, storing everything in one wide table can also make analysis more difficult and less efficient. The same customer and product information may be repeated thousands or even millions of times. Calculations such as counting unique customers or analysing average sales per customer may therefore require Power BI to process a large amount of repeated information.
One way to solve this is by separating the data into different tables based on the type of information being stored. Instead of storing Joseph's details in every sales record, his information can be stored once in a Customers table. The Sales table would then contain a CustomerID that connects each sale to the correct customer.
The same approach can be used for products. Product information can be stored in a separate Products table, while the Sales table contains the ProductID, quantity purchased and transaction details. This reduces unnecessary repetition and creates a more organised structure where customer and product information can be managed separately from sales transactions.
Customers Table
| CustomerID | CustomerName | Address | |
|---|---|---|---|
| C001 | Joseph Kamau | josephk@email.com | Nairobi |
| C002 | Mitchell Faraja | mitchellf@email.com | Mombasa |
Products Table
| ProductID | ProductName | Category |
|---|---|---|
| P101 | Laptop | Electronics |
| P102 | Mouse | Electronics |
| P103 | Keyboard | Electronics |
Sales Table
| OrderID | Date | CustomerID | ProductID | Quantity | Unit Price |
|---|---|---|---|---|---|
| S001 | 25/08/2005 | C001 | P102 | 10 | 250 |
| S002 | 26/08/2005 | C002 | P101 | 2 | 20000 |
| S003 | 27/08/2005 | C001 | P103 | 5 | 500 |
Once the data has been separated into different tables, the next step is to understand the role of each table. Not every table in a data model serves the same purpose. In the example, the Customers table contains information about customers, the Products table contains information about products, while the Sales table records the actual transactions that take place.
This leads to two important types of tables used in analytical data models: fact tables and dimension tables.
A fact table records business events or transactions that the organisation wants to analyse. In the example, the Sales table can be treated as the fact table because each row represents a sale. It contains information such as the transaction date, CustomerID, ProductID, quantity and unit price.
For example:
| OrderID | Date | CustomerID | ProductID | Quantity | Unit Price |
|---|---|---|---|---|---|
| S001 | 25/08/2005 | C001 | P102 | 10 | 250 |
| S002 | 26/08/2005 | C002 | P101 | 2 | 20000 |
| S003 | 27/08/2005 | C001 | P103 | 5 | 500 |
The Customers and Products tables, on the other hand, provide descriptive information about the entities involved in those sales. These are known as dimension tables.
The Customers table contain:
| CustomerID | CustomerName | Address | |
|---|---|---|---|
| C001 | Joseph Kamau | josephk@email.com | Nairobi |
| C002 | Mitchell Faraja | mitchellf@email.com | Mombasa |
The Products table could contain:
| ProductID | ProductName | Category |
|---|---|---|
| P101 | Laptop | Electronics |
| P102 | Mouse | Electronics |
| P103 | Keyboard | Electronics |
The main difference is that the fact table tells us what happened, while the dimension tables provide the context needed to understand and analyse what happened. For example, FactSales can tell us that a sale of KES 2500 occurred, while DimCustomer tells us who made the purchase and DimProduct tells us what was purchased.
This separation also helps reduce unnecessary duplication. Instead of storing Joseph's name, email and location in every sales record, the Sales table only needs to store her CustomerID. The same applies to products, where the Sales table stores ProductID instead of repeating the product name and category.
The tables can then be connected using keys. CustomerID is the primary key in DimCustomer and acts as a foreign key in FactSales. Similarly, ProductID is the primary key in DimProduct and a foreign key in FactSales.
Grain
Grain describes the level of detail represented by each row in a fact table. In simple terms, it answers the question: what exactly does one row represent?
For example, in an e-commerce FactSales table, the grain could be defined as one row representing one product sold as part of a sales transaction.
Using this grain, a single order containing three different products would result in three rows in the fact table:
Order O001
Laptop → 20000
Mouse → 250
Keyboard → 500
Each row represents a specific product within that transaction, allowing measures such as quantity sold and sales amount to be analysed at the product, customer or transaction level.
Defining the grain before building a fact table is important because it ensures that the data is stored at a consistent level of detail. It also helps prevent incorrect calculations, such as counting transactions multiple times or combining data that is recorded at different levels.
The grain does not have to be the same for every fact table. For example, one fact table could record individual sales transactions, while another could record daily inventory levels. What matters is that the grain of each fact table is clearly defined and consistently maintained.
Primary Keys and Foreign Keys
After separating the data into different tables, the next question is how those tables will be connected. This is where primary keys and foreign keys become important.
A primary key is a column that uniquely identifies each record in a table. For example, in a DimCustomer table, CustomerID can be used as the primary key because each customer should have their own unique CustomerID.
For example:
| CustomerID | CustomerName | |
|---|---|---|
| C001 | Joseph Kamau | josephk@email.com |
| C002 | Mitchell Faraja | mitchellf@email.com |
In the example, CustomerID is unique for every customer. The same CustomerID should not identify two different customers in the dimension table.
A foreign key is a column in another table that refers to the primary key. In the FactSales table, CustomerID acts as a foreign key because it connects each sale to the customer who made the purchase.
For example:
| SalesID | CustomerID | ProductID | SalesAmount |
|---|---|---|---|
| S001 | C001 | P102 | 250 |
| S002 | C002 | P101 | 20000 |
| S003 | C001 | P103 | 500 |
Here, C001 appears several times because Joseph has made multiple purchases. This is different from the DimCustomer table, where C001 appears only once.
This creates an important pattern in Power BI: the primary key is normally found on the one side of a relationship, while the foreign key appears on the many side. In this example, one customer can have many sales.
DimCustomer FactSales
CustomerID CustomerID
C001 ─────────────────────── C001
C002 ─────────────────────── C001
C003 C002
C001
The keys therefore provide the link between the descriptive information in the dimension table and the transactions stored in the fact table.
Cardinality
After identifying the keys that connect tables, the next thing to understand is cardinality. Cardinality describes how many records in one table can be related to records in another table.
There are three main types of cardinality that are commonly used in Power BI: one-to-many, one-to-one and many-to-many.
One-to-Many (1:*)
One-to-many is the most common relationship in a Power BI data model. It means that one record in one table can be related to many records in another table.
For example, one customer can make multiple purchases. In this case, DimCustomer is on the one side and FactSales is on the many side.
DimCustomer FactSales
CustomerKey CustomerKey
C001 ────────────────────────▶ C001
C002 ────────────────────────▶ C001
C003 ────────────────────────▶ C002
C001
The CustomerKey appears only once for each customer in DimCustomer, but the same CustomerKey can appear many times in FactSales as the customer makes different purchases.
Filters can also flow from the dimension table to the fact table. For example, if a report user selects Nairobi from DimCustomer[Address], Power BI can use the relationship to filter the relevant records in FactSales.
One-to-One (1:1)
A one-to-one relationship means that each record in one table is related to only one record in another table.
For example, an organisation might have an Employee table and a separate EmployeeConfidentialInformation table. Each employee would have one corresponding record in each table.
Employee EmployeeConfidential
EmployeeID EmployeeID
E001 ───────────────────▶ E001
E002 ───────────────────▶ E002
E003 ───────────────────▶ E003
Many-to-Many (:)
A many-to-many relationship occurs when multiple records in one table can be related to multiple records in another table.
For example, a student can take several courses, while each course can have many students.
Students Courses
Student A ────────────────▶ Course 1
Student A ────────────────▶ Course 2
Student B ────────────────▶ Course 1
Student B ────────────────▶ Course 3
Active and Inactive Relationships
Power BI relationships can be either active or inactive. An active relationship is the relationship Power BI uses by default when filters and calculations are applied between two related tables. An inactive relationship exists in the model but is not used automatically.
A common example occurs when a fact table contains more than one date that needs to be analysed. For example, a FactSales table may contain both OrderDate and ShipDate, while the model has a DimDate table.
┌──────────────┐
│ DimDate │
└──────┬───────┘
│
Active │
▼
OrderDate
┌──────────────┐
│ FactSales │
└──────────────┘
▲
Inactive │
│
ShipDate
The OrderDate relationship could be active, meaning it is used automatically when a date filter is applied. The relationship between DimDate and ShipDate can remain inactive because both relationships cannot normally be active between the same two tables at the same time.
This does not mean the inactive relationship is useless. It can be used when a particular analysis requires it. For example, a report may normally analyse sales based on the date an order was placed, but a separate measure may need to analyse sales based on the date the order was shipped.
DAX provides the USERELATIONSHIP() function to temporarily use an inactive relationship within a calculation. This allows the same DimDate table to support different date-based analyses without creating unnecessary duplicate tables.
For example, the model could use the active OrderDate relationship for normal sales analysis, while a separate measure uses the inactive ShipDate relationship when analysing shipped orders.
Active and inactive relationships are therefore useful when a fact table contains multiple fields that could relate to the same dimension.
The Star Schema
A star schema is a data model where a central fact table is connected directly to several dimension tables. The fact table sits at the centre, while the dimension tables surround it, creating a structure that looks like a star.
For example, in an e-commerce model, the FactSales table could contain information about sales transactions such as SalesAmount, Quantity, CustomerKey, ProductKey and DateKey. It can then be connected directly to dimension tables such as DimCustomer, DimProduct and DimDate.
┌─────────────────┐
│ DimCustomer │
└────────┬────────┘
│ 1
│
│ *
┌─────────────────┐ ┌────▼────────────┐ ┌─────────────────┐
│ DimDate │ 1 * │ FactSales │ * 1 │ DimProduct │
└─────────────────┘─────►│ │◄────└─────────────────┘
└─────────────────┘
The relationships are normally one-to-many (1:*), with the dimension table on the one side and the fact table on the many side. For example, one customer can have many sales transactions, so DimCustomer is on the one side while FactSales is on the many side.
Importance of a Star Schema
Simpler analysis:
Dimensions provide the descriptive information used to filter and group data, while the fact table contains the numbers and events being analysed. For example, a user can select a product category from DimProduct and analyse the corresponding sales from FactSales.
Better report performance:
A well-structured star schema reduces unnecessary relationships and keeps the model relatively simple. This can help Power BI process queries and DAX calculations more efficiently, especially as the amount of data increases.
Easier to use:
Report developers can easily identify where different types of information belong. Customer attributes are in DimCustomer, product attributes are in DimProduct, dates are in DimDate, and transaction measures are in FactSales.
Easier to maintain:
Because each table has a clear purpose, changes to the model are easier to manage. New descriptive attributes can be added to the appropriate dimension without unnecessarily changing the structure of the fact table.
Works well with DAX and filtering:
The clear separation between dimensions and facts makes it easier to create measures and understand how filters affect the results. For example, selecting a specific product category can filter the related sales records through the relationship between DimProduct and FactSales.
The Snowflake Schema
A snowflake schema is a variation of the star schema where dimension tables are further divided into smaller related tables. This process is known as normalization.
For example, instead of keeping the product category and sub-category information directly inside DimProduct, they can be separated into their own tables. DimProduct would connect to DimSubCategory, which would then connect to DimCategory.
┌─────────────────┐
│ DimCategory │
└────────┬────────┘
│ 1
│
│ *
┌────────▼────────┐
│ DimSubCategory │
└────────┬────────┘
│ 1
│
│ *
┌────────▼────────┐
│ DimProduct │
└────────┬────────┘
│ 1
│
│ *
┌────────▼────────┐
│ FactSales │
└─────────────────┘
Using the e-commerce example, DimProduct might contain ProductID, ProductName and SubCategoryID. DimSubCategory would contain SubCategoryID, SubCategoryName and CategoryID, while DimCategory would contain CategoryID and CategoryName.
This structure reduces repeated descriptive information. For example, instead of storing the same category information across many product records, the category can be stored once in DimCategory.
However, this also means that the model contains more tables and relationships. If a report needs to analyse sales by category, the filter may need to move through several tables:
DimCategory → DimSubCategory → DimProduct → FactSales
Advantages of a Snowflake Schema
- Reduces redundancy: Related information can be stored once instead of being repeated across a dimension.
- More structured dimensions: Large dimensions can be divided into smaller logical tables.
- Can be useful for complex data: Some organisations may already have highly normalized source systems that naturally produce this structure.
Limitations of a Snowflake Schema
- More relationships: Splitting dimensions creates additional relationships that Power BI has to manage.
- More complex reporting: Users may need to work across several tables to access related attributes.
- More complicated filtering: Filters may have to pass through multiple tables before reaching the fact table.
- More difficult to maintain: As the number of tables and relationships increases, understanding and troubleshooting the model can become harder.
Cross-Filter Direction
After establishing a relationship between tables, the next thing to consider is how filters should move between those tables. In Power BI, this is controlled by the cross-filter direction of a relationship.
There are two main options: Single direction and Both directions (bidirectional).
Single Direction
Single-direction filtering is the default and is the most common approach in a star schema. The filter normally flows from the one side of the relationship to the many side.
For example:
DimCustomer ───────────────► FactSales
1 *
If a user selects Nairobi from DimCustomer, Power BI can use that filter to show only the sales associated with customers from Nairobi in FactSales.
However, the filter does not automatically travel in the opposite direction. Filtering FactSales does not change the available values in DimCustomer.
This direction is usually preferred because it makes the model easier to understand and keeps filter behaviour predictable.
Both Directions
With Both selected, filters can move in both directions between the related tables.
DimCustomer ◄──────────────► FactSales
1 *
For example, filtering FactSales could also affect the values available in DimCustomer.
Power Query Joins
Power Query provides tools for preparing and transforming data before it is loaded into the Power BI data model. One of these tools is Merge Queries, which allows data from two tables to be combined based on a common column.
For example, suppose an organisation has a Sales table containing CustomerID, but the customer's name and location are stored in a separate Customers table.
Customers
┌────────────┬──────────────┬───────────┐
│ CustomerID │ CustomerName │ City │
├────────────┼──────────────┼───────────┤
│ C001 │ Joseph Kamau │ Nairobi│
│ C002│ Mitchell Faraja │ Mombasa│
│ C003 │ Joseph Kamau │ Kisumu │
└────────────┴──────────────┴───────────┘
Sales
┌─────────┬────────────┬─────────────┐
│ SalesID │ CustomerID │ SalesAmount │
├─────────┼────────────┼─────────────┤
│ S001 │ C001 │ 2500 │
│ S002 │ C002 │ 40000 │
│ S003 │ C001 │ 2500 │
└─────────┴────────────┴─────────────┘
The two tables have a common CustomerID column. In Power Query, this column can be used as the matching column when merging the tables.
After the merge, information from the Customers table can be brought into the Sales table:
Sales + Customers
↓
Merge
↓
┌─────────┬────────────┬──────────────┬───────────┬─────────────┐
│ SalesID │ CustomerID │ CustomerName │ City │ SalesAmount │
├─────────┼────────────┼──────────────┼───────────┼─────────────┤
│ S001 │ C001 │ Joseph Kamau │ Nairobi │ 2500 │
│ S002 │ C002 │ Mitchell Faraja │ Mombasa │ 40000 │
│ S003 │ C001 │ Joseph Kamau │ Kisumu │ 2500 │
└─────────┴────────────┴──────────────┴───────────┴─────────────┘
The important point is that Merge Queries physically brings data from one table into another during the Power Query transformation stage. It is therefore different from creating a relationship in the Power BI model.
When performing a merge, Power Query requires a column or columns that can be used to match records between the two tables. In this example, CustomerID is the matching column.
Power Query provides several types of joins that determine which records from the two tables are retained in the merged result. These include Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti joins.
Understanding what each join keeps is important because choosing the wrong join can result in missing records or unexpected data in the final table.
Left Outer Join
A Left Outer Join keeps all the records from the first, or left, table and brings in matching records from the second, or right, table. If a record in the left table does not have a match in the right table, the record is still retained, but the columns brought from the right table will contain blank or null values.
For example, suppose the Sales table is the left table and Customers is the right table.
Sales Customers
┌─────────┬────────────┐ ┌────────────┬──────────────┐
│ SalesID │ CustomerID │ │ CustomerID │ CustomerName │
├─────────┼────────────┤ ├────────────┼──────────────┤
│ S001 │ C001 │ │ C001 │ Joseph Kamau │
│ S002 │ C002 │ │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ │ C003 │ Joseph Kamau │
│ S004 │ C004 │ └────────────┴──────────────┘
└─────────┴────────────┘
If we merge these tables using CustomerID and select Left Outer, every record from Sales is retained.
Result
┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001 │ C001 │ Joseph Kamau │
│ S002 │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ Joseph Kamau │
│ S004 │ C004 │ null │
└─────────┴────────────┴──────────────┘
S004 is retained even though C004 does not exist in the Customers table. Because there is no matching customer record, CustomerName is returned as null.
This makes a Left Outer Join useful when the left table is the main dataset that must be preserved, while information from another table is being added where a match exists.
For example, an analyst could use a Left Outer Join to keep every sales transaction while adding customer names, locations or other customer attributes from a separate table.
In simple terms:
Left Outer = keep everything from the left table + matching records from the right table.
Right Outer Join
A Right Outer Join keeps all records from the second, or right, table and brings in matching records from the first, or left, table. If a record exists in the right table but has no matching record in the left table, it is still retained, while the columns from the left table contain blank or null values.
Using the same Sales and Customers tables:
Sales Customers
┌─────────┬────────────┐ ┌────────────┬──────────────┐
│ SalesID │ CustomerID │ │ CustomerID │ CustomerName │
├─────────┼────────────┤ ├────────────┼──────────────┤
│ S001 │ C001 │ │ C001 │ Joseph Kamau │
│ S002 │ C002 │ │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ │ C003 │ Joseph Kamau │
└─────────┴────────────┘ │ C004 │ David │
└────────────┴──────────────┘
If we merge the tables using CustomerID and select Right Outer, every record from Customers is retained.
Result
┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001 │ C001 │ Joseph Kamau │
│ S002 │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ Joseph Kamau │
│ null │ C004 │ David │
└─────────┴────────────┴──────────────┘
C004 is retained because it exists in the right-hand Customers table, even though there is no corresponding sales record. Since there is no matching record in Sales, the SalesID value is null.
A Right Outer Join can therefore be useful when the right table is the table whose records must all be preserved.
In practice, the same result could often be achieved by switching the order of the tables and using a Left Outer Join. Therefore, the important concept is not the word "right" itself, but understanding which table you want to preserve.
In simple terms:
Right Outer = keep everything from the right table + matching records from the left table.
Full Outer Join
A Full Outer Join keeps all records from both the left and right tables. Where a matching value exists in both tables, the records are combined. Where a record exists in only one table, it is still retained and the columns from the other table contain blank or null values.
Using the same Sales and Customers tables:
Sales Customers
┌─────────┬────────────┐ ┌────────────┬──────────────┐
│ SalesID │ CustomerID │ │ CustomerID │ CustomerName │
├─────────┼────────────┤ ├────────────┼──────────────┤
│ S001 │ C001 │ │ C001 │ Joseph Kamau │
│ S002 │ C002 │ │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ │ C003 │ Joseph Kamau │
│ S004 │ C005 │ │ C004 │ David │
└─────────┴────────────┘ └────────────┴──────────────┘
If we merge the tables using CustomerID and select Full Outer, every record from both tables is retained.
Result
┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001 │ C001 │ Joseph Kamau │
│ S002 │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ Joseph Kamau │
│ S004 │ C005 │ null │
│ null │ C004 │ David │
└─────────┴────────────┴──────────────┘
Here, C001, C002 and C003 exist in both tables, so their information is combined. C005 exists only in Sales, while C004 exists only in Customers. Both are still retained because a Full Outer Join does not discard unmatched records.
A Full Outer Join can therefore be useful when the goal is to identify all records across two datasets, including records that do not have a match.
For example, an analyst could use it to compare two datasets and identify customers that appear in one system but not the other.
In simple terms:
Full Outer = keep everything from both tables, whether there is a match or not.
Inner Join
An Inner Join keeps only the records that have a matching value in both tables. Any record that does not have a match is excluded from the merged result.
Using the same Sales and Customers tables:
Sales Customers
┌─────────┬────────────┐ ┌────────────┬──────────────┐
│ SalesID │ CustomerID │ │ CustomerID │ CustomerName │
├─────────┼────────────┤ ├────────────┼──────────────┤
│ S001 │ C001 │ │ C001 │ Joseph Kamau │
│ S002 │ C002 │ │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ │ C003 │ Joseph Kamau │
│ S004 │ C005 │ │ C004 │ David │
└─────────┴────────────┘ └────────────┴──────────────┘
If the tables are merged using CustomerID with an Inner Join, only customers that appear in both tables are retained.
Result
┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001 │ C001 │ Joseph Kamau │
│ S002 │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ Joseph Kamau │
└─────────┴────────────┴──────────────┘
C005 is removed because it exists in Sales but not in Customers. Similarly, C004 is not included because it exists in Customers but has no matching record in Sales.
An Inner Join is useful when the analysis should only include records that exist in both datasets. For example, an analyst may want to analyse only sales transactions that can be matched to a valid customer record.
In simple terms:
Inner Join = keep only the records that match in both tables.
Left Anti Join
A Left Anti Join returns only the records from the left table that do not have a matching record in the right table.
Using the same example:
Sales Customers
┌─────────┬────────────┐ ┌────────────┬──────────────┐
│ SalesID │ CustomerID │ │ CustomerID │ CustomerName │
├─────────┼────────────┤ ├────────────┼──────────────┤
│ S001 │ C001 │ │ C001 │ Joseph Kamau │
│ S002 │ C002 │ │ C002 │ Mitchell Faraja │
│ S003 │ C003 │ │ C003 │ Joseph Kamau │
│ S004 │ C005 │ │ C004 │ David │
└─────────┴────────────┘ └────────────┴──────────────┘
When Sales is the left table, a Left Anti Join checks which CustomerID values in Sales cannot be found in Customers.
Result
┌─────────┬────────────┐
│ SalesID │ CustomerID │
├─────────┼────────────┤
│ S004 │ C005 │
└─────────┴────────────┘
C005 is returned because it exists in Sales but does not exist in Customers.
This type of join is particularly useful for data-quality checks. For example, it can help an analyst identify sales transactions that refer to customer IDs that are missing from the customer master table.
In simple terms:
Left Anti = records in the left table that have NO match in the right table.
Right Anti Join
A Right Anti Join returns only the records from the right table that do not have a matching record in the left table.
Using the same tables, Customers is the right table. The join checks which customers do not appear in the Sales table.
Result
┌────────────┬──────────────┐
│ CustomerID │ CustomerName │
├────────────┼──────────────┤
│ C004 │ David │
└────────────┴──────────────┘
C004 is returned because it exists in Customers but has no corresponding record in Sales.
A Right Anti Join can therefore be useful for identifying records that exist in one dataset but are missing from another. For example, an analyst could use it to find registered customers who have not yet appeared in a sales dataset.
In simple terms:
Right Anti = records in the right table that have NO match in the left table.
Power Query Joins vs Power BI Relationships
Power Query joins and Power BI relationships can both involve matching columns between tables, but they serve different purposes and happen at different stages of the Power BI workflow.
The main difference is that a Power Query merge combines data, while a Power BI relationship connects tables without physically combining them.
Power Query Joins
A Power Query merge is performed during the data preparation and transformation stage. When two tables are merged, columns from one table can be brought into another based on matching values.
For example, if the Sales table contains CustomerID but does not contain the customer's name, Power Query can merge it with the Customers table using CustomerID and bring CustomerName into the resulting table.
Sales + Customers
│
│ Merge using CustomerID
▼
Combined table
│
├── SalesID
├── CustomerID
├── CustomerName
└── SalesAmount
The result is a physically combined table containing columns from both sources.
Power BI Relationships
A relationship is created after the data has been loaded into the Power BI model. Instead of combining the tables, Power BI keeps them separate and creates a connection between them using related columns.
For example:
DimCustomer FactSales
┌──────────────┐ ┌──────────────┐
│ CustomerID │ 1 * │ CustomerID │
│ CustomerName │───────────►│ SalesAmount │
│ City │ │ Quantity │
└──────────────┘ └──────────────┘
The tables remain separate, but the relationship allows filters from DimCustomer to affect the sales data in FactSales.
This is one of the main reasons relationships are important in a star schema. Customer information does not need to be copied into every sales transaction. Instead, the model connects the two tables through CustomerID.
Key Differences
| Power Query Merge | Power BI Relationship |
|---|---|
| Happens during data preparation | Happens in the data model |
| Physically combines columns from tables | Keeps tables separate |
| Produces a new/expanded query result | Creates a logical connection between tables |
| Used to transform and prepare data | Used for analysis, filtering and calculations |
| Can increase the width of a table | Keeps fact and dimension tables separate |
| Uses join types such as Inner and Left Outer | Uses relationship properties such as cardinality and filter direction |
When Should You Use a Merge?
A merge is useful when information from two sources genuinely needs to become part of the same table.
For example, an analyst may merge a product lookup table into a dataset during preparation when the product attributes are needed directly in the resulting table.
However, merging every table into one large dataset is not always a good approach. Excessive merging can create wide tables, increase duplication and make the data model harder to maintain.
When Should You Use a Relationship?
A relationship is generally preferable when the tables represent different types of information that should remain separate.
For example:
DimCustomer ───────► FactSales ◄─────── DimProduct
1 * 1
Here, customer and product information belongs in their respective dimension tables, while sales transactions belong in the fact table. Relationships allow these tables to work together during analysis without physically combining them.
This structure supports the star schema discussed earlier and allows the model to remain organised as more data is added.
Why Not Merge Everything?
It may seem simpler to combine all the data into one large table, but doing so can introduce unnecessary repetition.
For example, if the same customer makes 1,000 purchases, merging all of the customer's descriptive information into every sales record would repeat that information 1,000 times.
Keeping the customer information in DimCustomer and connecting it to FactSales through a relationship avoids this unnecessary duplication.
Therefore, the choice between a merge and a relationship depends on when and why the data needs to be combined:
Use Power Query merges to prepare and transform data. Use Power BI relationships to connect separate tables for analysis.
Recommended Power BI Model
Based on the concepts discussed above, a star schema with one-to-many relationships and single-direction filtering is generally the most suitable model for Power BI reporting and analytical workloads.
A typical e-commerce model could be structured as follows:
┌─────────────────┐
│ DimDate │
└────────┬────────┘
│ 1
│
│ *
┌─────────────────┐ ┌─────▼───────────┐ ┌─────────────────┐
│ DimCustomer │ 1 * │ FactSales │ * 1 │ DimProduct │
├─────────────────┤────────►│ │◄────────├─────────────────┤
│ CustomerKey │ │ CustomerKey │ │ ProductKey │
│ CustomerName │ │ ProductKey │ │ ProductName │
│ City │ │ DateKey │ │ Category │
│ Country │ │ Quantity │ │ Brand │
└─────────────────┘ │ Unit Price │ └─────────────────┘
└─────────────────┘
In this model, FactSales sits at the centre and contains the business transactions and numeric values being analysed. The dimension tables provide the descriptive context used to filter and group those transactions.
Why Use a Star Schema?
Simplicity:
Each dimension connects directly to the fact table, making the model easier to understand and navigate. Report developers can quickly identify where customer, product and date information is stored.
Performance:
A simpler relationship structure reduces unnecessary paths between tables and can allow Power BI to process analytical queries more efficiently. This becomes increasingly important as the amount of data grows.
Simpler DAX:
A clear separation between fact and dimension tables makes it easier to write and understand measures. For example, a measure calculating total sales can operate on the sales values in FactSales, while dimensions provide the context in which those sales are analysed.
Effective filter propagation:
Single-direction relationships allow filters to normally flow from dimensions to the fact table. For example, selecting a product category in DimProduct filters the corresponding records in FactSales.
Reduced unnecessary duplication:
Customer and product attributes are stored in their respective dimension tables rather than being repeated throughout every sales transaction. This keeps the fact table focused on the events and measurements being analysed.
Readability and maintainability:
Each table has a clear purpose. If a new customer attribute is required, it can be added to DimCustomer, while a new sales measure can be added to FactSales. This makes the model easier to maintain as reporting requirements change.
Scalability:
A star schema can accommodate additional dimensions as the reporting requirements grow. For example, a DimLocation or DimSalesperson table can be added and connected directly to FactSales without redesigning the entire model.
Recommended Relationship Design
For this model, the preferred relationship pattern would be one-to-many (1:*), with each dimension on the one side and the fact table on the many side.
The relationships would also normally use single-direction filtering, allowing filters to flow from the dimensions towards the fact table.
DimCustomer ──►
DimProduct ──► FactSales
DimDate ──►
This approach provides predictable filter behaviour and avoids introducing unnecessary bidirectional relationships that could create ambiguous filter paths.
Why Not Use a Snowflake Schema?
A snowflake schema can be appropriate in some situations, particularly where dimensions naturally contain several levels of hierarchy. However, for many Power BI reporting models, the additional tables and relationships can make the model more complicated than necessary.
For the e-commerce example used throughout this article, keeping product attributes such as category and brand within DimProduct provides a simpler structure than creating separate tables for each level of the product hierarchy.
Therefore, the recommended model is a star schema with one-to-many relationships and predominantly single-direction filtering. It provides a practical balance between performance, simplicity, usability, scalability and maintainability.
Comparison of Flat Table, Star Schema and Snowflake Schema
The three modelling approaches discussed above can be compared based on their structure, complexity, performance, flexibility and suitability for Power BI reporting.
| Aspect | Flat Table | Star Schema | Snowflake Schema |
|---|---|---|---|
| Structure | Most data stored in one table | One central fact table connected directly to dimensions | Fact table connected to dimensions that may be further split into related tables |
| Data redundancy | High, because descriptive information may be repeated | Lower, because descriptive data is stored in dimensions | Generally lower because dimensions are more normalized |
| Number of tables | Usually one large table | Fact table + dimension tables | Fact table + multiple related dimension/hierarchy tables |
| Model complexity | Simple at first, but can become difficult to manage as data grows | Relatively simple and easy to understand | More complex because of additional tables and relationships |
| Relationships | Few or none within the model | Mainly one-to-many relationships between dimensions and facts | More relationships because dimensions may connect to other dimension tables |
| Filter propagation | Limited need for relationship-based filtering | Clear and predictable from dimensions to fact | Can involve more relationship paths |
| DAX and analysis | Can become harder to manage as the table grows | Generally simpler because the model has a clear structure | Can require more complex relationships and calculations |
| Scalability | Less suitable as data and reporting requirements grow | Highly suitable for growing analytical models | Can scale, but complexity increases with additional normalized tables |
| Maintainability | Changes may affect a large table | Easier because each table has a clear purpose | More difficult because changes may involve several related tables |
| Typical use | Small datasets, simple analysis, spreadsheets | Business intelligence and Power BI reporting | Complex or highly normalized data structures |
| Recommended for Power BI reporting? | Usually not for larger analytical models | Yes generally preferred | Useful in specific scenarios |
From the comparison, the star schema provides a strong balance between simplicity, performance, scalability and maintainability. A flat table may be convenient when working with a small dataset, but repeated information and increasing table size can make it less suitable for a larger analytical model.
A snowflake schema can reduce redundancy and may be useful when dealing with complex hierarchies or highly normalized source data. However, it introduces additional tables and relationships that can make the model harder to understand and maintain.
For the e-commerce example used throughout this article, the star schema is therefore the preferred approach because it keeps the fact table focused on transactions while dimensions provide the descriptive information needed for analysis.
Conclusion
Data modelling is an important part of building effective Power BI reports because it determines how data is organised, connected and used for analysis. A well-designed model makes it easier to create reliable calculations, apply filters correctly and maintain the report as the amount of data and reporting requirements increase.
The comparison between flat tables, star schemas and snowflake schemas shows that each approach has its place. A flat table can be useful for simple datasets, while a snowflake schema can be appropriate when dealing with more complex or highly normalized data structures. However, for most Power BI analytical and reporting scenarios, a star schema provides a practical balance between simplicity, performance, scalability and maintainability.
Understanding the difference between fact and dimension tables is also important when building this type of model. Fact tables contain the events and measurements being analysed, while dimension tables provide the descriptive context used to filter and group those events. Defining the grain of the fact table and using appropriate primary and foreign keys helps maintain a consistent and reliable model.
Relationships then connect these tables and allow filters to move through the model. In a typical star schema, one-to-many relationships with single-direction filtering provide a clear and predictable structure. Active and inactive relationships can also be used when different date or analytical perspectives are required.
Power Query joins serve a different purpose. They are useful during the data preparation stage when information from different sources needs to be combined, filtered or compared. Power BI relationships, on the other hand, allow separate tables to work together during analysis without physically combining their data.
Overall, effective Power BI modelling is not simply about creating relationships between tables. It is about designing a structure that reflects the business data clearly and allows that data to be analysed efficiently. For the e-commerce scenario used throughout this article, a star schema with one-to-many relationships and predominantly single-direction filtering provides the most suitable foundation for building a clear, scalable and maintainable Power BI reporting model.
Top comments (0)