Schemas and Data Modelling in Power BI

Power BI powered by Microsoft is a business analytics platform that helps you turn data into actionable insights. Data engineers use Power BI primarily to validate data pipelines, monitor ETL performance, prototype semantic models, and deliver high-level dashboards that showcase data quality, lineage, and system health. On the other hand data analysts use Power BI to connect to, clean, model, and visualize raw data from multiple sources, turning it into interactive reports, dashboards, and actionable business insights.

DATA MODELLING

Data modelling in Power BI is the process of organizing, structuring, and connecting your data to make it usable for analysis and reporting.
What It Involves:

1. Importing/Connecting Data - Involves bringing data from various sources (Excel, databases, web, etc.)
2. Creating Relationships - Linking tables together (e.g., connecting Sales to Product via ProductID) and defining how tables interact with each other
3. Building Table Structure -Deciding which tables are fact tables (metrics) vs dimension tables (descriptions) and organizing into star or snowflake schemas
4. Creating Calculated Columns & Measures -Adding new columns using DAX formulas and creating measures for aggregations (Total Sales, Average Price, etc.)
5. Optimizing Performance - Done by doing the following: Removing unnecessary columns, Setting correct data types, Creating hierarchies (Year → Quarter → Month) and Managing cardinality and relationships
6. Defining Business Logic - That is by: Setting up date tables for time intelligence, Creating calculated tables and establishing row-level security

Schemas

A schema is a collection of database objects, including tables, views, and indexes. It defines the structure of the data and the relationships between the different objects. In Power BI, a schema represents a logical grouping of tables that are related to each other.

Two Main Types of Schemas

1. Star Schema (Most Common)
It contains one central fact table (contains metrics/measurements like sales, quantities, costs) which is then surrounded by multiple dimension tables (these contain descriptive attributes like customer names, product categories, dates)
Each dimension connects directly to the fact table
It is simple, fast, and optimized for reporting
Example:
Fact Table: Sales (Amount, Quantity, Date_ID, Product_ID, Customer_ID)
Dimension Tables: Date, Product, Customer, Store

2. Snowflake Schema
It is similar to star schema but dimension tables are broken into sub-dimensions
Has a more complex structure with dimensions connecting to other dimensions
it is Less common in Power BI because it can slow performance
Star schemas keep everything in one dimension table (fast, one hop). Snowflake schemas split it across multiple tables (slow, multiple hops). Power BI prefers the simpler star approach.
Example:
Product dimension splits into Product → Category → Subcategory (multiple levels)

Why Schemas Matter in Power BI:

-Performance: A well-designed star schema makes your reports faster because Power BI's engine is optimized for this structure.
-DAX Calculations: Relationships in your schema determine how measures and calculations work. DATEADD, CALCULATE, and other functions rely on proper schema relationships.
-Data Clarity: A good schema makes it easier to understand what data connects where, making report building more intuitive.

Snowflake vs. Star Schema

There are 6 key differences between star schema and snowflake schema:

1. A star schema has denormalized dimension tables, while a snowflake schema has normalized dimension tables.
2. A star schema is easier to design and implement than a snowflake schema.
3. A star schema can be more efficient to query than a snowflake schema because there are fewer JOINs between tables.
4. A star schema can require more storage space than a snowflake schema, because of the denormalized data.
5. A star schema can be more difficult to update than a snowflake schema, because of the denormalized data.
6. A star schema can be more difficult to troubleshoot than a snowflake schema, because of the denormalized data

Importance of Data Modelling in Power BI

1. Accuracy Ensures correct calculations and prevents errors Filters and relationships work properly across tables
2. Performance Faster report loading and data refresh Handles large datasets efficiently
3. Scalability Easily accommodates growing data and new sources Supports business expansion without rebuilding
4. Maintainability Clear structure makes updates simple Others can understand and work with your model
5. Consistency Single source of truth across all reports Centralized business logic eliminates conflicting numbers
6. User Experience Simple, intuitive report building Fast, responsive dashboards Enables self-service analytics
7. Advanced Capabilities Supports complex DAX calculations Enables time intelligence and drill-through features