Introduction
Data Modelling in PowerBI is about organizing data into tables and defining how these tables 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.
A Power BI relationship, however, keeps the tables separate and creates a logical connection between them. In the NovaMart model, DimCustomer and FactSales remain separate tables connected through CustomerID.
For analytical models, relationships are generally preferred for fact and dimension tables because they preserve the star-schema structure, reduce unnecessary duplication, simplify filtering, and improve model maintainability. Merging is more appropriate when columns from separate sources genuinely need to be combined into a single table.
While working on the NovaMart model, I used both Power Query merges and Power BI relationships to connect tables. Although both methods connect data using common fields, I noticed that they work quite differently.
When I merged JoinCustomers and JoinOrders in Power Query using CustomerID, the data from the two tables was physically combined. After expanding the merged column, fields such as OrderID and OrderAmount became part of the resulting query. This happens during the data preparation and transformation stage in Power Query.
Relationships work differently. For example, I connected DimCustomer to FactSales using CustomerID, but the two tables remained separate. The relationship simply allows Power BI to understand how the records in the two tables are connected. This happens during the data modelling stage, after the data has been loaded.
I would use a merge when I actually need information from different tables to become part of one dataset. For example, if customer information was stored in two different source files and I needed one complete customer table, merging the data would make sense.
For the main NovaMart model, however, keeping the fact and dimension tables separate was more appropriate. Instead of merging customer, product, location and date information into FactSales, I connected them using relationships. This allowed me to maintain the star schema structure.
Excessive merging can eventually create a large flat table with repeated information. This is similar to what we saw in the original RawFlatSales table, where customer, product and location information was repeated across transactions. Keeping FactSales and the dimension tables separate therefore gives the model a cleaner structure, reduces unnecessary duplication and makes it easier to maintain as the model grows.
Recommended Power BI Model Design
For a typical business intelligence project, I would recommend using a star schema with a central fact table connected to separate dimension tables using mainly one-to-many relationships.
From the three structures explored in this project, the star schema gives the best balance between simplicity and performance. A flat table may be easier to create at the beginning, but as the dataset grows, it can contain a lot of repeated information. This increases redundancy and makes the model harder to maintain.
A snowflake schema reduces some of this duplication by separating dimension data further. However, it also introduces more tables and relationships. This can make the model more difficult to read and can add unnecessary complexity when building reports.
The star schema keeps the model more straightforward. In the NovaMart model, FactSales is kept at the centre and connected to dimensions such as DimCustomer, DimProduct, DimDate and DimLocation.
This structure also makes DAX easier to write and understand, because measures are mainly calculated from the fact table while the dimension tables are used for filtering and grouping. It also makes report building easier because fields are organised according to their business purpose.
For relationships, I would normally use one-to-many relationships, where the dimension table is on the one side and the fact table is on the many side. For example:
DimProduct[ProductID] 1 → * FactSales[ProductID]
I would also normally use single-direction filtering from the dimension table to the fact table. This gives a more predictable filter flow and reduces the possibility of ambiguous filter paths. Bidirectional filtering can still be useful in specific situations, but I would not use it by default because it can make the model harder to troubleshoot.
Overall, I would choose the star schema because it gives me:
• better model readability
• simpler DAX
• less unnecessary data duplication
• easier report development
• predictable filter propagation
• easier maintenance
• better scalability as more data is added
• less model complexity than a heavily snowflaked design
For me, the main advantage is that the model remains simple enough to understand but structured enough to scale. That is why I would normally choose a star schema rather than a flat table or a heavily snowflaked model for a Power BI business intelligence project.
Top comments (0)