Schemas and Data modelling in Power BI.

Source: Dev.to

Dimensional schemas

Star schema

The star schema is the industry‑standard modelling approach for Power BI. It gets its name from its visual layout: a central fact table surrounded by dimension tables, resembling a star.

Components of a star schema

• Fact table – stores quantitative or measurable data (e.g., sales amount, quantity).
• Dimension tables – store descriptive data (e.g., date, product name).
• Relationships – define how fact and dimension tables are connected and how filters flow between them.

Features of dimension tables

• Fields – columns that hold attribute values.
• Primary keys – unique identifiers for each row.
• Attributes – descriptive columns (e.g., product category, region).
• Foreign keys – keys in the fact table that reference dimension primary keys.
• Measures – calculations based on fact data (e.g., total sales).

Relationships in a star schema

• One‑to‑Many – one row in a dimension table links to many rows in the fact table.
• One‑to‑One – each row in one table matches exactly one row in another table.
• Many‑to‑One – many rows in the fact table relate to a single dimension row.
• Many‑to‑Many – multiple rows in both tables can match each other (requires bridge tables).

Normalization vs. denormalization

• Normalization reduces redundancy by storing data in multiple related tables.
• Denormalization flattens related dimension data into wider tables to improve analytical performance and simplify reporting.

Snowflake schema

The snowflake schema normalizes dimension tables into third normal form (3NF) or higher, creating a more granular, hierarchical structure.

Key features of a snowflake schema

• Normalized dimension tables – split into smaller sub‑tables to eliminate repeated values.
• Hierarchical structure – dimensions are organized into layers (e.g., product → category → manufacturer).
• Reduced data redundancy – minimizes inconsistencies by storing each value once.

Components of a snowflake schema

• Fact table – core table storing primary quantitative data and multiple foreign keys to dimension tables; typically the largest table.
• Normalized dimension tables – provide descriptive context and are further split into sub‑dimension tables.
• Lookup tables – first level of description for the fact table.
• Sub‑dimension tables – created by “snowflaking” main dimension tables, forming hierarchical layers.

Key fields in a snowflake schema

• Primary keys – unique identifiers in dimension and sub‑dimension tables (e.g., ProductID).
• Foreign keys – fields in the fact or dimension tables that reference related primary keys.
• Surrogate keys – system‑generated integers used instead of natural business keys to improve performance.

Importance of good modelling

• Prevents logical errors (e.g., double counting or missing data).
• Clear naming conventions and hidden technical fields make the model accessible to non‑technical users.
• Simple relationships between a central fact table and surrounding dimensions reduce ambiguity.
• Centralized dimension tables ensure consistent definitions across visuals, avoiding conflicting totals.
• A modular model allows new data sources or dimensions to be added without rebuilding the entire system.