#Schemas and Data Modelling in Power BI
Source: Dev.to
Introduction
Data modelling is one of the most important steps in Power BI. A well‑designed data model improves performance, accuracy, and ease of analysis, while a poor model can lead to slow reports and incorrect insights. This article explains schemas and data‑modelling concepts in Power BI.
What Is a Schema?
A schema is the logical arrangement of tables used in Power BI reports. Schemas help determine:
- How data is connected
- How filters flow between tables
- How efficiently queries are executed
Types of Schemas in Power BI
Galaxy Schema
Dimensions are split into sub‑dimensions, which can be further divided into smaller tables.
Star Schema
The most common schema in Power BI (and Excel). It consists of:
- One fact table
- Multiple dimension tables
Benefits
- Faster query performance
- Simpler DAX formulas
- Predictable filter behavior
- Easy to understand
Snowflake Schema
Dimension tables are normalized into additional dimension tables, creating a more complex network of relationships.
Implications
- More joins during queries, which can reduce performance
- More complex filter behavior
- DAX formulas become harder to write and maintain
Fact and Dimension Tables
Fact Table
- Stores quantitative data (measures) for analysis
- Contains numerical values and many rows
- References dimension tables using keys (e.g.,
CustomerID)
Dimension Table
- Provides descriptive information that gives context to facts
- Contains text or categorical data, usually fewer rows than fact tables
- Used for filtering and grouping (e.g., Date, Product, Customer, Location)
Relationships
A relationship connects a column in one table to a column in another, typically through a key.
Example
Sales[ProductID] → Product[ProductID]One‑to‑Many (Recommended)
- One record in a dimension table relates to many matching records in a fact table
- Used in star schemas
- Filters flow from dimension to fact tables (single direction)
One‑to‑One (Rare)
- Each value appears once in both tables
- Filters flow both ways, which can cause confusion and performance issues
- Should be used only when necessary
Many‑to‑Many (Avoid When Possible)
- Occurs when both tables contain duplicate values
- Can cause ambiguous results and should be avoided unless required
Best Practices for Relationships
- Keep relationships one‑to‑many whenever possible
- Use single‑direction filtering (dimension → fact)
- Prefer star schemas for simplicity and performance
- Ensure relationships are correctly defined to avoid incorrect totals, slow visuals, and broken slicers
Star vs. Snowflake Schemas
| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Structure | All dimension tables connect directly to the fact table | Dimension tables connect to other dimension tables |
| Relationship type | One‑to‑many | One‑to‑many (plus additional relationships) |
| Filter direction | Single (dimension → fact) | May involve multiple directions |
| Performance | Faster due to fewer joins | Potentially slower due to extra joins |
| Complexity | Simpler DAX and maintenance | More complex DAX and maintenance |
Conclusion
Good data modelling is the foundation of effective Power BI reporting. By using well‑structured schemas such as the star (or, when necessary, snowflake) and defining correct relationships, you can improve performance, ensure accurate calculations, and create reports that are easy to understand and maintain. Investing time in proper modelling leads to faster insights and more reliable decision‑making.