SCHEMAS AND DATA MODELLING IN POWER BI

Source: Dev.to

What is a schema?

What is data modelling in Power BI?

Core concepts of data modelling

Star Schema

The gold standard for Power BI. It consists of a central fact table (containing quantitative data) surrounded by dimension tables (descriptive data).

Snowflake Schema

A data‑warehouse modeling technique where dimension tables are normalized into multiple related sub‑tables.

Features of Snowflake Schema

• Normalization – Reduces redundancy and improves consistency.
• Hierarchical Structure – Built around a central fact table with connected dimension tables.
• Multiple Levels – Dimensions can be split into multiple levels, allowing detailed drill‑down analysis.
• Joins – Requires more joins, which can slow performance on large datasets.
• Scalability – Scales well for large data, but its complexity makes it harder to manage.

Relationships in Power BI

Types of table relationships

• One‑to‑One (1:1) – Each row in the first table is related to only one row in the second table.
• Many‑to‑One (*:1) – Many rows in the first table are related to one row in the second table.
• One‑to‑Many (1:*) – One row in the first table is related to one or more rows in the second table.
• Many‑to‑Many (:*) – Each row in the first table can be related to multiple rows in the second table. This type requires an intermediate table to link the two tables.

Fact and dimension tables

• Fact tables contain the data we want to analyze, typically transactional in nature, and include keys to the related dimensions.
• Dimension tables provide information to describe, categorize, group, or filter the data in the fact tables.

Why good data modelling is critical for performance and accurate reporting

Performance Optimization

• Optimized compression
• Reduced query time
• Faster data refresh
• Efficient memory usage
• Avoidance of “many‑to‑many” chaos

Accurate & Consistent Reporting

• Single source of truth
• Correct filter propagation
• Time‑intelligence capabilities
• Handling ambiguity
• Separation of logic