Introduction
When working with data in Power BI, having the data is only the beginning. The way that data is organized and connected can make a big difference in how easily we can analyze it and how reliable our reports become. In a real business environment, information is rarely stored in one simple table. Sales, customers, products, dates, and locations are often stored separately, which means Power BI needs to understand how these different pieces of information relate to each other.
This is where data modelling, relationships, and joins come in. Data modelling involves structuring tables and defining how they connect so that the data can be used effectively for reporting and analysis. Relationships allow Power BI to move filters between related tables, while Power Query joins allow us to combine data during the data preparation stage.
In this article, I will explore the main data modelling approaches used in Power BI, including flat tables, star schemas, and snowflake schemas. I will also explain the difference between fact and dimension tables, how relationships and cardinality work, and how filter direction affects the way data behaves in a report. In addition, I will look at the different join types available in Power Query and explain how they differ from relationships in the Power BI data model.
Rather than looking at these concepts only from a theoretical point of view, I will use a practical sales example throughout the article. This will help demonstrate not only what these concepts mean, but also why choosing the right structure and relationships matters when building a Power BI solution.
Data Modelling in Power BI
Data modelling in Power BI is the process of organizing your data into tables and creating relationships between those tables so that Power BI can understand how the data is connected.
In our article today, we will be referencing electronic sales with entries like Sales ID, Date, Customer, Region, Product, Category, and Quantity.
The purpose of data modelling is not simply to separate tables. It is to create a structure that allows Power BI to correctly filter, calculate, analyze, and present your data.
Importance of a well-designed data model.
A good data model provides the foundation for your entire Power BI report.
1. Reporting
A proper model helps your charts, tables, slicers, and filters return the correct results.
For example, if you data contains quantity of phones sold and you want to know the quantity sold at a particular region for example in California, Power BI needs to understand the relationship between the sales and region information. With the correct relationships, selecting California automatically filters the relevant sales.
2. Analytics
A good model makes it easier to look at your data from different perspectives.
Using the same sales data, you might want to analyze:
- Quantity sold by region
- Quantity sold by customer
- Quantity sold by phone version
- Sales over time
- Performance of different products
You can do this without creating completely separate datasets for every analysis.
3. DAX calculations
Data modelling is especially important when creating DAX measures.
For example:
Total Quantity = SUM(Sales[Quantity])
The measure can then be used with different fields such as Region, Customer, Version, or Date.
If the relationships between your tables are correct, Power BI knows how those filters should affect the calculation. If the model is poorly designed, your DAX calculations can produce unexpected or incorrect results.
4. Performance
The way you structure your data can affect how quickly Power BI processes calculations and displays your reports.
A clean model avoids unnecessary duplication and complicated relationships, which can help Power BI work more efficiently, especially when dealing with large datasets.
5. Scalability
A good model should still work when your data grows.
For example, you might start with a few thousand sales records but later have hundreds of thousands or millions. You may also want to add information such as price, discount, payment method, or salesperson.
A well-designed model makes it easier to add this information without rebuilding the entire report.
6. Maintainability
Finally, a good model makes your Power BI project easier to understand and maintain.
If you or another analyst needs to modify the report later, it should be clear:
- What each table represents
- What each column means
- How the tables are related
- Which calculations are being used
- Where the data is coming from
This makes troubleshooting and future changes much easier.
data model is described as the foundation of your Power BI report.
You can have beautiful dashboards and complicated DAX formulas, but if the underlying model is poorly designed, you can still end up with incorrect results, slow reports, and difficult-to-maintain solutions.
So the basic idea is:
Organize the data → Create the right relationships → Build DAX calculations → Create visualizations → Get reliable insights.
A well-designed data model therefore supports accurate reporting, meaningful analytics, reliable DAX calculations, better performance, future growth, and easier maintenance.
So, we are going to look at different approaches when it comes to data modelling.
1. Flat Table
A flat table stores all the information in one table. Instead of separating information into fact and dimension tables, everything is kept together in a single dataset.
There are no relationships between separate tables because all the information is stored together.
Advantages
- Simple to understand, especially for beginners.
- Easy to import into Power BI.
- Does not require creating relationships between tables.
- Suitable for small and simple datasets.
- Quick to set up for simple reports.
Disadvantages
- Can contain a lot of duplicated information.
- The dataset can become very large.
- Changes to descriptive information may have to be repeated in many rows.
- More difficult to maintain as the dataset grows. Can use more memory. It is not ideal for complex DAX calculations and large Power BI models.
Instances flat table is Appropriate
A flat table can be appropriate when:
1.The dataset is small.
2.The analysis is relatively simple.
3.There are no complicated relationships between entities.
4.A quick report or prototype is required.
5.The source data is already provided as a single table.
6.Power BI performance and complexity
For small datasets, a flat table can perform reasonably well because there are no relationships to manage. However, as the number of rows and columns increases, the repeated information can increase the size of the model.
Therefore, although a flat table is simple, it is generally less scalable than a well-designed dimensional model.
2. Star Schema
A star schema is a data model where a central fact table is connected directly to several dimension tables.
It is called a star schema because the structure looks like a star: the fact table is in the center while the dimension tables surround it.
Structure
For example, our sample business could have:
┌──────────────────────┐
│ Dim Date │
├──────────────────────┤
│ PK | Date Key │
│ | Date │
│ | Month │
│ | Year │
└──────────┬───────────┘
│ 1
│
│ N
┌──────────────────────┐ 1 ▼ N ┌──────────────────────┐ N 1 ┌──────────────────────┐
│ Dim Customer ├─────────►│ Fact Sales │◄────────┤ Dim Product │
├──────────────────────┤ ├──────────────────────┤ ├──────────────────────┤
│ PK | Cust Key │ │ FK | Cust Key │ │ PK | Prod Key │
│ | Cust Name │ │ FK | Prod Key │ │ | Prod Name │
│ | Segment │ │ FK | Date Key │ │ | Category │
└──────────────────────┘ │ FK | Loc Key │ │ | Brand │
│ | Quantity │ └──────────────────────┘
│ | SalesAmt │
└──────────┬───────────┘
│ N
│
│ 1
┌──────────▼───────────┐
│ Dim Location │
├──────────────────────┤
│ PK | Loc Key │
│ | County │
│ | Region │
└──────────────────────┘
The Fact Sales table contains the business events, while the dimension tables provide information that describes those events.
Advantages
1.Simple and easy to understand.
2.Works very well with Power BI.
3.Reduces unnecessary duplication.
4.Makes relationships easier to manage.
5.Makes DAX calculations easier to write.
6.Generally provides good query performance.
7.Easier to maintain than a large flat table.
8.Scales better as the amount of data increases.
Disadvantages
1.Requires more planning than a flat table.
2.Requires relationships between tables.
3.Poorly designed relationships can produce incorrect results.
4.Users need to understand basic concepts such as primary keys, foreign keys, dimensions and facts.
When to use a star schema
Star schemas are particularly suitable for:
1.Business intelligence dashboards.
2.Sales analysis.
3.Financial reporting.
4.Customer analytics.
5.Inventory analysis.
6.Marketing analytics.
7.Power BI reports with multiple dimensions and measures.
8.Power BI performance and complexity
The star schema is generally the recommended approach for Power BI models because it separates business events from descriptive information.
For example, instead of storing the customer's name thousands of times in a sales table, the fact table can store a Customer Key and retrieve the customer's details from Dim Customer.
This reduces unnecessary duplication and gives Power BI a more structured model.
3. Snowflake Schema
Definition
A snowflake schema is an extension of the star schema where dimension tables are further divided into additional related tables.
In a star schema, dimensions are usually denormalized. In a snowflake schema, they are more normalized.
Structure
For example:
Advantages
1.Reduces duplication within dimension tables.
2.Provides a more normalized structure.
3.Can be useful when dimensions contain complex hierarchies.
4.Easier to manage certain large and complex datasets.
5.Can improve data consistency because information is maintained in one place.
Disadvantages
1.More tables and relationships.
2.More complicated than a star schema.
3.Can make the Power BI model harder for users to understand.
4.More relationships can increase modelling complexity.
5.Queries may require more joins.
6.Can make DAX and report development more complicated.
When to use snowflake schema .
A snowflake schema may be useful when:
- Dimension data is very large.
- Dimensions have multiple levels of hierarchy.
- There is substantial duplication within dimensions.
- Data consistency is particularly important. 5.The organization already uses highly normalized databases.
Although a snowflake schema can reduce duplicated data, it introduces additional relationships. This can make the model more complex and potentially require Power BI to perform more relationship traversals.
For many Power BI reporting scenarios, a star schema is preferred over a snowflake schema because it provides a good balance between performance, simplicity, and maintainability.
| Feature | Flat Table | Star Schema | Snowflake Schema |
|---|---|---|---|
| Number of Tables | One | Several | Several (including sub-dimensions) |
| Relationships | None | Fact → Dimensions | Fact → Dimensions → Sub-dimensions |
| Complexity | Low | Medium | High |
| Data Duplication | High | Lower | Very low |
| Ease of Understanding | Very easy | Easy | More difficult |
| Power BI Suitability | Small/simple models | Excellent | Good for complex models |
| Performance | Good for small data | Generally very good | Can be affected by additional relationships |
| Scalability | Low | High | High |
| Maintenance | Simple initially | Easy | More complex |
| Typical Use | Simple analysis | BI / Reporting | Complex normalized data |
A flat table is the simplest approach but becomes difficult to manage as data grows.
A star schema provides a good balance between simplicity, performance, and scalability and is therefore commonly recommended for Power BI.
A snowflake schema provides greater normalization but introduces more relationships and therefore more complexity.
Relationships, Filter Direction and Joins in Power BI
Creating tables is only part of building a good Power BI data model. The next important step is making sure Power BI understands how those tables are connected. This is where relationships become important.
In a typical electronic sales project, data is usually stored in several tables rather than one large table. For example, a sales system may have a Fact Sales table containing transactions, a Dim Customer table containing customer information, a Dim Product table containing product information, and a Dim Date table containing dates.
Relationships tell Power BI how these tables are connected and allow filters and calculations to work across them.
1. Relationships in Power BI
A relationship in Power BI is a connection between two tables based on a column that the tables have in common.
For example, Dim Customer may contain:
| Customer ID | Customer Name | Region |
|---|---|---|
| C001 | John | Nairobi |
| C002 | Mary | Mombasa |
| C003 | Peter | Kisumu |
While Fact Sales may contain:
| Sales ID | Customer ID | Product | Quantity |
|---|---|---|---|
| S001 | C001 | Phone | 2 |
| S002 | C002 | Laptop | 1 |
| S003 | C001 | Tablet | 3 |
| S004 | C003 | Phone | 1 |
Both tables contain Customer ID. Power BI can use this column to establish a relationship.
┌─────────────────────┐
│ Dim Customer │
├─────────────────────┤
│ PK | Customer ID │
│ | Customer Name │
│ | Region │
└──────────┬──────────┘
│
│ 1 (One)
│
Key: CustomerID
│
│ * (Many)
▼
┌─────────────────────┐
│ FactSales │
├─────────────────────┤
│ PK | SalesID │
│ FK | CustomerID │
│ | Product │
│ | Quantity │
└─────────────────────┘
Without this relationship, Power BI would not automatically know that C001 in the sales table refers to John in the customer table.
2. Primary Keys and Foreign Keys
Understanding keys is important when creating relationships.
Primary Key
A primary key is a column that uniquely identifies each record in a table.
For example:
Dim Customer
Customer ID
C001
C002
C003
C004
Each Customer ID appears only once, making it suitable as a primary key.
Foreign Key
A foreign key is a column used to refer to a record in another table.
In FactSales, the same CustomerID can appear many times:
Fact Sales
Customer ID
C001
C002
C001
C003
C001
This happens because one customer can make multiple purchases.
Therefore:
Dim Customer[Customer ID] → unique
Fact Sales[Customer ID] → can repeat
This is what creates a typical one-to-many relationship.
3. Relationship Cardinality
Cardinality describes how many records in one table can be associated with records in another table.
The main relationship types are:
- One-to-Many (1:*)
- One-to-One (1:1)
- Many-to-Many (:)
3.1 One-to-Many (1:*)
One-to-many is the most common relationship in a Power BI star schema.
It means that one record in one table can be related to many records in another table.
For example, one customer can make many sales.
Dim Customer Fact Sales
Customer ID Customer ID
C001 ─────────────────────────────► C001
C001
C001
C002 ─────────────────────────────► C002
C002
C003 ─────────────────────────────► C003
The relationship is:
Dim Customer Fact Sales
1 *
│ │
└─────────────────┘
The 1 side contains unique values, while the * side can contain repeated values.
Practical example
A company may have one customer record:
C001 | John | Nairobi
but John may have purchased several products:
C001 | Phone
C001 | Laptop
C001 | Headphones
C001 | Tablet
Therefore:
Dim Customer → Fact Sales = 1:*
When to use it.
This is normally the preferred relationship when working with a star schema.
Typical examples include:
- Dim Customer → Fact Sales
- Dim Product → Fact Sales
- Dim Date → Fact Sales
- Dim Location → Fact Sales
When to use it.
It should not be used simply because two tables have a similar column name. The values and meaning of the keys must actually represent a valid relationship.
3.2 One-to-One (1:1)
A one-to-one relationship means that one record in one table corresponds to only one record in another table.
Table A Table B
ID 1 ─────────────────────► ID 1
ID 2 ─────────────────────► ID 2
ID 3 ─────────────────────► ID 3
For example, an organization might have:
Employee
Employee ID| Employee Name
001 | Jane
002 | Peter
003 | Mary
and a separate table containing employee payroll information:
Employee Payroll
Employee ID| Salary
001 | 80,000
002 | 75,000
003 | 90,000
Each employee has one corresponding payroll record.
When to use it.
A 1:1 relationship can be useful when:
- A table contains additional information that logically belongs to the same entity.
- Sensitive or restricted information is kept separately.
- A very wide table has been intentionally split into smaller tables.
When not to use it.
If the two tables represent essentially the same information and can be combined without creating problems, a 1:1 relationship may unnecessarily complicate the model.
3.3 Many-to-Many (:)
A many-to-many relationship occurs when many records in one table can be related to many records in another table.
For example, students and courses:
Students Courses
Student A ───────────────────► Course 1
Student A ───────────────────► Course 2
Student B ───────────────────► Course 1
Student B ───────────────────► Course 3
One student can take many courses, while one course can have many students.
Students Courses
* *
\ /
\ /
────────────────
In a real database, this type of relationship is often handled using a bridge/junction table:
DimStudent
│
│ 1:*
▼
BridgeStudentCourse
│
│ *:1
▼
DimCourse
When to use it.
Many-to-many relationships can be useful when the underlying business relationship genuinely allows multiple matches on both sides.
When not to use it.
They should not be used as a quick solution for a poorly designed model.
Direct many-to-many relationships can make filter propagation and DAX calculations more difficult and may produce unexpected results.
Where possible, a bridge table or a clearer star-schema design is usually preferable.
4. Referential Integrity
Referential integrity means that values used as foreign keys should correspond to valid records in the related table.
For example, if Fact Sales contains:
Customer ID
C001
C002
C003
then those customers should normally exist in Dim Customer.
Dim Customer
C001 ✓
C002 ✓
C003 ✓
Fact Sales
C001 ✓
C002 ✓
C003 ✓
If the sales table contains C999, but C999 does not exist in Dim Customer, the relationship has a missing match.
This can lead to blank or unexpected results in reports.
Good data modelling therefore involves checking that keys are valid and that relationships make business sense.
5. Active and Inactive Relationships
Power BI can have relationships between the same tables that are not all active at the same time.
An active relationship is the relationship Power BI uses automatically when filtering and calculating data.
For example:
Dim Date[Date] ───────► Fact Sales[Order Date]
This could be the active relationship.
However, a sales table might also contain:
- Order Date
- Shipping Date
The date table could therefore have two possible relationships:
Dim Date ───────► Fact Sales[Order Date] ACTIVE
Dim Date ───────► Fact Sales[Shipping Date] INACTIVE
The inactive relationship can still be used in specific DAX calculations when required.
This is useful when the same fact table contains different dates that need to be analyzed separately.
6. Filter Direction
Relationships do more than connect tables. They also determine how filters move between tables.
Suppose we have:
Dim Product
│
│
▼
Fact Sales
If the relationship uses single-direction filtering, selecting Phone from Dim Product filters the related records in Fact Sales.
For example:
Dim Product
Product
-------
Phone ← Selected
Laptop
Tablet
↓ filter
Fact Sales
Product Quantity
Phone 2
Phone 1
Phone 4
The sales table now shows only sales associated with the selected product.
Single-Direction Filtering
Single-direction filtering normally moves from the dimension table towards the fact table.
Dim Product
│
│ Filter
▼
Fact Sales
This is generally the preferred approach for a star schema because the direction of filtering is clear and predictable.
For example:
Dim Customer ───────► Fact Sales
Dim Product ───────► Fact Sales
Dim Date ───────► Fact Sales
Dim Location ───────► Fact Sales
The dimensions filter the fact table.
7. Bidirectional Filtering
Bidirectional filtering allows filters to move in both directions.
Dim Product
▲
│
▼
Fact Sales
This can sometimes be useful, but it should be used carefully.
For example, if a product is selected, the sales table can be filtered. With bidirectional filtering, filters can also travel back from the sales table towards the product table.
Reason it may cause a problem.
Consider a model with several connected tables:
Dim Customer ───► Fact Sales ◄─── Dim Product
▲ ▲
│ │
└──────────────┬──────────┘
│
Other tables
If filters are allowed to travel in both directions, Power BI can end up with multiple possible paths between tables.
This can create:
- Ambiguous filter paths.
- Unexpected filtering.
- More complicated DAX behavior.
- Difficult-to-debug reports.
- Additional model complexity.
Therefore, single-direction filtering is generally preferred unless there is a clear reason to use bidirectional filtering.
8. Joins in Power Query
Relationships are not the only way to work with multiple tables.
Power Query provides another approach called Merge Queries.
A join combines two tables based on one or more matching columns.
For example, suppose we have:
Customers
| Customer ID | Customer Name |
|---|---|
| C001 | John |
| C002 | Mary |
| C003 | Peter |
| C004 | Jane |
Orders
| Order ID | Customer ID | Amount |
|---|---|---|
| O001 | C001 | 50,000 |
| O002 | C002 | 30,000 |
| O003 | C001 | 20,000 |
| O004 | C005 | 15,000 |
The tables can be merged using Customer ID.
Power Query provides several join types.
9. Left Outer Join
A Left Outer Join keeps all records from the left table and matching records from the right table.
Customers Orders
C001 ─────────────────────── C001 ✓
C002 ─────────────────────── C002 ✓
C003 ─────────────────────── no match
C004 ─────────────────────── no match
Expected result:
| Customer ID | Customer Name | Order ID | Amount |
|---|---|---|---|
| C001 | John | O001 | 50,000 |
| C002 | Mary | O002 | 30,000 |
| C003 | Peter | null | null |
| C004 | Jane | null | null |
The customers table determines which records are retained.
When to use it.
A company wants a list of all customers, including customers who have never placed an order.
10. Right Outer Join
A Right Outer Join keeps all records from the right table and matching records from the left table.
Customers Orders
C001 ─────────────────────── C001 ✓
C002 ─────────────────────── C002 ✓
C003
C004
C005 ← no customer match
Expected result:
| Customer ID | Customer Name | Order ID | Amount |
|---|---|---|---|
| C001 | John | O001 | 50,000 |
| C002 | Mary | O002 | 30,000 |
| C001 | John | O003 | 20,000 |
| C005 | null | O004 | 15,000 |
When to use it.
This could be useful when the orders table is the primary table and the objective is to retain every order, even where customer information is missing.
In practice, a left join is often easier to understand because you can simply place the table you want to preserve on the left.
11. Full Outer Join
A Full Outer Join keeps all records from both tables, whether they match or not.
Customers Orders
C001 ─────────────────────── C001 ✓
C002 ─────────────────────── C002 ✓
C003 ─────────────────────── no match
C004 ─────────────────────── no match
C005 ← no customer match
Expected result:
| Customer ID | Customer Name | Order ID | Amount |
|---|---|---|---|
| C001 | John | O001 | 50,000 |
| C002 | Mary | O002 | 30,000 |
| C001 | John | O003 | 20,000 |
| C003 | Peter | null | null |
| C004 | Jane | null | null |
| C005 | null | O004 | 15,000 |
When to use it.
A full outer join can be useful when checking data quality and identifying unmatched records on both sides.
For example, it can help identify:
- Customers without orders.
- Orders without valid customers.
12. Inner Join
An Inner Join keeps only records that have a match in both tables.
Customers Orders
C001 ─────────────────────── C001 ✓
C002 ─────────────────────── C002 ✓
C003 no match
C004 no match
C005
Expected result:
| Customer ID | Customer Name | Order ID | Amount |
|---|---|---|---|
| C001 | John | O001 | 50,000 |
| C002 | Mary | O002 | 30,000 |
| C001 | John | O003 | 20,000 |
Customers without orders are removed, and orders without a matching customer are also removed.
When to use it.
An inner join is appropriate when only records that exist in both datasets are relevant.
13. Left Anti Join
A Left Anti Join returns records from the left table that do not have a matching record in the right table.
Customers Orders
C001 ─────────────────────── C001
C002 ─────────────────────── C002
C003 ← retained
C004 ← retained
C005
Expected result:
| Customer ID | Customer Name |
|---|---|
| C003 | Peter |
| C004 | Jane |
These are customers who have no matching orders.
When to use it.
This is particularly useful for data quality checks.
For example:
Which customers have registered but never placed an order?
14. Right Anti Join
A Right Anti Join returns records from the right table that do not have a matching record in the left table.
In our example:
Customers Orders
C001 ─────────────────────── C001
C002 ─────────────────────── C002
C003
C004
C005 ← retained
Expected result:
| Customer ID | Order ID | Amount |
|---|---|---|
| C005 | O004 | 15,000 |
This identifies an order whose customer does not exist in the Customers table.
When to use it.
It can be used to identify:
- Invalid customer IDs.
- Orphan transactions.
- Data entry errors.
- Missing master data.
15. Summary of Power Query Join Types
To summarize this we ould say:
| Join Type | Records Retained |
|---|---|
| Left Outer | All left + matching right |
| Right Outer | All right + matching left |
| Full Outer | All records from both tables |
| Inner | Only matching records |
| Left Anti | Left records with no match |
| Right Anti | Right records with no match |
16. Power Query Joins vs Power BI Relationships
Although joins and relationships both involve connecting tables, they do fundamentally different things.
Power Query Merge
A Merge Query happens during the data preparation stage.
It can physically bring columns from one table into another.
For example:
Customers
Customer ID| Customer Name
C001 | John
+
Orders
Customer ID| Order ID| Amount
C001 | O001 | 50,000
↓ Merge
Combined Table
Customer ID| Customer Name| Order ID| Amount
C001 | John | O001 | 50,000
The result is a new table containing information from both sources.
Power BI Relationship
A relationship does not physically merge the tables.
Instead, the tables remain separate:
Dim Customer Fact Sales
Customer ID Customer ID
Customer Name Sales ID
Region Product ID
Amount
1 ───────────────── *
Power BI understands that the tables are connected and uses the relationship when filtering and calculating.
17. When to Merge Instead of Create a Relationship.
A merge is useful when two pieces of information logically belong together and you want them available in the same table.
For example, if a product table contains:
Product ID
Product Name
Category ID
and a small lookup table contains:
Category ID
Category Name
you may merge the category name into the product table during Power Query transformation.
However, you should not automatically merge every table.
For example, it is usually better to keep:
Dim Customer
Dim Product
Dim Date
Dim Location
Fact Sales
as separate tables rather than creating one enormous table.
18. Why Excessive Merging Can Be a Problem
Imagine taking:
Fact Sales
+
Customer
+
Product
+
Date
+
Location
and merging everything into one table.
The result may be a very wide table containing repeated information.
For example, if John makes 1,000 purchases, his customer information could be repeated across many rows.
This can:
- Increase redundancy.
- Make the model harder to maintain.
- Increase the number of columns in the fact table.
- Make the data model less intuitive.
- Make changes to dimensions more difficult.
- Move the model away from a clean star schema.
Therefore, merging should be done when there is a genuine data-preparation reason, not simply because tables are related.
19. Recommended Power BI Model
For a typical business intelligence project, I would generally recommend a star schema rather than a flat table or a highly normalized snowflake schema.
A typical model could look like this:
┌───────────────┐
│ Dim Date │
└───────┬───────┘
│ 1:*
▼
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ Dim Customer │ │ Fact Sales │ │ Dim Product │
│ │ 1:* │ │ *:1 │ │
└───────┬───────┘───────►│ Customer ID │◄───────┴───────────────┘
│ │ Product ID │
│ │ Date Key │
│ │ Location ID │
│ │ Quantity │
│ │ Sales Amount │
│ └───────┬───────┘
│ │ *:1
│ ▼
│ ┌───────────────┐
└────────────────►│ Dim Location │
└───────────────┘
The fact table contains the transactions, while the dimensions describe those transactions.
Reasons to choose a star schema?
Performance:
A well-designed star schema generally works efficiently in Power BI because the model has clear relationships and avoids unnecessary duplication.
DAX simplicity:
Measures such as:
Total Sales = SUM(Fact Sales[Sales Amount])
can be easily evaluated and then analyzed using dimensions such as product, customer, or date.
Model readability:
It is easy to understand what each table does. Fact tables contain events, while dimensions provide context.
Scalability:
New products, customers, locations, or dates can be added to their respective dimensions without redesigning the entire model.
Maintainability:
If a customer's region changes, the customer information can be updated in the dimension rather than being repeated across thousands of transaction records.
Reduced redundancy:
Descriptive information is stored once instead of repeatedly in every transaction.
20. Recommended Relationships and Filter Direction
For a typical star schema, I would normally use one-to-many relationships:
Dim Customer 1 ───── * Fact Sales
Dim Product 1 ───── * Fact Sales
Dim Date 1 ───── * Fact Sales
Dim Location 1 ───── * Fact Sales
The dimension tables would normally be on the one side, while the fact table would be on the many side.
I would also normally use single-direction filtering:
Dim Customer ───────► Fact Sales
Dim Product ───────► Fact Sales
Dim Date ───────► Fact Sales
Dim Location ───────► Fact Sales
This creates a clear filtering path.
For example, selecting Smartphones in Dim Product filters Fact Sales to only smartphone transactions. Selecting 2026 in Dim Date filters the sales to transactions occurring in that year.
Bidirectional filtering would only be introduced when there is a specific modelling requirement that cannot be handled cleanly with single-direction filtering.
Conclusion
Relationships, joins, and filter direction all play important but different roles in Power BI. A relationship connects tables in the Power BI data model without physically combining them. It allows filters and calculations to move between related tables.
A Power Query merge, on the other hand, combines data during the data preparation stage and can physically bring columns from one table into another.
For relationships, one-to-many is the most common cardinality in a well-designed star schema. One-to-one relationships can be useful in specific situations, while many-to-many relationships should be used carefully because they can introduce additional complexity.
Filter direction also matters. Single-direction filtering is normally preferred because it creates a predictable flow from dimensions to fact tables. Bidirectional filtering can be useful, but unnecessary use can create ambiguous filter paths and make the model harder to understand.
For Power Query, the different join types provide flexibility for combining and validating data. Inner joins keep matches, outer joins preserve records from one or both tables, while anti joins are particularly useful for finding unmatched records.
For a typical Power BI business intelligence solution, I would choose a star schema with one-to-many relationships and primarily single-direction filtering. This approach provides a good balance of performance, DAX simplicity, readability, scalability, low redundancy, and maintainability. More importantly, it keeps the model understandable: the fact table records what happened, while the dimension tables explain who, what, when, and where it happened.
Top comments (0)