SCHEMAS AND MODELLING IN POWER BI.
Published: (February 1, 2026 at 10:00 AM EST)
2 min read
Source: Dev.to
Source: Dev.to
Data Modelling
The process involves structuring, organizing, and connecting data tables for effective performance. Designing a good data model is important because it makes reports accurate and easy to understand. In Power BI, you set up a data model by opening the report view and navigating to Model. This involves bringing multiple data tables together and defining the relationships between them.
A schema is a structure that shows how data is organized and related in a data model.
Star Schema vs. Snowflake Schema
Star Schema
- Consists of one central fact table surrounded by multiple dimension tables.
- Dimension tables are connected directly to the fact table.
- Preferred in Power BI because it is simple to understand, executes DAX functions efficiently, and offers good query performance.
Snowflake Schema
- The fact table connects to dimension tables, which in turn connect to additional dimension tables.
- Less preferred due to its complexity, slower performance, and reduced efficiency for DAX calculations.
Key Differences
| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Structure | Fact table surrounded by dimension tables | Fact table → dimension tables → sub‑dimension tables |
| Joins required | Single join per dimension | Multiple joins (one per level of dimension) |
| Performance | Faster query and DAX execution | Slower due to additional joins |
Relationships
- One‑to‑many (1:*) – One record in a dimension table relates to many records in a fact table.
- Many‑to‑one (*:1) – The inverse view of a one‑to‑many relationship.
- Many‑to‑many (: ) – Multiple records in one table match multiple records in another table.
Fact Tables
- Store measurable, quantitative values (usually numeric).
- Examples: Revenue, Gross Profit, Total Sales, Quantity Sold.
- Contain many rows and relatively few columns.
- Used for calculations such as SUM, AVERAGE, MIN, MAX.
Dimension Tables
- Act as lookup tables for the values in fact tables.
- Examples: Employee Name, Month, Country, Region, Product Category.
- Provide descriptive attributes that give context to the facts.
Benefits of Good Data Modelling
- Data Accuracy: Reduces ambiguity and duplication.
- Performance: Fewer joins and relationships improve query speed (e.g., star schema).
- Simplified Analysis: Enables accurate and efficient DAX calculations.
- Data Integrity: Ensures consistent and reliable storage over time.
Understanding data modelling is essential for effective data analysis, reporting, and decision‑making. A well‑designed model delivers better performance and more reliable insights.