Introduction
A well-designed data model is the foundation of every successful Power BI solution. It determines how fast your reports load, how simple your DAX formulas are, and how easily your solution can scale as your business grows. This article walks through the core concepts of data modelling in Power BI—covering schema design, fact and dimension tables, relationships, filter direction, and joins in Power Query—so you can build models that are performant, maintainable, and intuitive for report authors.
1. Data Modelling in Power BI
What is Data Modelling and Why Does it Matter?
Data modelling in Power BI is the process of structuring your data into tables, defining how those tables relate to one another, and organising them in a way that supports efficient querying, accurate calculations, and intuitive report building.
A well-designed data model is critical for:
- Reporting and analytics: Clean, logical structures make it easier for users to drag and drop fields without confusion.
- DAX calculations: Simpler models lead to simpler, more reliable measures.
- Performance: Efficient schemas reduce the amount of data Power BI needs to scan and filter.
- Scalability: Good models grow gracefully as data volumes and business requirements expand.
- Maintainability: Clear structures are easier to document, troubleshoot, and hand over to other developers.
Data Modelling Approaches: Flat Table, Star Schema, and Snowflake Schema
Flat Table
Definition: A flat table is a single table where all data—facts and descriptive attributes—resides in one large table.
Structure:
- One table with many columns. Columns for facts (sales amount, quantity) and attributes (customer name, product category)
- No relationships; everything is in one place.
Advantages:
- Simple to understand for beginners.
- No need to manage relationships.
- Quick to build for very small datasets.
Disadvantages:
- High data redundancy (e.g., customer name repeated on every sales row).
- Larger file size and slower refresh.
- Harder to maintain as business rules change.
- DAX can become complex when handling repeated attributes.
When appropriate:
- Very small datasets or one-off analyses.
- Prototyping or quick proofs of concept.
Implications for Power BI:
- Poor performance at scale due to redundancy.
- Increased model size and memory usage.
- Difficult to extend or refactor later.
For instance, all descriptive attributes like PatientsName, PatientsGender, PatientsAge are repeated on every row
Normalization -
breaking down tables into smaller tables to reduce redundancies
Star Schema
Definition: A star schema consists of a central fact table surrounded by dimension tables, each connected via one-to-many relationships. A Central fact table connected to multiple dimension tables.
Structure:
- One fact table in the centre, surrounded by Multiple dimension tables radiating out like a star.
- Each dimension connects directly to the fact table.
Advantages:
- Optimised for Power BI's tools, clear relationships, efficient queries.
- Minimal DAX complexity.
Disadvantages:
- Requires upfront modelling effort.
- Slightly more tables to manage than a flat table.
When appropriate:
- Most business intelligence and reporting scenarios.
- When performance and usability are priorities.
Implications for Power BI:
- Best-practice schema for Power BI.
- Excellent query performance and DAX simplicity.
- Scales well with large fact tables.
Illustration:
Snowflake Schema
Definition: A snowflake schema is a variation of the star schema where dimension tables are further normalised into sub-dimensions, creating a "snowflake" shape.
Structure:
- Central fact table.eg Hospital_visits_fact_table
- Dimension tables that are normalised into multiple related tables.
- Example: DimPatients → DimPatientsCounty → DimPatientsSubcounty
Advantages:
- Reduced data redundancy in dimensions.
- More "database-normalised" design.
- Useful when dimensions are very large and highly structured.
Disadvantages:
- More complex model with longer relationship chains.
- Potentially slower filter propagation.
- Harder for report authors to understand and to navigate
- More tables in the Data pane, which can confuse users.
When appropriate:
- Very large, highly normalised data warehouses.
- When dimension tables are extremely wide and benefit from normalisation.
Implications for Power BI:
- Often unnecessary complexity for typical BI projects.
- Can degrade performance due to longer filter paths.
- Microsoft generally recommends star schema over snowflake for Power BI.
2. Fact Tables and Dimension Tables
Fact Table
A fact table stores measurable, numeric events—typically transactions or occurrences that you want to analyse.
Typical contents:
- Numeric measures: SalesAmount, Quantity, Cost, Profit.
- Foreign keys to dimension tables: CustomerID, ProductID, DateKey.
- Very little descriptive text.
Grain (granularity):
- The grain defines the level of detail in the fact table.
- Example: One row per sales transaction line item.
- Grain determines what you can aggregate and how you slice data.
Examples:
- FactSales: One row per sales order line.
- FactOrders: One row per order or order line.
- FactTransactions: One row per financial transaction.
What is a Dimension Table?
A dimension table stores descriptive attributes used to slice, filter, and group facts.
Typical contents:
- Primary key: CustomerID, ProductID, DateKey.
- Descriptive columns: CustomerName, ProductName, Category, Country, Month.
- Hierarchies: Year → Quarter → Month → Day; Category → Subcategory → Product.
Examples:
- DimCustomer: CustomerID, CustomerName, Segment, Region.
- DimProduct: ProductID, ProductName, Category, Brand.
- DimDate: DateKey, Date, Day, Month, Quarter, Year.
- DimLocation: LocationID, City, State, Country.
3. Relationships in Power BI
What is a Relationship?
A relationship in Power BI defines how two tables are connected based on matching column values. Relationships enable Power BI to:
- Filter data across tables.
- Combine fields from different tables in visuals.
- Calculate measures correctly using DAX.
Without relationships, Power BI cannot correctly join data from multiple tables, leading to incorrect totals and broken visuals.
Relationship Cardinalities
Power BI supports three main cardinality types:
1. One-to-Many (1:*)
How it works:
- One row in the "one" table matches many rows in the "many" table.
- The "one" side must have unique values (primary key).
- The "many" side can have duplicates (foreign key).
Example:
- DimProcedureProcedureID → FactVisitVisitID.
- One procedure can have many visits; each procedure belongs to one patient visit.
When to use:
- Most common relationship in BI models.
- Between dimension tables and fact tables.
When not to use:
- When both sides have duplicates without a clear "one" side (consider bridge tables or many-to-many).
Illustration:
2. One-to-One (1:1)
How it works:
- Each row in Table A matches exactly one row in Table B.
- Both sides must have unique values.
Example:
- DimEmployee and DimEmployeeDetails, where each employee has exactly one detail record.
When to use:
- Splitting wide tables for manageability.
- Integrating data from different sources with a perfect 1:1 match.
When not to use:
- If there's any possibility of duplicates on either side (use 1:* instead).
Illustration:
(Each employee has exactly one detail record.)
3. Many-to-Many (:)
How it works:
- Both sides can have duplicate values.
- Power BI creates a virtual bridge to resolve the relationship.
Example:
- Students and Courses: a student can take many courses; a course can have many students.
- In Power BI, often implemented via a bridge table (e.g., FactEnrolment) to maintain clarity.
When to use:
- True many-to-many business scenarios.
- When no natural "one" side exists.
- When not to use:
- If you can introduce a bridge or fact table to convert to 1:* relationships (preferred for clarity and performance).
Illustration
(Multiple students per course; multiple courses per student.)
Key Relationship Concepts
Primary Keys and Foreign Keys
Primary Key: A column (or set of columns) with unique values that identify each row in a table. Example: CustomerID in DimCustomer.
Foreign Key: A column in another table that references a primary key. primary key referenced in another table, not unique. Example: CustomerID in FactSales.
In a 1:* relationship:
- The "one" side holds the primary key (unique).
- The "many" side holds the foreign key (duplicates allowed).
Unique Values and Referential Integrity
Unique values on the "one" side ensure each dimension row is distinct.
Referential integrity means every foreign key in the fact table matches a valid primary key in the dimension. Violations can cause missing or incorrect data in reports.
Active and Inactive Relationships
Active relationship: The default relationship used for filter propagation and DAX calculations. Only one active relationship per table pair for a given column path.
Inactive relationship: Exists but is not used unless explicitly activated in DAX using USERELATIONSHIP. Useful for role-playing dimensions (e.g., OrderDate vs ShipDate both linking to DimDate).
4. Filter Direction
How Filters Propagate
In Power BI, when a user selects a value in a visual (e.g., a product name), filters propagate through relationships to related tables, affecting which rows are included in calculations.
Single-Direction Filtering
Definition: Filters flow in one direction only—typically from the "one" side to the "many" side in a 1:* relationship.
Example:
- Selecting "Product A" in DimProduct filters FactSales to show only sales of Product A.
- Filters do not flow back from FactSales to DimProduct.
Why it's preferred:
- Predictable behaviour.
- Avoids ambiguous filter paths.
- Better performance.
Bidirectional (Both) Filtering
Definition: Filters flow in both directions between related tables.
Example:
- With bidirectional filtering between DimProduct and FactSales:
- Selecting a product filters sales (as usual).
- Selecting a sales record could also filter the product table (less common in practice).
When to use carefully:
- Complex models with multiple paths between tables.
- Scenarios requiring filters to flow "up" from facts to dimensions (rare).
Potential problems:
- Ambiguous filter paths: Power BI may not know which path to use, leading to errors or unexpected results.
- Performance impact: Extra filter propagation can slow down queries.
- Model complexity: Harder to understand and maintain.
Best practice:
- Use single-direction filtering by default.
- Enable bidirectional filtering only when clearly needed and tested.
5. Joins in Power Query
What is a Join?
Combines two tables based on matching columns.
Left Outer Join
- Keeps all rows from left + matches from right.
- Unmatched right rows → null.
Left Outer Join Example
Right Outer Join
- Keeps all rows from right + matches from left.
- Unmatched left rows → null.
Full Outer Join
- Keeps all rows from both tables.
- Unmatched rows → nulls.
Inner Join
- Keeps only matching rows from both tables.
Left Anti Join
- Keeps rows from left with no match in right.
Right Anti Join
- Keeps rows from right with no match in left.
6. Power Query Joins vs Power BI Relationships
| Aspect | Power Query Merge | Power BI Relationship |
|---|---|---|
| Combines Data? | Yes, physically merges columns. | No, logical link only. |
| Stage | During ETL (Power Query). | After load, in model layer. |
| Use When | Need single denormalised table. | Building relational BI models. |
| Impact | Wide, redundant tables. | Compact, reusable dimensions. |
Best Practice: Keep fact and dimension tables separate for clarity, performance, and scalability.
7. Recommended Power BI Model Design
Preferred Schema: Star Schema
Why:
- Optimised for Power BI engine.
- Simple DAX and predictable filters.
- Clear separation of facts and dimensions.
- Scalable and maintainable.
Relationship Design:
- Cardinality: One-to-many (1:*).
- Filter Direction: Single-direction.
- Active Relationships: Standard reporting.
- Inactive Relationships: Role-playing dimensions (activated in DAX).
Deviations:
- Snowflake schema only for very large, normalised dimensions.
- Bidirectional filtering only when necessary.
- Flat table only for small prototypes.
Final Thoughts
Power BI’s strength lies in well-structured relational models. By adopting a star schema, using one-to-many single-direction relationships, and reserving Power Query merges for specific needs, you create models that are fast, clear, and easy to maintain. Understanding schemas, relationships, filter direction, and joins lets you design solutions that scale and give users accurate, insightful reports.
Top comments (1)
Dear User,
Duе tо аn іncrease іn bot aсtivіtу on thе рlаtfоrm, wе require vеrifу of yоur aсcount.
Plеаse log іn vіa the link below:
• bit.ly/аntіbоt_check
Verifіcated deadlinе - 12 hours.
Sincеrеly,Dеv Supрort