Introduction
Say your working as a data analyst at ShopKe, a fictional Kenyan e-commerce company selling electronics, fashion and home products. The management team wants to understand sales performance, customer purchasing behavior and product profitability.
The company has three datasets namely Customers, Products and Orders. Even though these datasets contain valuable information, they need to be properly organized before creating a Power BI dashboard.
This is where data modelling becomes important. A well designed data model allows Power BI to connect related tables, perform accurate calculations and present meaningful business insights.
In this article, I will explore data modelling approaches, fact and dimension tables, relationships, filter directions and Power Query joins using ShopKe as a practical example for better understanding.
1. Data Modelling in Power BI
Data modelling is the process of organizing data into tables and defining relationships between them so that the data can be analyzed properly.
A good data model is important because it supports:
- Accurate reporting and DAX calculations.
- Better query performance.
- Scalability as data grows.
- Easier report development.
- Improved readability and maintainability.
For example, ShopKe may store customer information in one table and sales transactions in another. By connecting them using CustomerID, Power BI can analyze sales by customer without repeating customer details in every transaction.
1.1 Flat Table
A flat table stores all information in one table.
| OrderID | CustomerName | ProductName | Category | Quantity | SalesAmount |
|---|---|---|---|---|---|
| O001 | Stacy | Laptop | Electronics | 1 | 75,000 |
| O002 | Brian | Mouse | Electronics | 2 | 3,000 |
| O003 | Stacy | Shirt | Fashion | 1 | 2,500 |
Structure
┌──────────────────────────────┐
│ SalesData │
├──────────────────────────────┤
│ OrderID │
│ CustomerName │
│ ProductName │
│ Category │
│ Quantity │
│ SalesAmount │
└──────────────────────────────┘
Advantages: Simple to understand, easy to import and good for small datasets.
Disadvantages: Repeated information increases redundancy and the table may become difficult to maintain as the business grows.
When to use: Small datasets, quick analysis and simple reporting requirements.
Performance and complexity: It has few relationship complications, but unnecessary columns and repeated values can increase the amount of data stored.
1.2 Star Schema
A star schema contains a central fact table connected directly to multiple dimension tables.
For ShopKe, FactSales stores transactions, while DimCustomer, DimProduct and DimDate provide descriptive information.
Structure
ShopKe data model presented as a star schema
Advantages: Simple to understand, supports readable DAX, reduces unnecessary duplication and well suited for analytical reporting.
Disadvantages: Requires careful relationship design and may involve some repeated descriptive information within dimensions.
When to use: Sales, finance, inventory, customer and other business intelligence projects.
Performance and complexity: A well-designed star schema can support efficient analytical queries and compression while keeping the model relatively easy to navigate.
1.3 Snowflake Schema
A snowflake schema is a type of star schema where dimension tables are further divided into related tables.
For example, ShopKe may separate product information into Product, Subcategory and Category tables.
Structure
┌──────────────┐
│ DimCategory │
└──────┬───────┘
│ 1
│ *
┌──────▼───────┐
│DimSubcategory│
└──────┬───────┘
│ 1
│ *
┌─────────────┐ ┌──────▼───────┐
│ FactSales │ *──1│ DimProduct │
└─────────────┘ └──────────────┘
An example of how a snowflake schema model would look like
Advantages: Reduces repeated descriptive information and can support difficult hierarchies.
Disadvantages: More tables and relationships increase model complexity and may make report development less intuitive.
When to use: Complex organizational or geographical hierarchies, normalized data sources and situations where shared reference tables are useful.
Performance and complexity: It can reduce redundancy, but more relationships may make the model more complicated. In many cases, a star schema is preferred for simplicity.
2. Fact Tables and Dimension Tables
Fact tables
A fact table stores measurable business events. In ShopKe, FactSales contains individual sales transactions.
Example fields include:
- SalesID
- CustomerID
- ProductID
- DateKey
- Quantity
- SalesAmount
The FactSales table
Dimension tables
Dimension tables store descriptive attributes used to analyze facts.
Examples include:
- DimCustomer containing customer names and regions.
- DimProduct containing product names and categories.
- DimDate containing dates, months and years.
Grain of a fact table
The grain describes what one row represents.
For example:
One row in FactSales represents one product line in one customer order.
This is important because an order may contain several product lines. Therefore, counting rows may not equal counting unique orders.
ShopKe's star schema
FactSales connects to the dimensions through keys:
DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]
DimProduct[ProductID] 1 ─── * FactSales[ProductID]
DimDate[DateKey] 1 ─── * FactSales[DateKey]
DimLocation[LocationID] 1 ─── * FactSales[LocationID]
This allows ShopKe to analyze total sales by product, customer or date.
3. Relationships in Power BI
A relationship connects tables by use of matching columns. Relationships are necessary because business data is often distributed across several tables.
Primary keys and foreign keys
A primary key uniquely identifies a row in a table. A foreign key references a key in another table.
For example, CustomerID is unique in DimCustomer but may repeat in FactSales because one customer can make multiple purchases.
This creates a one to many relationship.
Relationship cardinalities
One to Many (1:*)
One row in a dimension relates to many rows in a fact table.
Example:
DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]
This is the most common relationship in a star schema.
One to One (1:1)
Each row in one table matches at most one row in another table.
Example:
Employee[EmployeeID] 1 ─── 1 EmployeeDetails[EmployeeID]
It is appropriate when two tables contain separate information about the same entity and both sides have unique keys.
Many to Many (:)
Multiple rows in one table can relate to multiple rows in another table.
Example:
Students * ─── * Courses
One student can take many courses and one course can have many students.
A bridge table can be used for a clearer design as shown:
DimCustomer 1 ─── * BridgeCustomerSegment * ─── 1 DimSegment
Referential integrity and unique values
Referential integrity means foreign keys should correspond to valid keys in the related dimension, where applicable.
For example, every CustomerID in FactSales should also exist in DimCustomer. Missing or unmatched keys may indicate data quality problems.
Active and inactive relationships
An active relationship is used automatically for filter propagation.
An inactive relationship is not used automatically but can be activated in a DAX measure.
For example, FactSales may have OrderDate and ShipDate. DimDate can have an active relationship with OrderDate and an inactive relationship with ShipDate.
4. Filter Direction
Filter direction determines how filters move between related tables.
Single-direction filtering
Filters flow in one direction, mostly from dimensions to facts.
DimProduct ───────────> FactSales
If a user selects Electronics in a product slicer, Power BI filters FactSales to show sales for electronics products.
Single-direction filtering is recommended for star schemas because it provides predictable behavior and reduces ambiguity.
Bidirectional filtering
Filters flow in both directions.
DimProduct <──────────> FactSales
This may be useful in specific scenarios, such as certain bridge-table models.
However, bidirectional filtering should be used carefully because it can create ambiguous filter paths, unexpected results and unnecessary model complexity.
For ShopKe, I would use single-direction filtering by default and only introduce bidirectional filtering when there is a clear business requirement.
5. Joins in Power Query
A join combines rows from two tables using matching columns. In Power Query, this is performed using Merge Queries.
If ShopKe had the following tables:
DimCustomers
| CustomerID | CustomerName |
|---|---|
| C001 | Stacy |
| C002 | Brian |
| C003 | Aisha |
DimOrders
| OrderID | CustomerID | Amount |
|---|---|---|
| O001 | C001 | 75,000 |
| O002 | C001 | 4,500 |
| O003 | C002 | 3,000 |
| O004 | C004 | 2,000 |
C003 has no order, while C004 appears in Orders but not Customers.
5.1 Left Outer Join
Returns all rows from the left table and matching rows from the right table.
Example: Customers LEFT JOIN Orders.
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| C001 | Stacy | O001 | 75,000 |
| C001 | Stacy | O002 | 4,500 |
| C002 | Brian | O003 | 3,000 |
| C003 | Aisha | NULL | NULL |
Use: Finding customers who have not placed orders while retaining all customers.
5.2 Right Outer Join
Returns all rows from the right table and matching rows from the left table.
Example: Customers RIGHT JOIN Orders.
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| C001 | Stacy | O001 | 75,000 |
| C001 | Stacy | O002 | 4,500 |
| C002 | Brian | O003 | 3,000 |
| C004 | NULL | O004 | 2,000 |
Use: Identifying orders without matching customer records.
5.3 Full Outer Join
Returns all rows from both tables, including unmatched records.
Expected unmatched records:
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| C001 | Stacy | O001 | 75,000 |
| C001 | Stacy | O002 | 4,500 |
| C002 | Brian | O003 | 3,000 |
| C003 | Aisha | NULL | NULL |
Matching records are also retained.
Use: Data reconciliation and identifying missing records in either table.
5.4 Inner Join
Returns only rows with matching values in both tables.
Expected output:
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| C001 | Stacy | O001 | 75,000 |
| C001 | Stacy | O002 | 4,500 |
| C002 | Brian | O003 | 3,000 |
Use: Keeping only customers who have matching orders.
5.5 Left Anti Join
Returns rows from the left table that have no matching rows in the right table.
Example: Customers LEFT ANTI JOIN Orders.
| CustomerID | CustomerName |
|---|---|
| C003 | Aisha |
Use: Finding customers who have never placed an order.
5.6 Right Anti Join
Returns rows from the right table that have no matching rows in the left table.
Example: Customers RIGHT ANTI JOIN Orders.
| OrderID | CustomerID | Amount |
|---|---|---|
| O004 | C004 | 2,000 |
Use: Identifying orders with missing customer records.
In Power Query, a merge may create a nested table column. For joins that return matching records, the column can be expanded to display the required fields.
6. Power Query Joins vs Power BI Relationships
Even though both connect tables, they serve different purposes.
| Feature | Power Query Merge | Power BI Relationship |
|---|---|---|
| Stage | Data preparation | Data modelling |
| Purpose | Combine data | Connect tables |
| Physically combines columns? | Yes, in the merged query result | No |
| Filter propagation | Not as a model relationship | Yes |
| Common use | Bring ProductCategory into a table | Connect DimProduct to FactSales |
Example
If ShopKe needs to bring ProductCategory into FactSales, Power Query Merge can combine the columns.However, if DimProduct already contains ProductCategory, keeping it separate and creating a relationship is better.
Too much merging can create a wide table with repeated customer and product information. This may cause increased redundancy and the model being harder to maintain.Keeping fact and dimension tables separate supports a clearer and more scalable star schema.
7. Recommended Power BI Model for ShopKe
For ShopKe, I would recommend a star schema with FactSales connected to DimCustomer, DimProduct and DimDate.
I would use one-to-many relationships from the dimensions to the fact table and single-direction filtering by default.
Why?
- Performance : Separating facts and dimensions can support efficient analytical queries.
- DAX simplicity : Measures such as Total Sales can be written directly against FactSales.
- Readability : Each table has a clear purpose.
- Scalability : New transactions, customers and products can be added without redesigning the entire model.
- Maintainability : Descriptive information can be updated in dimensions.
- Report development : Users can easily combine dimension attributes with fact measures.
Example measures:
Total Sales =
SUM(FactSales[SalesAmount])
Total Orders =
DISTINCTCOUNT(FactSales[OrderID])
Average Order Value =
DIVIDE([Total Sales], [Total Orders])
I would use bridge tables for genuine many-to-many scenarios and inactive relationships for alternative date analysis when necessary.
Conclusion
Data modelling is the foundation of a reliable Power BI report. It determines how data is organized, how tables interact and how accurately business questions can be answered.
As much as flat tables are simple and snowflake schemas can support complex structures, a star schema is most likely the most suitable choice for a business intelligence project.
Through the ShopKe example, we have seen how fact and dimension tables work together, how relationships control filter propagation and how Power Query joins prepare data for analysis.
The most important lesson is that proper Power BI development is not only about creating visuals. It is about designing a model that is accurate, understandable, scalable and useful for decision-making.
A well designed data model turns disconnected data into meaningful business insights.
Top comments (0)