Data Modelling and schemas in power BI.

Introduction.

Mastering data modeling is the "secret sauce" that separates a slow, confusing Power BI report from one that is lightning-fast and dead-accurate. If your visuals are lagging or your numbers don't seem to add up, the problem usually isn't the visual it’s the model. This article explains schemas and data modelling in Power BI using simple understandable language.

What Is Data Modelling in Power BI?

Data modelling is the process of Organizing data into tables and defining relationships between those tables.

A good data model helps:

• Reports run faster
• Calculations give correct results
• Dashboards remain easy to maintain

Fact tables vs Dimension tables

Fact Tables: These store the numbers. Think of them as a list of events.

Note: They are usually very long (millions of rows) and contain Foreign Keys to connect to other tables.

Dimension Tables: These provide the context. They answer the "Who, What, Where, and When."
Examples: Product names, Customer details, Store locations...

Note: They are usually wider (many descriptive columns) but shorter than fact tables.

Star Schema

The star schema is the most recommended data model in Power BI.

Structure:

• One central fact table
• Multiple dimension tables
• All dimensions connect directly to the fact table

It looks like a star ⭐.

Why Star Schema Is Best

• Easy to understand
• Faster report performance
• Works best with DAX
• Fewer relationship error

Snowflake Schema

A snowflake schema is a more complex version of the star schema.

Structure:

• Dimension tables are split into multiple related tables
• Dimensions connect to other dimensions

Snowflake Schema Example:
Instead of one Product table:

Product → Subcategory → Category

This reduces data duplication but increases complexity.

Downsides of Snowflake Schema

• More relationships
• Slower performance
• Harder to debug errors
• More complex DAX formulas

In Power BI, star schema is usually preferred over snowflake schema.

Relationships in Power BI

Relationships define how tables are connected.

Common Relationship Types;

• One-to-Many (most common)
• Many-to-One
• Many-to-Many

One-to-Many Relationship
Example: One Product → Many Sales records

Product ID appears once in the Product table but many times in the Sales table.

Many-to-Many Relationship
Occurs when: Both tables contain duplicate keys

This type can cause:

• Incorrect totals
• Confusing result

Direction: Always aim for Single Direction filtering (from the Dimension to the Fact table). Bi-directional filtering can cause "ambiguity," where Power BI gets confused about how to apply filters, leading to incorrect numbers.

Why data modelling is critical.

• Performance: Power BI’s engine (VertiPaq) is designed to compress and scan columns in a Star Schema. Large flat tables eat up memory and make slicers feel sluggish.

• Accuracy: Proper modeling prevents "double counting." If your relationships are messy, a simple SUM might return a number way higher than reality.

• DAX Simplicity: Writing formulas (DAX) is much easier when your model is clean. A Star Schema makes time-intelligence functions (like "Sales Year-over-Year") work instantly.

Common Data Modelling Mistakes.

• Using too many fact tables
• Mixing fact and dimension data in one table
• Creating unnecessary many-to-many relationships
• Using snowflake schemas when star schemas work better

Conclusion

Data modelling is the foundation of every Power BI report.
By using fact and dimension tables, applying a star schema, and creating proper relationships, you ensure your reports are fast, accurate, and scalable.

Great visuals start with great models — design your data right, and Power BI will do the rest 📊✨