🚨 🚀 Power BI Roadmap — Topic 5
🏗️ Data Modeling in Power BI (Most Important Topic)
If DAX is the brain of Power BI, then Data Modeling is the backbone.
Many beginners focus on visuals and DAX but ignore data modeling. In reality, a well-designed model can:
✅ Improve report performance by 10x
✅ Reduce DAX complexity
✅ Minimize errors
✅ Improve scalability
✅ Simplify maintenance
👉 Most Power BI performance issues originate from poor data modeling.
🎯 Learning Objectives
By the end of this topic, you will understand:
✅ Fact Tables
✅ Dimension Tables
✅ Relationships
✅ Cardinality
✅ Star Schema
✅ Snowflake Schema
✅ Date Tables
✅ Model Optimization
📌 1. What is Data Modeling?
Data Modeling is the process of organizing data tables and relationships so Power BI can analyze data efficiently.
Example
Instead of putting everything into one large table:
❌ Bad Model
OrderID, Product, Customer, Region, Sales
We split data logically.
✅ Good Model
FactSales, DimProduct, DimCustomer, DimRegion, DimDate
📌 2. Fact Table
A Fact Table contains measurable business data.
Examples
OrderID, ProductID, CustomerID, Sales
Characteristics
✅ Large table
✅ Numeric values
✅ Transactional data
✅ Foreign Keys
Common Metrics
Sales, Revenue, Profit, Quantity, Cost
👉 Fact tables answer: "What happened?"
📌 3. Dimension Table
Dimension tables describe business entities.
Example
DimProduct
ProductID, ProductName, Category
DimCustomer
CustomerID, CustomerName, City
Characteristics
✅ Descriptive data
✅ Smaller tables
✅ Unique Keys
Dimensions answer: "Who?" "What?" "Where?" "When?"
📌 4. Understanding Relationships
Relationships connect tables.
Example
FactSales
ProductID
DimProduct
ProductID
Relationship: FactSales → ProductID → DimProduct
📌 5. Types of Relationships
🔹 One-to-One (1:1)
One record matches one record.
Example: Employee ↔ EmployeeProfile
Rarely used.
🔹 One-to-Many (1:_)
Most common relationship.
Example: One Product → Many Sales Transactions
DimProduct (1) → FactSales (_)
👉 Preferred relationship type.
🔹 Many-to-Many (_:_)
Multiple matches on both sides.
Example: Students ↔ Courses
Usually avoided when possible.
📌 6. Cardinality
Cardinality defines how tables relate.
Types
Type | Meaning
1:1 | One-to-One
1:_ | One-to-Many
_:* | Many-to-Many
Interview Question
Which cardinality is preferred?
✅ One-to-Many
Because it: Improves performance, Simplifies filtering, Reduces ambiguity
📌 7. Cross Filter Direction
Controls how filters flow.
Single Direction
DimProduct → FactSales
Recommended.
Both Directions
DimProduct ↔ FactSales
Use carefully. May create:
❌ Ambiguous relationships,
❌ Performance issues
📌 8. Active vs Inactive Relationships
Suppose Sales table contains: Order Date, Ship Date
Both connect to Date Table.
Power BI allows:
✅ One Active Relationship,
❌ Others become Inactive
Example
Sales → Date Active
Sales → Date Inactive[OrderDate][Date][ShipDate]
DAX Solution
Ship Sales =
CALCULATE(
SUM(Sales[Amount]),
USERELATIONSHIP(Sales[ShipDate], Date[Date])
)
📌 9. Star Schema (MOST IMPORTANT)
Industry-standard Power BI design.
Structure
DimDate
DimCustomer → FactSales → DimProduct
DimRegion
Characteristics
✅ Simple
✅ Fast
✅ Easy DAX
✅ Best Performance
Why Use Star Schema?
Because Power BI's engine is optimized for it.
👉 90% of enterprise Power BI models use Star Schema.
📌 10. Snowflake Schema
Dimension tables are further normalized.
Example
FactSales → DimProduct → DimCategory
Advantages
✅ Reduced redundancy
Disadvantages
❌ More joins
❌ Slower performance
❌ More complexity
Interview Answer
Power BI generally prefers: ✅ Star Schema over
❌ Snowflake Schema
📌 11. Date Table (Very Important)
Many DAX functions require a proper Date Table.
Date Table Includes
Date, Year, Month, Quarter, Week
Benefits
✅ Time Intelligence
✅ YTD, MTD, QTD
✅ Previous Year Analysis
Example
Calendar =
CALENDAR(DATE(2024,1,1), DATE(2026,12,31))
📌 12. Surrogate Keys
Artificial unique identifiers.
Example
Instead of: Product Name
Use: ProductID
Benefits
✅ Faster joins
✅ Better relationships
✅ Improved performance
📌 13. Model Optimization Best Practices
Remove Unused Columns
❌ Load 50 columns
✅ Use only required columns
Use Correct Data Types
Example: Sales ✅ Whole Number, ❌ Text
Reduce Cardinality
High-cardinality columns increase memory.
Examples: ❌ Transaction IDs, ❌ GUIDs
Prefer Measures
Instead of:
❌ Calculated Columns
Use: ✅ Measures
📌 14. Common Modeling Mistakes
❌ Fact-to-Fact Relationships: Avoid FactSales ↔ FactInventory
❌ Many-to-Many Overuse: Causes confusion
❌ Duplicate Keys: Dimension table keys must be unique
❌ Bi-Directional Relationships Everywhere: Can slow reports dramatically
📌 15. Real-World Model Example
Retail Dashboard
Fact Table
FactSales: ProductID, CustomerID, DateID, Quantity, Revenue
Dimension Tables
DimProduct, DimCustomer, DimRegion, DimDate
Relationship Structure
DimDate
DimCustomer → FactSales → DimProduct
DimRegion
📌 16. Interview Questions
1. What is Data Modeling?
2. Difference between Fact and Dimension tables?
3. What is Cardinality?
4. What is a Star Schema?
5. What is a Snowflake Schema?
6. What is a Date Table?
7. What is Cross Filter Direction?
8. What are Inactive Relationships?
9. What causes duplicate value errors?
10. Why is Star Schema preferred?
📌 17. Practice Project
🛒 Retail Sales Model
Tables: Sales, Products, Customers, Regions, Calendar
Tasks:
✅ Create Relationships
✅ Build Star Schema
✅ Create Date Table
✅ Test Filtering
✅ Validate Model
🎯 Goal of This Topic
After completing this topic, you should be able to:
✅ Design professional Power BI models
✅ Create relationships confidently
✅ Build Star Schemas
✅ Optimize performance
✅ Prepare for Data Modeling interview questions
Double Tap ❤️ For Part-6
🚀 Power BI Roadmap — Topic 4
📊 Power BI Basics
In this section, you'll learn:
- How Power BI works
- The Power BI ecosystem
- Connecting data
- Creating your first report
- Understanding the Power BI interface
📌 1. What is Power BI?
Microsoft Power BI is a Business Intelligence (BI) and Data Visualization platform developed by Microsoft.
It helps organizations:
✔ Analyze data
✔ Create reports
✔ Build dashboards
✔ Share insights
✔ Make data-driven decisions
📌 2. Components of Power BI
Power BI consists of three major components.
🔹 Power BI Desktop
Used for: Creating reports, Building data models, Writing DAX, Data transformation
👉 This is where developers spend most of their time.
🔹 Power BI Service
Cloud-based platform used for: Publishing reports, Sharing dashboards, Scheduled refresh, Collaboration
🔹 Power BI Mobile
- Used for: Viewing reports, Monitoring KPIs, Accessing dashboards on mobile devices
📌 3. Installing Power BI Desktop
Download Options:* Microsoft Store, Official Microsoft website
Installation Steps:
1. Download installer
2. Run setup
3. Complete installation
4. Launch Power BI Desktop
📌 4. Understanding the Power BI Interface
When Power BI opens, you'll see:
Main Sections:
Area | Purpose
Ribbon | Commands & tools
Report Canvas | Build visualizations
Fields Pane | Tables & columns
Visualizations Pane | Charts & visuals
Filters Pane | Filtering
📌 5. Three Main Views in Power BI
🔹 Report View
Used to:
✔ Create reports,
✔ Add charts,
✔ Build dashboards
Icon: 📄 Report
Most work happens here.
🔹 Data View
Used to:
✔ Inspect data,
✔ Create calculated columns,
✔ Verify loaded tables
Icon: 📋 Table
🔹 Model View
Used to:
✔ Create relationships,
✔ Build star schemas,
✔ Manage data models
Icon: 🔗 Relationship
📌 6. Connecting Data Sources
Power BI supports hundreds of data sources.
Common Sources:
Files: ✔ Excel, ✔ CSV, ✔ XML, ✔ JSON
Databases: ✔ SQL Server, ✔ MySQL, ✔ PostgreSQL, ✔ Oracle
Cloud: ✔ Azure, ✔ SharePoint, ✔ Google Analytics
Web: ✔ APIs, ✔ Websites
📌 7. Get Data Process
Steps:
1. Click "Get Data"
2. Choose source
3. Connect
4. Load or Transform
Example:
Excel File: Sales.xlsx
Power BI imports: Sheets, Tables, Named Ranges
📌 8. Import vs DirectQuery vs Live Connection
🔹 Import Mode
Data is loaded into Power BI memory.
Advantages:
✅ Fast performance,
✅ Full DAX support,
✅ Better user experience
Disadvantages:
❌ Requires refresh
🔹 DirectQuery
Data remains in database.
Advantages:
✅ Real-time data
Disadvantages:
❌ Slower performance
🔹 Live Connection
Direct connection to enterprise models.
Example: SSAS Tabular Models
📌 9. Loading Data
After connecting:
Options:
Load: Directly loads data
Transform Data: Opens Power Query Editor
Used for:
✔ Cleaning data,
✔ Removing duplicates,
✔ Formatting columns
👉 In real projects, you'll often choose Transform Data first.
📌 10. Creating Your First Visualization
Suppose you have:
Product | Sales
Laptop | 50000
Phone | 30000
Create Bar Chart:
1. Select Bar Chart
2. Drag Product → Axis
3. Drag Sales → Values
Power BI automatically generates a chart.
📌 11. Understanding Visualizations Pane
Contains Charts:
✔ Bar Chart,
✔ Column Chart,
✔ Line Chart,
✔ Pie Chart,
✔ Area Chart,
✔ Scatter Plot
Advanced Visuals:
✔ KPI Card,
✔ Gauge,
✔ Waterfall,
✔ Funnel,
✔ Matrix
📌 12. Understanding Fields Pane
Shows: Tables, Columns, Measures
Example:
Sales Table
├─ Product
├─ Quantity
├─ Revenue
Used to build visuals.
📌 13. Understanding Filters Pane
Three levels:
Visual-Level Filter: Affects one visual
Page-Level Filter: Affects one page
Report-Level Filter: Affects entire report
📌 14. Saving Power BI Files
File Extension: .pbix
Contains:
✔ Data,
✔ Model,
✔ DAX,
✔ Reports
📌 15. Publishing Reports
Steps:
1. Save PBIX
2. Click Publish
3. Sign in
4. Select Workspace
5. Publish
Report becomes available in Power BI Service.
📌 16. First Mini Dashboard
Create:
KPI Cards: Total Sales, Total Orders
Charts: Sales by Product, Sales by Region
Filters: Region, Month
📌 17. Common Beginner Mistakes
❌ Loading unnecessary columns
❌ Ignoring data types
❌ Using too many visuals
❌ Poor naming conventions
❌ Skipping Power Query cleaning
📌 18. Practice Project
🛒 Sales Dashboard
Dataset: Product, Region, Sales
Tasks:
✔ Import Excel Data
✔ Create: Bar Chart, Line Chart, KPI Cards
✔ Add Filters
✔ Publish Report
📌 19. Interview Questions
1. What is Power BI?
2. Difference between Desktop and Service?
3. What are the three views in Power BI?
4. What is Import Mode?
5. What is DirectQuery?
6. What is a PBIX file?
7. How do you publish reports?
8. What is a Workspace?
9. What is Power Query?
10. What is a Dashboard?
🎯 Goal of This Topic
After this topic you should be able to:
✅ Install Power BI
✅ Connect data sources
✅ Load data
✅ Create visualizations
✅ Build simple dashboards
✅ Publish reports
Double Tap ❤️ For Part-5