DEV Community

Cover image for #Schemas and Data Modelling in Power BI
Victor Karanja
Victor Karanja

Posted on

#Schemas and Data Modelling in Power BI

#webdev #beginners

Schemas and Data Modelling in Power BI

Data modelling is one of the most important steps in Power BI. A well-designed data model improves performance, accuracy, and ease to analyse, 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 Data Modelling?

Data Modelling involves organizing data sources into a structured model. In Power BI, this means organizing tables and defining relationships between them to support accurate reporting and efficient calculations.

What Is a Schema?

A schema is the structure and organization of data in a table. It's the logical arrangement of tables used in reports on Power BI.

In Power BI, 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
-Snowflakes schema
-star schema

Snowflakes schema

Here dimensions are split into sub dimensions, that can also be split into further smaller tables.

_snowflake diagram_

Star schema

It's the mostly schema used in Excel, where it has multiple dimensions and one fact table.
its advantages are;

  • Faster query performance
  • Easier to write DAX formulas
  • Better filter -Easy to understand.

_star schema image_

Fact Tables

A fact table stores quantitative data (measures) that can be analysed.

Characteristics of Fact Tables:

  • Contain numerical values
  • Have many rows
  • Reference dimension tables using keys

Examples:

  • Costomer Id
  • Name
  • Age
  • Region

Dimension Tables

A dimension table contains descriptive information that provides context to facts.

Characteristics of Dimension Tables:

  • Contain text or categorical data
  • Have fewer rows than fact tables
  • Used for filtering and grouping data

Examples:

  • Date
  • Product
  • Customer ID
  • Location

What Is a Relationship in Power BI?

A relationship connects a column in one table to a column in another table, usually through a key.

Example:

  • Sales[ProductID] → Product[ProductID]

This allows Power BI to understand how records relate across tables.

Relationship Types in Power BI

One-to-Many

This is the most common and recommended relationship type.

  • One record in a dimension table
  • Many matching records in a fact table

Example:

  • One product → many sales records

Used in star schemas.

Many-to-Many (:)

Occurs when both tables contain duplicate values.

  • Can cause ambiguous results
  • Should be avoided when possible

Use only when necessary and with caution.

One-to-One (1:1)

  • Each value appears once in both tables
  • Rarely used in analytical models

Relationship Direction

Single Direction (Recommended)

  • Filters flow from dimension tables to fact tables
  • Predictable and efficient behavior

Best practice for star schemas.

Both Direction (Bi-Directional)

  • Filters flow both ways
  • Can cause confusion and performance issues

Use only when absolutely necessary.

Relationships in Star Schema

In a star schema:

  • All dimension tables connect directly to the fact table
  • Relationships are one-to-many
  • Filter direction is single

This results in:

  • Faster performance
  • Simpler DAX formulas
  • Accurate filtering

Relationships in Snowflake Schema

In a snowflake schema:

  • Dimension tables connect to other dimension tables
  • More relationships are required
  • More joins occur during queries

This can:

  • Reduce performance in Power BI
  • Complicate filter behavior
  • Make DAX harder to write and maintain

Why Relationships Matter in Power BI

Correct relationships:

  • Ensure accurate calculations
  • Control how filters behave
  • Improve report performance
  • Prevent incorrect totals

Poor relationships can lead to:

  • Incorrect results
  • Slow visuals
  • Broken slicers and filters

Best Practices for Power BI Relationships

  • Use star schema whenever possible
  • Keep relationships one-to-many
  • Use single-direction filtering

- Avoid many-to-many relationships

Conclusion

Good data modelling is the foundation of effective Power BI reporting. By using well-structured schemas such as the star and snowflake schema 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.

Top comments (0)