POWER BI - SCHEMAS & MODELLING GUIDE.
Published: (February 2, 2026 at 12:21 PM EST)
4 min read
Source: Dev.to
Source: Dev.to
What is Power BI?
Power BI is a Business Intelligence tool built by Microsoft that turns raw data into interactive insights.
It has a very user‑friendly interface that lets you create beautiful, comprehensive dashboards easily.
Power BI gained its popularity because of its versatility: it can pull data from numerous sources (Excel, CSV files, databases, web APIs, etc.), clean it, analyze it, and then create visuals that are easy to understand.
Types of Power BI Applications
| # | Application | Description |
|---|---|---|
| 1 | Power BI Desktop | Free Windows application for creating reports and dashboards. Connect your data, clean it, build visualizations, and save your reports. |
| 2 | Power BI Service (Cloud) | Cloud‑based version. Publish reports created in Desktop to share, collaborate, and get real‑time updates. |
| 3 | Power BI Mobile | Mobile app (iOS & Android) that lets you view and interact with your reports on the go. |
| 4 | Power BI Report Server | On‑premises server for securely hosting Power BI reports within your own infrastructure. |
Getting Started with Power BI Desktop
-
Download and Install
- Go to the Microsoft Store on your PC or the official Power BI website and install Power BI Desktop.
-
Open Power BI Desktop
- After installation, launch the application.
- Click Blank report to start a new project.
-
Load Data
- Click Get Data on the ribbon.
- Choose your file type (e.g., Excel → .xlsx, CSV → .csv).
- Select the file, preview the data, and click Load.
What is Data Modelling?
Data modelling is the process of organizing and structuring data to create meaningful relationships between tables, enabling efficient analysis and visualization.
Core Concepts
| Concept | Definition |
|---|---|
| Fact Table | Central table in a star schema that stores quantitative, measurable data (e.g., sales amount). |
| Dimension Table | Stores descriptive attributes (e.g., product name, customer location, time period) that provide context for the facts. |
| Relationships | Define how tables interact. Built on Primary Key (unique identifier) and Foreign Key (reference to a primary key). |
| Cardinality | Describes the nature of the relationship: • One‑to‑One (1:1) – each row in Table A matches exactly one row in Table B. • One‑to‑Many (1:M) – a single row in Table A matches multiple rows in Table B. • Many‑to‑Many (M:M) – multiple rows in Table A match multiple rows in Table B. |
Star Schema
- Structure: A central fact table directly connected to multiple dimension tables, forming a “star” shape.
- Features
- Simple to design and maintain.
- Dimension tables are denormalized (flat).
- Optimized for fast read performance, making it ideal for BI tools like Power BI and Tableau.
- Disadvantage: Higher storage consumption and data redundancy.
Snowflake Schema
- Structure: A central fact table linked to normalized dimension tables, which may further break down into sub‑dimension tables, creating a “snowflake” shape.
- Features
- Reduces redundancy through normalization.
- Suited for complex data models that require strict data integrity.
- Can lead to slower query performance due to additional joins.
- Disadvantages
- More complex to navigate for ad‑hoc reporting.
- Higher maintenance overhead.
Key Differences Between Star and Snowflake Schemas
| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Normalization | Denormalized | Normalized |
| Number of Joins | Fewer joins | More joins |
| Storage | Uses more storage space | Uses less storage space |
| Performance | Faster analysis (read‑optimized) | Slower due to extra joins |
| Data Integrity | Good enough for most BI scenarios | Higher data integrity |
Building Your First Data Model in Power BI
-
Load Data
- Import data from your preferred source (Excel, databases, web APIs, etc.).
-
Clean & Prepare Data (Power Query)
- Click Transform data under the table view to open Power Query.
- Remove blanks, duplicates, and correct data types.
-
Create Relationships
- Switch to Model view in the ribbon.
- Drag‑and‑drop fields to connect tables or use Manage Relationships.
- Define the relationship type (e.g., 1:M) and set the cross‑filter direction.
-
Design the Schema
- Identify fact and dimension tables to form a star schema.
- Use a surrogate key (unique ID) when natural keys are inconsistent.
-
Add Calculated Columns & Measures
- Use DAX (Data Analysis Expressions) to create derived columns or measures for deeper insights.
-
Optimize the Model
- Remove unnecessary columns/rows.
- Prefer measures over calculated columns when possible to keep the model lean.
Features of Power BI Data Modeling
Model View
- Visual canvas to see tables, relationships, and cardinalities.
Cross‑Filter Direction
| Direction | Description |
|---|---|
| Single | Filters flow in one direction only. |
| Both | Filters flow in both directions – useful for complex models. |
Role‑Playing Dimensions
- Same dimension table used multiple times (e.g., OrderDate, ShipDate).
Calculated Tables
- Tables created with DAX formulas for advanced scenarios.
Hierarchies
- Build drill‑down paths (e.g., Year → Quarter → Month → Day).
Importance of Modelling in Data Analysis
- Improves Query Performance – Well‑designed models reduce the amount of data scanned and the number of joins required.
- Ensures Data Consistency – Proper relationships and cardinalities prevent ambiguous results.
- Facilitates Self‑Service BI – End users can explore data confidently when the underlying model is clean and intuitive.
- Scales Efficiently – Optimized schemas handle larger datasets without degrading performance.
Now you have a clean, well‑structured overview of Power BI, its components, and the fundamentals of data modelling. Happy analyzing!
## e:
- **Data Integrity & Consistency:**
- **Scalability:**
- **Improved Decision-Making:**
- **Single Source of Truth:**
Without proper modeling, organizations often face slow, buggy reports, conflicting numbers, and high maintenance costs.
*Reference: “Data analysis made easy” by Ezekiel Akele.*