Introduction
Power BI helps organizations turn raw data into meaningful insights for better decision-making. A well-designed data model is essential for accurate analysis, efficient DAX calculations, and good report performance. This article explains data modelling, fact and dimension tables, relationships, filter directions, and Power Query joins, using practical examples to demonstrate how these concepts are applied when building an effective Power BI solution.
1. Data Modelling
What is data modelling?
Data modelling in Power BI is the process of organizing data from one or more sources into tables and defining the relationships between those tables so that the data can be analyzed efficiently.
A well-designed data model provides the foundation for accurate Power BI reports and dashboards. Instead of keeping all information in one large table, you can separate related information into logical tables and connect them using keys.
For example, in an agricultural business, crop production records could be stored in a central fact table while information about farmers, crops, locations, and seasons is stored in separate dimension tables.
- A good data model is important because it:
- Improves reporting and analytical accuracy.
- Makes DAX calculations easier to write and understand.
- Improves Power BI performance by reducing unnecessary data duplication.
- Makes models easier to scale when new data is added.
- Improves maintainability because each table has a clear purpose.
- Makes relationships and filter propagation easier to understand.
There are three common approaches to structuring data: Flat Table, Star Schema, and Snowflake Schema.
1.2 Flat Table
A flat table stores all information in a single table. Instead of separating customers, products, locations, and transactions into different tables, all the attributes and business-event information are stored together.
Structure
For example, an agricultural dataset could look like this:
.
Illustration
Figure 1.1- shows a flat table
Advantages
- Simple to understand.
- Easy to import and begin analyzing.
- Requires few or no relationships.
- Suitable for small datasets and simple reports.
Disadvantages
- Creates data redundancy because descriptive information is repeated.
- Can make the dataset unnecessarily large.
- Changes to descriptive information may need to be repeated across many rows.
- Can make complex DAX calculations and modelling more difficult.
- Less scalable as the dataset grows.
When is it appropriate?
A flat table can be appropriate when working with a small, simple dataset, especially for basic analysis where the data does not need to be reused across multiple business processes.
Power BI implications
Flat tables are initially simple, but large flat tables can contain substantial redundancy. This can increase model size and make the model harder to maintain as more business requirements are introduced.
1.3 Star Schema
A star schema separates the data into a central fact table and several surrounding dimension tables.
The fact table contains measurable business events, while dimension tables contain descriptive information used to analyze those events.
Structure
For the agricultural example:
Advantages
- Excellent for Power BI reporting and dashboards.
- Reduces data redundancy.
- Makes DAX measures easier to understand.
- Relationships are generally straightforward.
- Supports efficient filter propagation.
- Easier to maintain and extend.
- Provides a clear and intuitive model structure.
Disadvantages
- Requires more planning than a flat table.
- Requires relationships between tables.
- Users need to understand facts, dimensions, and keys.
- Creating the model can initially take more time.
When is it appropriate?
Star schemas are particularly appropriate for business intelligence, reporting, and analytical systems where users need to analyze measures by different dimensions.
For example:
Total Profit by Crop, County, Farmer, and Season
can be easily analyzed using a star schema.
Power BI implications
Star schemas are generally preferred in Power BI because they provide simple relationship paths, efficient filtering, and a clear separation between descriptive attributes and measurable events.
1.4 Snowflake Schema
A snowflake schema is an extension of the star schema in which dimension tables are further divided into related tables.
For example, instead of storing the county and region directly inside DimLocation, location information could be separated:
Advantages
- Reduces repeated information within dimensions.
- Can be useful when dimensions contain complex hierarchies.
- Can provide greater control over highly structured data.
- May reduce redundancy in some large and complex datasets.
Disadvantages
- Creates more tables.
- Creates more relationships.
- Makes filter propagation more complicated.
- Can make DAX and report development less intuitive.
- Increases model complexity.
When is it appropriate?
A snowflake schema may be appropriate when dimensions are large, hierarchical, or naturally normalized.
For example, a multinational agricultural organization could have:
Country → Region → County → Location
and maintaining these levels separately may be useful.
Power BI implications
Although a snowflake schema can reduce redundancy, it introduces additional relationships and filter paths. For many Power BI analytical models, a well-designed star schema is simpler and easier to work with.
2 Fact Table and Dimensions
A well-designed Power BI data model usually separates business events from the descriptive information used to analyze those events. This is where fact tables and dimension tables become important.
Fact Tables
A fact table stores measurable business events or transactions. It normally contains numeric values that can be aggregated, together with foreign keys that connect the facts to descriptive dimension tables.
The table contains business events such as sales transactions. Quantity and SalesAmount are measures that can be aggregated, while CustomerID, ProductID, and DateID identify the dimensions associated with each sale.
Dimension Tables
A dimension table contains descriptive information about the entities involved in business events. Dimensions provide the context used to analyze facts.
Examples include:
-
DimCustomer– customer name, gender, segment, and location. -
DimProduct– product name, category, brand, and price. -
DimDate– date, month, quarter, and year. -
DimLocation– county, region, town, or country. For example:
The distinction can therefore be summarized as:
Understanding Grain
One of the most important concepts when designing a fact table is its grain, or granularity.
The grain defines exactly what one row in the fact table represents.
For example, if the grain of FactSales is:
"One row represents one product sold in one sales transaction."
then every row should represent one product line within a transaction.
Clearly defining the grain prevents problems such as duplicated transactions and incorrect calculations.
3. Relationships in Power BI
A relationship connects tables in a Power BI data model. Relationships are necessary because business data is often distributed across multiple tables rather than stored in one large table.
For example, FactSales may contain CustomerID, while DimCustomer stores customer names and customer segments.
This relationship lets Power BI connect the two tables and use customer information when analyzing sales.
Primary Keys and Foreign Keys
A primary key uniquely identifies a record in a table.
For example, CustomerID can be the primary key in DimCustomer:
Each CustomerID should occur only once.
The same CustomerID can appear multiple times in FactSales because one customer can make many purchases:
Each CustomerID should occur only once.
A one-to-many relationship means one record in one table can relate to many records in another table.
For example:
|DimCustomer | |FactSales|
|CustomerID | | CustomerID|
C001 C001
C001
C001
DimCustomer[CustomerID] contains unique values, while FactSales[CustomerID] can contain repeated values.
This is the most common relationship type in a star schema.
Use it when a dimension contains unique entities and a fact table contains multiple business events associated with those entities.
3.2 One-to-One Relationship (1:1)
A one-to-one relationship means that each record in one table corresponds to only one record in another table.
For example:
EmployeeID EmployeeDetails
E001 ────> E001
E002 ────> E002
E003 ────> E003
This relationship may be appropriate when information about the same entity has intentionally been split into two tables.
However, a 1:1 relationship should not normally be used simply because two tables can be connected by a column. If the tables represent the same entity and there is no strong modelling reason to separate them, combining them may be simpler.
3.3 Many-to-Many Relationship (:)
A many-to-many relationship occurs when multiple records in one table can be related to multiple records in another table.
For example, students can enrol in multiple courses, while each course can have multiple students.
Students Courses
Student A ───────────────> Course 1
Student A ───────────────> Course 2
Student B ───────────────> Course 1
Student C ───────────────> Course 2
In a proper dimensional model, a bridge or factless fact table can often be introduced to resolve this situation.
Many-to-many relationships should be used carefully because they can make filter
propagation and DAX calculations more difficult to understand.
Cardinality
Cardinality describes how records in two related tables correspond to each other.
The main cardinalities in Power BI are:
- 1:* — One-to-Many
- 1:1 — One-to-One
- : — Many-to-Many For most analytical models, 1:* relationships are preferred because they provide a clear dimension-to-fact structure.
Referential Integrity
Referential integrity means that foreign-key values should correspond to valid primary-key values in the related table.
For example, if FactSales contains:
CustomerID = C005
then C005 should normally exist in DimCustomer.
Missing dimension records can create unmatched or blank members in reports and may indicate data-quality problems.
Active and Inactive Relationships
Power BI can have both active and inactive relationships.
An active relationship is used automatically when filters move between related tables.
An inactive relationship is not automatically used but can be activated in a DAX calculation when required.
A common example is a sales table containing multiple date columns:
FactSales
---------
OrderDate
ShipDate
DeliveryDate
A date dimension may therefore have multiple relationships to the fact table. Only one can normally be active for the same pair of tables, while others can remain inactive and be activated in specific calculations.
4. Filter Direction
Relationships do more than connect tables. They also determine how filters propagate through the model.
Single-Direction Filtering
In a typical star schema, filtering flows from the dimension table to the fact table.
For example:
DimProduct
│
│ Filter
▼
FactSales
Suppose DimProduct contains:
If a user selects Electronics in a Power BI report, the filter is passed from DimProduct to FactSales.
Power BI then calculates sales using only the transactions associated with products in the Electronics category.
This is the preferred direction for many star-schema models because it is predictable and easy to understand.
Bidirectional Filtering
With bidirectional filtering, filters can move in both directions:
DimProduct
↕
FactSales
Although bidirectional filtering can solve some modelling scenarios, it should be used carefully.
It can create:
Ambiguous filter paths
- Unexpected filtering behaviour
- More complex DAX calculations
- Performance problems in complex models
- Difficulty understanding why a visual is being filtered
For example, if several dimension tables can reach each other through multiple paths, Power BI may have more than one possible route through which a filter can travel.
Therefore, single-direction filtering from dimensions to facts is generally preferable in a standard star schema.
_Figure 4.1 single-direction filtering versus bidirectional filtering between DimProduct and FactSales _
5. Joins in Power Query
Relationships are not the only way to work with multiple tables. Power Query provides Merge Queries, which allow tables to be joined during the data transformation stage.
A join matches records from two tables using one or more common columns.
Consider these two tables.
The tables can be merged using CustomerID.
5.1 Left Outer Join
A Left Outer Join keeps every record from the left table and matching records from the right table.
If Customers is the left table:
Carol remains in the result even though she has no matching order.
Use case: Find all customers and show their orders where available.
5.2 Right Outer Join
A Right Outer Join keeps every record from the right table and matching records from the left table.
Order O004 remains even though customer C004 does not exist in the Customers table.
Use case: Keep all orders and add customer information when available.
5.3 Full Outer Join
A Full Outer Join keeps all records from both tables, whether or not they have a match.
The result would include:
This is useful when you need to identify both:
- Customers without orders
- Orders without valid customers It can therefore be particularly useful for data-quality analysis.
5.4 Inner Join
An Inner Join keeps only records that exist in both tables.
Customers without orders and orders without matching customers are excluded.
Use case: Analyse only orders associated with valid customers.
5.5 Left Anti Join
A Left Anti Join returns records that exist in the left table but have no matching record in the right table.
Using Customers as the left table:
This identifies customers who have not placed an order.
Use case: Finding inactive customers or records missing from another dataset.
5.6 Right Anti Join
A Right Anti Join returns records that exist in the right table but have no matching record in the left table.
Using Orders as the right table:
This identifies orders whose customer does not exist in the Customers table.
Use case: Detecting invalid foreign keys or data-quality issues
_ Figure 5.3 -illustrates the summary of different joins _
6. Power Query Joins vs Power BI Relationships
Although both joins and relationships connect tables, they serve different purposes and operate at different stages of the Power BI workflow.
Power Query Merge
A Merge Query is a data-transformation operation.
It occurs before the data is loaded into the Power BI model.
For example:
Customers
+
Orders
↓
Power Query Merge
↓
Combined Table
The merge can bring columns from one table into another.
For example, an Orders table could be merged with Customers to add:
CustomerName
CustomerSegment
County
The result is physically represented as a combined query/table when loaded into the model.
Power BI Relationship
A relationship is created after the tables are loaded into the data model.
For example:
DimCustomer
│
│ 1:*
▼
FactSales
The relationship does not physically combine the tables.
Instead, it tells Power BI how the tables are connected so that filters and calculations can work across them.
Key Difference
When Should You Use a Merge?
Tables, Relationships, Filter Direction and Joins in Power BI
A merge is appropriate when the columns are genuinely required in the same table.
For example, if a source system stores product information separately but a transformation requires the product category to be included in a staging table, merging may be appropriate.
When Should You Use a Relationship?
A relationship is preferable when the tables represent different business entities.
For example:
DimProduct
│
▼
FactSales
There is usually no need to copy product name, category, and brand into every sales transaction.
Keeping them in DimProduct reduces data redundancy and preserves a clean dimensional model.
Problems with Excessive Merging
Excessive merging can create a very wide table containing repeated descriptive information.
For example, instead of:
DimProduct
ProductID
ProductName
Category
Brand
and:
FactSales
SalesID
ProductID
Quantity
Quantity
SalesAmount
a merge could produce:
SalesID
ProductID
ProductName
Category
Brand
Quantity
SalesAmount
If thousands or millions of transactions contain the same product information, the data becomes unnecessarily repetitive.
This can increase model size, reduce readability, and make maintenance more difficult.
For business intelligence solutions, keeping fact and dimension tables separate is often preferable because it supports a cleaner and more scalable model.
7. Recommended Power BI Model
For a typical business intelligence project, I would recommend a star schema rather than a flat table or a highly normalized snowflake schema.
A typical model could look like this:
graph TD
`
Customer["DimCustomer"]
Product["DimProduct"]
Date["DimDate"]
Location["DimLocation"]
Sales["FactSales"]
Customer -->|1:*| Sales
Product -->|1:*| Sales
Date -->|1:*| Sales
Location -->|1:*| Sales
`
The fact table contains measurable business events, while dimensions contain descriptive attributes.
Why Choose a Star Schema?
1. Performance
Star schemas generally provide an efficient structure for analytical queries because dimensions are separated from large transaction tables and relationships are straightforward.
2. Simpler DAX
Clear relationships make DAX calculations easier to understand.
For example, a measure such as:
Total Sales = SUM(FactSales[SalesAmount])
can be analyzed by product, customer, location, or date through the relationships in the model.
- Model Readability A star schema is visually easy to understand. Developers and report users can quickly identify:
- Which table contains transactions
- Which tables contain descriptive attributes
- How the tables are connected ###4. Scalability As the business grows, additional dimensions can be added without redesigning the entire model. For example:
DimCustomer
|
DimProduct — FactSales — DimLocation
|
DimDate
A future DimSalesperson or DimPromotion could also be connected to the fact table.
5. Reduced Redundancy
Product and customer information does not need to be repeated for every transaction.
Instead of storing:
Laptop | Electronics | HP | 500 times
the information can be stored once in DimProduct and referenced through ProductID.
6. Maintainability
If a product category changes, it can be updated in the dimension rather than repeatedly changing the same information across thousands of fact records.
7. Easier Reporting
Report users can drag attributes such as:
DimProduct[Category]
DimDate[Year]
DimLocation[County]
`
and measures such as:
[Total Sales]
into visualizations without needing to understand complex joins.
Recommended Relationships
For a standard star schema, I would normally implement:
- One-to-many
(1:*)relationships - Dimension table on the 1 side
- Fact table on the
*side - Single-direction filtering from dimensions to facts
- Unique primary keys in dimension tables
- Matching foreign keys in fact tables
- Clearly defined fact-table grain
- Active relationships for the main analytical paths Many-to-many relationships should only be introduced when the business requirement genuinely requires them, and bridge tables should be considered where appropriate.
Star Schema vs Flat Table vs Snowflake Schema
A flat table can be useful for small or simple datasets, but it can become inefficient and difficult to maintain as the amount of data grows.
A snowflake schema normalizes dimensions into additional related tables. This can reduce redundancy further, but it introduces additional relationships and complexity.
For most Power BI analytical models, the star schema provides the best balance between performance, simplicity, scalability, and maintainability.
Final Recommendation
My preferred Power BI model would therefore be a star schema with a central fact table surrounded by dimension tables. I would use one-to-many relationships wherever possible, with dimensions on the 1 side and facts on the * side.
I would also use single-direction filter propagation from dimensions to facts unless there is a specific modelling requirement for bidirectional filtering.
This approach produces a model that is easier to understand, easier to maintain, and easier to use when developing DAX measures and reports. More importantly, it separates business events from descriptive context, allowing the model to scale as the organization's reporting requirements grow.
A good Power BI model is therefore not simply about connecting tables. It is about designing those connections so that the resulting model is accurate, performant, understandable, and useful for business analysis.
Top comments (0)