Tools: Schemas and Data Modelling in Power BI: A Beginner-Friendly Step-by-Step Guide

1. Introduction ## 2. What Is Data Modelling in Power BI? ## Why is Good Data Modelling Critical? ## 3. Fact Tables and Dimension Tables ## 3.1 Fact Tables ## 3.2 Dimension Tables ## 4. What Is a Schema? ## 5. Star Schema (Highly Recommended) ## 5.1 What Is a Star Schema? ## 5.2 Why Star Schema Is Best in Power BI ## 5.3 Step-by-Step: Creating a Star Schema in Power BI ## Step 1: Load Your Data ## Step 2: Check Tables in Data View ## Step 3: Go to Model View ## Step 4: Create Relationships ## 6. Snowflake Schema ## 6.1 What Is a Snowflake Schema? ## 6.2 Why Snowflake Is Less Ideal in Power BI ## 7. Understanding Relationships in Power BI ## 7.1 Cardinality ## 7.2 Filter Direction ## 8. Why Good Data Modelling Is Critical ## 8.1 Performance ## 8.2 Accuracy ## 9. Common Beginner Mistakes (Avoid These) ## 10. Conclusion Data modelling is one of the most important skills when working with Power BI. Even if your data is correct, poor data modelling can lead to slow reports, incorrect numbers, and confusing visuals. This article explains, from scratch, how schemas and data modelling work in Power BI. It is written so that a beginner can follow step by step, click by click, and build a correct model. Data modelling is the process of: Think of data modelling like designing the foundation of a house. If the foundation is weak, everything built on top will fail. • Performance: Well-designed models load faster and respond to queries more quickly • Accuracy: Proper relationships ensure your calculations and aggregations are correct • Scalability: Good models can handle growing data volumes without performance degradation • Maintainability: Clean models are easier to update and troubleshoot • User Experience: Fast, accurate reports lead to better business decisions In Power BI, data modelling is done mainly in: Fact tables are the central tables in your data model that contain quantitative data (measures) about your business processes. Think of them as "what happened" tables. A fact table stores measurable business data (numbers). Characteristics of a fact table: Example: Sales_Fact table A Sales fact table might contain: • SaleID (Primary Key) • DateKey (Foreign Key to Date dimension) • ProductKey (Foreign Key to Product dimension) • CustomerKey (Foreign Key to Customer dimension) • StoreKey (Foreign Key to Store dimension) • Quantity (Measure) • SalesAmount (Measure) • Cost (Measure) • Profit (Measure) A dimension table stores descriptive information (context). Examples of dimensions: Characteristics of dimension tables: Example: Product_Dimension table A schema is the structure that defines how fact and dimension tables are connected. In Power BI, the two most common schemas are: It looks like a star shape. ✔ Faster performance ✔ Simpler DAX formulas ✔ Accurate aggregations ✔ Easy to understand Power BI’s engine (VertiPaq) is optimized for star schemas. A snowflake schema is when: ❌ Slower performance ❌ More complex relationships ❌ Harder DAX formulas Power BI works better when dimensions are denormalized (flattened). 📌 Best Practice: Convert snowflake schemas into star schemas where possible. 📌 Rule: Filters should flow from Dimension → Fact Poor modelling can cause: Correct modelling ensures: ❌ Multiple fact tables directly connected ❌ Many-to-many relationships everywhere ❌ Snowflake schemas without need ❌ Using fact tables as filters ✔ Always design with a star schema mindset Data modelling is not optional in Power BI—it is essential. When your data model is clean, everything else becomes easier—DAX, visuals, and performance. Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse - What data modelling means in Power BI - Fact tables vs Dimension tables - Star schema and Snowflake schema - Relationships (one-to-many, cardinality, filter direction) - Why good modelling improves performance and accuracy - How to create a clean data model in Power BI (step-by-step) - Organizing tables - Defining relationships between tables - Structuring data so Power BI can analyze it efficiently - Model View (Relationships diagram) - Data View (Table structure) - Sales amount - Quantity sold - Contain numerical measures (sales amount, quantity, cost, profit) - Usually have many rows (can contain millions of records) - Contain foreign keys that link to dimension tables - Represent business events or transactions - Are typically narrow (fewer columns) but very long (many rows) - Contain text or categories - Smaller than fact tables - Used for filtering and grouping - Star Schema ⭐ (recommended) - Snowflake Schema ❄️ - One central fact table - Multiple-dimensional tables directly connected to it - Open Power BI Desktop - Click Home tab - Click Get Data - Choose your source (Excel, CSV, SQL, etc.) - On the left panel, click the Data icon (table icon) - Click each table - Fact table (numeric columns) - Dimension tables (descriptive columns) - Click the Model icon (diagram icon) on the left - You will see tables as boxes - Drag ProductID from Product_Dim - Drop it onto ProductID in Sales_Fact - Power BI opens the Create Relationship window - Cardinality: One to Many (1:*) - Cross filter direction: Single - Make sure the dimension is on the 1 side - Dimension tables are connected to other dimension tables - One-to-Many (1:*) → Most common and recommended - Many-to-Many → Avoid unless absolutely necessary - Single Direction (recommended) - Both Directions (can cause wrong totals) - Slow visuals - High memory usage - Fast reports - Efficient calculations - Double counting - Incorrect totals - Misleading reports - Correct aggregations - Reliable business decisions - Use fact tables for numbers - Use dimension tables for descriptions - Build star schemas - Use one-to-many relationships - Keep filter direction single