Power BI is a business intelligence tool that allows you to connect to various data sources, visualize the data in reports and dashboards, and then share them. Power BI helps to track Key Performance Indicators (KPIs) and helps businesses to get insights that inform decision making.
This article explores data modeling in Power BI, comparing flat table, star schema, and snowflake schema designs; the role of fact and dimension tables; how relationships and filter direction work within the model; how joins in Power Query differ from relationships in the data model; and finally, a recommended approach for structuring a typical business intelligence solution.
Data Modeling
Data modeling is the process of creating a structured and visual representation of data and their relationships. Without data modeling, Power BI projects suffer from bloated file sizes, slow performance, and incorrect report numbers.
A well-designed data model is important for several reasons:
Reporting and analytics - a clean data model is crucial because it ensures fast report performance, accurate calculations, and a simplified structure that allows users to easily build and scale their own analytics.
DAX calculations - DAX measures rely on relationships between tables to filter and aggregate data correctly. A strong model keeps DAX formulas simple and predictable; a weak one forces overly complex workarounds or produces incorrect results.
Performance - a clean data model reduces memory usage, allowing dashboards and interactive visuals to load and filter instantly.
Scalability - a good model can absorb new tables and new data sources without requiring a complete redesign.
Maintainability - when tables are logically separated with clear relationships, the model is easier for others to understand, troubleshoot and use over time.
In summary, a well-designed model makes reporting, analysis, calculations and performance much easier, while a poorly designed one creates unnecessary challenges at every stage.
There are primarily three structural types of data modeling in Power BI: Flat Table, Star Schema and Snowflake Schema.
1. Flat Table
A flat table is a single, wide table that stores all relevant information in one place. It has no relationships and the data is in a single, spreadsheet-like structure. This is typically what a dataset looks like the moment it's loaded into Power BI from a single Excel file or CSV before any modeling.
2. Star Schema
A star schema is a data modeling approach where a single central fact table is connected directly to multiple surrounding dimension tables, forming a shape that resembles a star when viewed in Power BI's Model view. It is the most widely recommended structure for Power BI reporting.
Fact table - a central table in a data model that stores the measurable, numeric events of the business along with foreign keys linking each row to the relevant dimensions.
Dimension table - a supporting table that stores descriptive attributes about the entities involved in the events stored in fact tables.
The screenshot above shows a Star Schema. FactSales sits at the centre of the model, connected by four separate relationship lines to DimCustomer, DimProduct, DimLocation and DimDate with their relationships.
Advantages of Star Schema
- Simple, intuitive relationships - every dimension links directly to the fact table, making the model easy to read, navigate and explain.
- Simplifies DAX — Dimension and fact responsibilities are separated, making measure logic clear.
- Easy to build reports - dragging fields from dimension tables onto a visual filters the fact table correctly with no extra configuration.
- Highly scalable - Supports adding new dimensions without affecting existing structure
Disadvantages of Star Schema
Data Redundancy - Denormalized dimension tables repeat attribute values across multiple rows, which can increase storage needs.
Risk of Inconsistency - Duplicated data means that updating an attribute (like renaming a product category) may require changes in multiple rows, risking data quality issues if not done correctly.
Where it is appropriate
The star schema is the default, recommended structure for most business intelligence projects. It is particularly appropriate when:
- Building standard reporting solutions such as sales analysis, financial reporting, or operational dashboards.
- Dimension tables are moderate in size and don't require complex, multi-level hierarchies to be broken out separately.
- Report performance and simplicity of DAX are priorities.
Implications for Power BI performance and complexity:
A star schema significantly boosts Power BI performance by allowing the VertiPaq engine to optimize data compression and accelerate query execution through direct relationship paths. Also the star schema minimizes data complexity by separating numeric facts from descriptive attributes, streamlining DAX calculations and simplifying the overall reporting model.
3. Snowflake Schema
A snowflake schema is an extension of the star schema in which one or more dimension tables are further broken down into related sub dimension tables, normalizing the data further and producing a shape that resembles a snowflake when viewed in Power BI's Model view.
In a snowflake schema, a dimension table that would normally sit as a single table in a star schema is split into two or more related sub dimension tables, each holding a narrower set of attributes. The lowest level dimension retains a direct relationship to the fact table, while the additional sub dimension tables branch outward from it in a chain, with each table connected to the next through its own primary and foreign key. This continues until the attributes have been separated into as many normalized levels as the hierarchy requires, resulting in a structure where the fact table sits at the centre, immediate dimensions surround it as in a star schema, and further related tables extend outward from those dimensions rather than connecting directly back to the fact table.
The screenshot shows the snowflake schema in Model view, with FactSales connected directly to DimProduct as in the star schema, but DimProduct_Snowflake now extending further to DimSubcategory and then to DimCategory, illustrating the extra normalization and relationship hops that distinguish a snowflake schema from a star schema.
Advantages of Snowflake schema
A snowflake schema reduces data redundancy further than a star schema, since repeated text values such as category names are stored once in their own table rather than repeated across every product row. It can also save storage space when a dimension table is very large and its higher level attributes repeat extensively.
Disadvantages of Snowflake schema
The additional tables mean more relationships and more relationship hops for filters to travel through, which increases model complexity. DAX measures can become harder to write and slower to evaluate when a filter has to pass through several linked dimension tables before reaching the fact table.
Where it is appropriate
A snowflake schema is appropriate when a dimension table is very large and highly repetitive at higher levels, when strict normalization is required for governance or auditing reasons, or when a dimension naturally has a deep, well-defined hierarchy that benefits from being modeled as separate levels, such as Category, Subcategory, and Product. It is generally used on specific dimensions rather than applied across an entire model.
Implications for Power BI performance and complexity
Because filters must pass through additional relationship hops before reaching the fact table, snowflake schemas are slower than star schemas and place more load on the DAX engine when aggregating across levels. Model complexity is also higher, since there are more tables and relationships to maintain, making the snowflake schema best reserved for specific dimensions that genuinely benefit from normalization rather than the whole model.
Relationships in Power BI
Relationships in Power BI refer to the logical connections between two or more tables based on a common column. These connections enable Power BI to combine data from multiple tables and perform calculations across them, just like joins in traditional databases.
Relationships are the core of any data model because star and snowflake schemas intentionally divide information into separate tables. Without these connections, the tables would stay completely isolated, preventing data from being filtered, combined, or analyzed together.
Every relationship in Power BI has a cardinality which describes how many matching rows can exist on each side of the relationship.
1. One-to-Many (1:*)
One record in the first table is related to multiple records in the second table.
For example, in our model, DimCustomer has one row per customer, while FactSales can contain many rows for the same customer, since a customer can place multiple orders. CustomerID is unique in DimCustomer, but repeats in FactSales wherever that customer appears again.
2. One-to-One (1:1)
Each row in the first table relates to exactly one row in the second table, and vice versa, meaning the key column is unique on both sides of the relationship.
3. Many to Many (:)
Rows in both tables can have multiple matching rows on the other side, meaning the key column is not unique in either table.
For example, a student can enroll in many courses and each course can have many students. Neither StudentID nor CourseID is unique on its own, since the same student appears against multiple courses and the same course appears against multiple students.
Primary Keys and Foreign Keys
A primary key is the column in a table that uniquely identifies each row, such as CustomerID in DimCustomer.
A foreign key is a column in another table that references that primary key, such as CustomerID appearing in FactSales. This is why CustomerID contains unique values in DimCustomer, appearing once per customer, but appears multiple times in FactSales as a foreign key, once for every order that customer places.
Unique Keys, Cardinality and Referential Integrity
- Unique Keys For a relationship to work correctly in Power BI, the column on the “one” side must contain unique values. A unique key allows Power BI to establish a valid relationship between tables.
- Cardinality Cardinality defines the type of relationship created between the tables based on how their key values correspond.
- Referential integrity Referential integrity means that every foreign key in one table has a corresponding primary key in the related table. For instance, each CustomerID in FactSales should have a matching CustomerID in DimCustomer. If some foreign key values do not have matching records, Power BI may produce blank or unexpected results in reports.
Active and Inactive Relationships
Active and inactive relationships become important when tables can be connected through multiple relationship paths. Power BI permits only one active relationship between two tables at a time and this relationship is automatically used when applying filters and performing calculations. Other possible relationships are treated as inactive and must be specifically enabled within a DAX measure using functions such as USERELATIONSHIP.
For example, suppose the FactSales table contains both OrderDate and ShipDate, with each column connected to the DimDate table. Only one of these relationships can remain active at a time. The other relationship is inactive and can be temporarily activated in a measure when analysis needs to be based on the shipping date rather than the order date.
Filter Direction
Filter direction determines how filtering behaves once a relationship exists, specifically which table's filters are allowed to affect the other table.
Single-Direction Filtering
In single-direction filtering, filters flow only one way, typically from the "one" side of a relationship to the "many" side. This is the default and most common setting in Power BI. For example, if you select a product from a DimProduct slicer, Power BI filters the FactSales table so that it displays only sales related to that selected product. However, the filtering does not work in reverse, changes or selections in FactSales will not filter the DimProduct table.Bi-Direction Filtering
In bidirectional filtering, filters flow both ways, meaning a selection in either table can affect the other. For example, if bidirectional filtering were enabled between DimProduct and FactSales, filtering FactSales down to a specific store location would also filter DimProduct, showing only the products that were sold at that location, in addition to the usual DimProduct-to-FactSales filtering. This is different from single-direction filtering, where FactSales selections have no effect on DimProduct at all.
Joins in Power Query
A join, in the context of Power Query, is a way of combining two tables based on a matching column between them, using the Merge Queries feature. Unlike a relationship in the data model, a merge in Power Query happens during data preparation, before the model is even loaded, and it produces a new, physically combined table rather than simply linking two existing ones.
Throughout this section, we'll use two sample tables:
Customers
| CustomerID | CustomerName |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
| 4 | David |
Orders
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 250 |
| 102 | 2 | 100 |
| 103 | 2 | 75 |
| 104 | 5 | 300 |
1. Left Outer Join
Returns all rows from the left (first) table, plus matching rows from the right (second) table. Where there's no match, the right table's columns are filled with null.
Every row from the left table, matched or not, is retained.
Example: Merge Customers (left) with Orders (right) on CustomerID
Expected Output:
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | Alice | 101 | 250 |
| 2 | Bob | 102 | 100 |
| 2 | Bob | 103 | 75 |
| 3 | Carol | null | null |
| 4 | David | null | null |
This is the default and most common join in Power Query — used when you want to enrich a primary table (e.g., Customers, Products) with related data without losing any of its original rows.
2. Right Outer Join
Returns all rows from the right (second) table, plus matching rows from the left table. Unmatched left-table columns are null.
Every row from the right table, matched or not, is retained.
Example: Merge Customers (left) with Orders (right) on CustomerID.
Expected Output:
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | Alice | 101 | 250 |
| 2 | Bob | 102 | 100 |
| 2 | Bob | 103 | 75 |
| null | null | 104 | 300 |
3. Full Outer Join
Returns all rows from both tables, matched where possible, with null filled in wherever a match doesn't exist on either side.
Every row from both tables is retained. Nothing is dropped.
Example: Merge Customers and Orders on CustomerID.
Expected Output:
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | Alice | 101 | 250 |
| 2 | Bob | 102 | 100 |
| 2 | Bob | 103 | 75 |
| 3 | Carol | null | null |
| 4 | David | null | null |
| null | null | 104 | 300 |
4. Inner Join
Returns only the rows where the key exists in both tables.
Only matched rows are retained. Anything unmatched on either side is dropped entirely.
Example: Merge Customers and Orders on CustomerID.
Expected Output:
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | Alice | 101 | 250 |
| 2 | Bob | 102 | 100 |
| 2 | Bob | 103 | 75 |
5. Left Anti Join
Returns only the rows from the left table that have NO match in the right table. It is the logical opposite of an Inner Join, restricted to the left side.
Left-table rows with no corresponding right-table match are retained. No columns from the right table are brought in (there's nothing to bring in, since by definition nothing matched).
Example: Merge Customers (left) with Orders (right) on CustomerID.
Expected Output:
| CustomerID | CustomerName |
|---|---|
| 3 | Carol |
| 4 | David |
6. Right Anti Join
Returns only the rows from the right table that have NO match in the left table.
Right-table rows with no corresponding left-table match are retained.
Example: Merge Customers (left) with Orders (right) on CustomerID.
Expected Output:
| OrderID | CustomerID | Amount |
|---|---|---|
| 104 | 5 | 300 |
Power Query Joins vs Power BI Relationships
A Power Query merge combines data by adding columns from one table to another before the data is loaded into Power BI, resulting in one combined table. In contrast, a Power BI relationship keeps the tables separate and defines how filters should move between them when queries and reports are processed.
A merge is useful when you need a combined table for export or when you want to permanently add a small number of lookup columns. A relationship is more suitable when the tables represent different entities that need to be analyzed independently in reports.
Recommended Power BI Model
For most business intelligence projects, a star schema is a strong choice. It typically consists of a central fact table, such as FactSales, linked to shared dimension tables such as DimCustomer, DimProduct, DimDate, and DimLocation. These tables are usually connected using one to many relationships, with filters flowing in a single direction from the dimension tables to the fact table.
Compared with a flat table, a star schema reduces the repetition of descriptive information across large numbers of fact records. This helps keep the model more compact and can improve refresh performance. It is also generally simpler than a snowflake schema because it reduces the number of relationships and filtering steps required by the model. This makes DAX calculations easier to write and the overall model easier to understand.
A star schema also provides several practical benefits. It supports consistent and efficient filtering, works well with common DAX and time based calculations, and gives report developers a clear structure that is easy to navigate. Since descriptive attributes are maintained in dimension tables rather than repeatedly stored in the fact table, unnecessary data duplication is reduced. The model can also grow more easily as the amount of business data increases, while changes to business information can usually be made in one central location.
Conclusion
In conclusion, effective Power BI modelling depends on choosing the appropriate schema, separating fact and dimension tables, and establishing well-designed relationships between them. A star schema is generally preferred because it reduces data duplication, improves query performance, simplifies DAX calculations and makes the model easier to understand and maintain. Using reliable keys and controlled filter directions also helps ensure accurate and predictable results, while Power Query merges should be reserved for situations where combining data during preparation is necessary. Overall, applying these principles creates a Power BI model that is efficient, scalable, easy to manage, and suitable for reliable business reporting and analysis.
Top comments (0)