Introduction
Data Modelling in Power BI is about organizing data into tables and defining how these tables are connected to each other and support reporting and analysis. A good model will make it easier to build reports, manage filters, write DAX calculations (Data Analytics Expressions) while maintaining great performance as the data keeps on changing and growing.
In this article, I will explore 3 main modelling approaches: flat table, star and snowflake schema. I will use a mart data set which shows different customers and other information related to them and their orders. The mart is called NovaMart.
Flat Table
A flat table stores transactional and descriptive data in one table. Its simple to set up and works well for small datasets. However, as the data set grows, it might become harder to maintain and manager. In our NovaMart Dataset, the RawFlatSales represent this structure.
Star Schema
A star schema is used for separating the data into a central fact table connected directly to the dimension tables. This structure is much cleaner as it separates the business events from the the descriptive information. It also supports simple DAX and predictable filtering and good report performance, although some descriptive data can still be repeated within dimensions. It is generally suitable for BI and reporting models.
Snowflake Schema
This is an advanced version of the star schema. It separates some dimension information into additional tables. This reduces data redundancy and is useful for complex dimensions. The additional tables and relationships however make the model more complex and may make the reporting and filtering less straight forward. The NovaMart project demonstrates each of these approaches practically in the sections that follow, using the model and screenshots created in Power BI.
Keys, Cardinality and Referential Integrity.
In power BI, relationships depend on common keys (Primary Keys) between tables. In the NovaMart Model, the CustomerID is unique in DIMCustomer making it the primary key. This same key appears several times in the FactSales as a foreign key since one customer can make multiple purchases, which creates a one-to-many relationship *(1:) **
Cardinality describes how records in two tables relate, such as one-to-one, one-to-many or many-to-many. Referential integrity means that foreign key values should have matching records in the related table, helping maintain consistent and reliable data.
Active and Inactive Relationships
Power BI can have more than one relationship between the same tables, but only one is normally active at a time. In NovaMart,DimDate[Date] is actively related to FactSales[OrderDate], meaning it is used by default when analysing sales by date.
A second relationship can connect DimDate[Date] to FactSales[DeliveryDate] but remain inactive. This avoids conflicting filter paths while still making the relationship available when delivery-date analysis is required.
Filter Direction
Filter direction determines how filters move between related tables. In the NovaMart star schema, I used single-direction filtering, where filters move from the dimension tables to FactSales.
For example, selecting a product category such as Electronics fromDimProduct filters the related transactions in FactSales, allowing measures such as Total Sales to be calculated only for that category.
Bidirectional filtering allows filters to move in both directions. However, it should be used carefully as it can make filter paths and the overall model more complex.
Data Modelling in Power BI
Data modelling in Power Bi is the process of organizing data into tables and then defining how these tables are related to each other before using the data for analysis and reporting. This step should be structured in a way that will make analysis easier.
The structure of the model affects how easily reports can be created, how DAX calculations behave and how filters move between tables. It can also affect performance as the amount of data grows. A well-designed model should therefore be easy to understand, maintain and expand without introducing unnecessary duplication or complexity. In the NovaMart project, I used 3 approaches: a ** flat table,** ,star schema, and snowflake schema.
1. Import and Inspect the Data
Open Power BI Desktop, then go to Home → Get Data → Excel Workbook and select the NovaMart workbook.
The workbook contains several project tables. The next step is data cleaning in transform data tab. Ensure all the fields are properly formated.
2. Demonstrating Flat Tables
A flat table is simple and convenient for small datasets, but repeated descriptive data can increase redundancy, make maintenance harder and reduce scalability.
3. Building a star schema
Load FactSales, DimCustomer, DimProduct, DimDate and** DimLocation**. Go to Model View and create the below relationships.
DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]
DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]
DimProduct[ProductID] 1 ─── * FactSales[ProductID]
DimLocation[LocationID] 1 ─── * FactSales[LocationID]
DimDate[Date] 1 ─── * FactSales[OrderDate]
4. Demonstrating a snowflake Schema
A snowflake schema extends the star schema by separating dimension data into additional related tables. In our model, Category was separated from DimProduct into DimCategory, creating the structure FactSales → DimProduct → DimCategory. This reduces data redundancy but introduces additional relationships and model complexity.
5. Fact Tables, Dimension Tables
A fact table stores measurable business events and numeric values, while dimension tables store descriptive attributes used to analyse those events. In our data, the Common fact tables include FactSales, FactOrders ** and **FactTransactions.
In the NovaMart model, FactSales contains values such as Quantity, Discount and SalesAmount. It connects to DimCustomer, DimProduct, DimDate and DimLocation, which describe who purchased, what was purchased, when and where.
The grain or granularity defines what each row in a fact table represents. In FactSales, each row represents an individual sales transaction line identified by SaleID.
Together, these tables form a star schema, allowing sales to be analysed by customer, product, date and location.
6. Demonstrating Relationship Types
** One-to-Many (1:)*
One CustomerID appears once in DimCustomer but may appear many times in FactSales. DimCustomer[CustomerID] **acts as the primary key and **FactSales[CustomerID] as the foreign key.
One-to-One (1:1)
A one-to-one relationship exists when each key appears only once in both related tables. In the NovaMart model, DimCustomer and CustomerProfile are connected through CustomerID. Each customer has one customer record and one corresponding profile containing attributes such as Join Date and Loyalty Tier.
Many-to-Many
In the NovaMart model, products and promotions have a many-to-many relationship because a product can participate in several promotions, while a promotion can apply to several products. A BridgeProductPromotion table was used between DimProduct and DimPromotion to manage this relationship through two one-to-many relationships. This provides a clearer and more controlled model than using a direct many-to-many relationship.
7. Active and Inactive Relationships
Active Relationship
Power BI uses the solid line for the active relationship and a dashed line for the inactive relationship.
Inactive Relationship
8. Filter Directions
In the NovaMart star schema, filters normally flow in a single direction from dimension tables to FactSales. For example, selecting Electronics from DimProduct filters the related sales records and recalculates measures such as Total Sales.
Single Direction Filtering
For this, we created a measure of total sales, then added a slicer showing the total sales. This demonstrates a single direction filtering.
Both/Bidirectional Filtering
Bidirectional filtering allows filters to move in both directions, but it should be used carefully because it can create ambiguous filter paths and unnecessary model complexity.
9. Power Query Joins
For this part, Join_Customers and Join_Orders contain matching and non matching records. We will use them to obtain the different join types
Left Outer Join
This join displays all the customers and their matching orders
Right Outer Join
A right outer join retains all records from the second table and matching records from the first. In this example, all orders were retained, including the order for Customer C005, even though that customer did not exist in the Customers table.
Full Outer Join
A full outer join retains all records from both tables. Matching records are combined, while unmatched records from either table are retained with null values where no corresponding data exists.
Inner Join
An inner join returns only records with matching values in both tables. Any unmatched records from either table are excluded.
Left Anti Join
Returns rows from the first table that have no matching record in the second table. It is useful for identifying missing or unmatched records.
Right Anti Join
Returns records from the second table that have no matching record in the first table. In the NovaMart example, it identifies orders associated with customers that do not exist in the Customers table.
Power Query Joins vs Power BI Relationships
Power Query joins and Power BI relationships both connect data, but they serve different purposes. A Power Query join physically combines data from two tables during the data preparation stage. For example, merging JoinCustomers with JoinOrders adds order information to the resulting query.
While working on the NovaMart model, I used both Power Query merges and Power BI relationships. A Power Query merge physically combines data during the data preparation stage. For example, when I merged JoinCustomers and JoinOrders using CustomerID, fields such as OrderID and OrderAmount became part of the resulting query.
Relationships work differently. When I connected DimCustomer to FactSales using CustomerID, the tables remained separate. The relationship only defines how the tables interact and is created during the data modelling stage.
I would use a merge when data from different tables needs to become one dataset. For the main NovaMart model, relationships were more suitable because the fact and dimension tables needed to remain separate while still working together during analysis.
Excessive merging can create large flat tables with repeated information, similar to RawFlatSales. Keeping the fact and dimension tables separate reduces duplication, maintains the star schema structure and makes the model easier to manage as it grows.
Recommended Power BI Model Design
For a typical business intelligence project, I would recommend a star schema, with a central fact table connected to dimension tables using mainly one-to-many relationships.
From the three approaches explored, I found the star schema gives the best balance between performance, simplicity and scalability. A flat table is easier to set up but can create repeated data as it grows, while a snowflake schema reduces redundancy but introduces additional tables and relationships.
In the NovaMart model, FactSales is connected to DimCustomer, DimProduct, DimDate and DimLocation. This structure keeps the model readable, simplifies DAX and report creation, and makes it easier to maintain and expand.
I would also use one-to-many relationships with single-direction filtering from the dimension to the fact table. This provides a clear and predictable filter flow. Bidirectional filtering would only be used where necessary because it can make the model more complex.
Overall, I prefer the star schema because it provides good performance, less redundancy, simpler DAX, clear filter propagation and lower model complexity, while remaining easy to maintain as the model grows.
Conclusion
The NovaMart project demonstrates how different modelling choices can affect the structure and usability of a Power BI model. While flat tables, star schemas and snowflake schemas each have their place, the choice depends on the data being used and what the report needs to achieve.
For a typical BI project, I would use a star schema with one-to-many relationships and mainly single-direction filtering. It keeps the model organised, reduces unnecessary complexity and makes reporting, filtering and future changes easier to manage.
Top comments (0)