Schemas and Data Modelling in Power BI - Core Concepts:
Source: Dev.to
What is Data Modelling?
Data modelling in Power BI is the practice of organizing tables and defining relationships between them. It involves analyzing different data types and creating connections that enable meaningful analysis.
Benefits of Data Modelling
- Saving time and money – Proper planning reduces unnecessary processing and investment.
- Well‑structured data system – Facilitates easy connection between business data and analysis.
- Reducing errors – Prevents redundant data from entering the system.
- Tracking and setting targets – Provides indicators and timely performance targets aligned with business objectives.
An example of data modelling in Power BI:
Schemas
Schemas are logical frameworks that organize tables, relationships, and data structures. They act as a blueprint for the semantic model.
Star Schema
The star schema is the most commonly used schema in Power BI. It classifies tables as either dimension or fact tables.
Fact Table
A fact table contains:
- Dimension key columns that relate to dimension tables.
- Numeric measure columns.
The dimension keys determine the table’s dimensionality, while the key values set the granularity. Dimension tables usually have fewer rows, whereas fact tables can contain a large and growing number of rows.
Snowflake Schema
The snowflake schema is a variant of the star schema where dimension tables are normalized into multiple related tables.
Key Characteristics
- Hierarchical structure centered around a fact table.
- Multiple levels of dimension tables for more granular analysis.
- Joins: Typically requires more complex SQL queries involving multiple table joins.
Comparison: Star vs. Snowflake
Among the two, the star schema is more widely used because it is easier to understand, well‑defined, and enables faster querying and smoother report interaction.