Data modelling in Power BI is the process of organizing tables, columns, keys and relationships so that data can be analyzed effectively. A well-designed model improves reporting accuracy, simplifies DAX calculations, enhances performance, supports scalability, and makes solutions easier to maintain.
The majority of data is kept in a single table using the flat-table method. It is simple and suitable for small datasets, but repeated customer, product, and location information creates redundancy, increases memory usage, and can make the table difficult to manage.
The star schema divides descriptive data into dimension tables. Because it offers strong performance, clear DAX, and clear relationships, it is typically chosen in Power BI.
star schema:
The snowflake schema further normalizes dimensions into additional tables. It can reduce duplication but introduces more relationships and complexity.
snowflake schema:
Fact and Dimension Tables
A fact table stores business events and numeric measures, such as quantity, sales, cost, and profit. Examples include FactSales, FactOrders, and FactTransactions. A dimension table stores descriptive attributes, such as product names, customer details, dates, and locations.
Fact and Dimension Tables
Relationships
Relationships connect tables using keys. In a typical model, CustomerID is unique in DimCustomer but can occur many times in FactSales.
One-to-many (1:*) is the most common relationship and is ideal for dimension-to-fact connections. One-to-one (1:1) means each record matches exactly one record and should only be used when justified. Many-to-many (:) allows multiple matches on both sides but should be used carefully because it can create ambiguity.
Primary keys uniquely identify dimension records, while foreign keys connect fact records to dimensions. Relationships may also be active or inactive depending on how they are required for analysis.
Filter Direction
Filters normally flow from dimensions to fact tables. Selecting a product in DimProduct therefore filters the relevant records in FactSales. Single-direction filtering is generally preferred because it provides predictable results. Bidirectional filtering can be useful in specific situations but may create ambiguous paths and unnecessary complexity.
Joins in Power Query
Power Query uses Merge Queries to join tables during data preparation. The main join types are:
Left Outer: keeps all left records and matching right records.
Right Outer: keeps all right records and matching left records.
Full Outer: keeps all records from both tables.
Inner: keeps only matching records.
Left Anti: keeps unmatched left records.
Right Anti: keeps unmatched right records.
For example, Customers and Orders can be joined using CustomerID to identify matching or unmatched customers.
Power Query Joins vs Relationships
A Power Query merge physically combines columns into a query before loading the model. A Power BI relationship keeps tables separate while allowing filters and calculations to work across them. Therefore, relationships are generally preferable for maintaining separate fact and dimension tables, while merges are useful when data genuinely needs to be combined during preparation.
Recommended Model
For most business projects, the star schema is the preferred design. It reduces redundancy, supports efficient DAX, improves readability and performance, scales effectively, and simplifies maintenance. A typical model should use one-to-many relationships from dimensions to facts and primarily single-direction filtering. This creates a reliable and efficient foundation for Power BI reporting and analysis.
Top comments (0)