DEV Community

akello
akello

Posted on

# Data Modelling, Relationships & Joins in Power BI

#architecture #data #database #performance

1. Data Modelling

A data model is the structural blueprint linking your tables so Power BI can filter, aggregate, and calculate correctly. A well-designed model improves DAX simplicity, report performance, scalability, and long-term maintainability.

Schema Structure Pros Cons Best For
Flat Table One wide table, all columns together Simple, no relationships needed High redundancy, slow on large data, hard to maintain Small, one-off datasets
Star Schema One fact table + denormalized dimensions directly connected Fast performance, simple DAX, easy to navigate Some data redundancy in dimensions Most standard BI reporting
Snowflake Schema Dimensions normalized into sub-dimensions Less redundancy, smaller storage More joins/relationships, slower, complex DAX Very large, highly structured dimension hierarchies 
Flat Table:            Star Schema:                 Snowflake Schema:
[ One big table ]      DimCustomer   DimDate         DimCustomer─DimRegion
                            \         /               DimProduct─DimCategory
                          FactSales                        \      /
                            /        \                    FactSales
                     DimProduct   DimLocation                /
                                                        DimDate
Enter fullscreen mode Exit fullscreen mode

2. Fact vs Dimension Tables

Fact tables (e.g., FactSales, FactOrders) store measurable, numeric business events (SalesAmount, Quantity) plus foreign keys — defined at a specific grain (e.g., one row per order line). Dimension tables (DimCustomer, DimProduct, DimDate, DimLocation) store descriptive attributes used to filter and group facts. Example: FactSales connects to DimProduct, DimCustomer, DimDate, and DimLocation — a classic star schema.

3. Relationships

A relationship links tables via keys so filters flow correctly across the model.

  • One-to-Many (1:*) — e.g., DimCustomer[CustomerID] → FactSales[CustomerID]. Standard for fact-to-dimension links.
  • One-to-One (1:1) — e.g., Employee ↔ EmployeeDetails. Rare; usually signals tables should be merged.
  • Many-to-Many (*:*) — e.g., Students ↔ Courses. Use only when no unique key exists; avoid otherwise due to ambiguity risk.

Key concepts: Primary Key (unique row identifier, e.g., CustomerID in DimCustomer), Foreign Key (repeats in FactSales to link back), Cardinality, Referential Integrity (every fact key must exist in its dimension), and Active vs Inactive relationships (only one active path between two tables at a time; inactive paths need USERELATIONSHIP in DAX).

4. Filter Direction

  • Single-direction: filters flow dimension → fact (default, recommended). Selecting a product in DimProduct filters FactSales.
  • Bidirectional: filters flow both ways — useful for many-to-many bridges, but risks ambiguous filter paths and unnecessary model complexity. Use sparingly.

5. Joins in Power Query (Merge)

Join Type Keeps
Left Outer All of left table + matches from right
Right Outer All of right table + matches from left
Full Outer All rows from both tables
Inner Only matching rows from both
Left Anti Left rows with no match in right
Right Anti Right rows with no match in left

Example: Customers ⋈ Orders — Inner join returns only customers who placed orders; Left Anti returns customers who never ordered.

6. Power Query Joins vs Power BI Relationships

A Merge in Power Query happens at the ETL stage and physically combines columns into one new table. A Relationship happens at the model stage and links tables logically without duplicating data. Use merges sparingly (only when you truly need combined columns) — excessive merging flattens the model, increases redundancy, and reduces scalability. Keeping facts and dimensions separate, linked by relationships, preserves a clean, performant star schema.

7. Recommendation

For most BI projects, a Star Schema is preferable: it balances performance, simple DAX, redundancy, and readability far better than a flat table (too redundant/slow at scale) or snowflake (unnecessary join complexity for typical reporting needs). Implement one-to-many relationships from dimensions to the fact table, keep filters single-direction by default, and reserve bidirectional or many-to-many relationships for specific bridging scenarios only.
PowerBI_Data_Modelling_Article.md
Displaying PowerBI_Data_Modelling_Article.md.

Top comments (0)