Introduction
Data modeling is a key step when working with Power BI. Before creating dashboards, data must be structured in a clear and logical way. A good data model improves performance, reduces errors, and makes analysis easier.
This article explains data modeling, SQL joins, Power BI relationships, schemas, and how to create them step-by-step.
What is Data Modeling?
Data modeling is the process of organizing data into tables and defining how those tables are connected.
It involves:
- Structuring tables
- Defining keys
- Creating relationships
Why it matters
- Faster reports
- Accurate calculations
- Better organization
SQL Joins (With Diagrams)
Assume two tables:
- Customers (ID, Name)
- Orders (ID, CustomerID)
1. INNER JOIN
Returns only matching records.
Example: Customers who made orders
2. LEFT JOIN
Returns all records from the left table and matching ones from the right.
Example: All customers, including those without orders
3. RIGHT JOIN
Returns all records from the right table and matching ones from the left.
Example: All orders, even if customer details are missing.
4. FULL OUTER JOIN
Returns all records from both tables.
Example: All customers and all orders
5. LEFT ANTI JOIN
Returns rows from the left table with no match.
Example: Customers who never ordered
6. RIGHT ANTI JOIN
Returns rows from the right table with no match.
Example: Orders without customers
Creating Joins in Power BI (Step-by-Step)
- Open Power BI Desktop
- Click Transform Data
- Select a table
- Click Merge Queries
- Select the second table
- Choose matching columns
- Select join type (Inner, Left, Right, Full, Anti)
- Click OK
- Expand columns
Power BI Relationships
Relationships connect tables without merging them.
Types of Relationships
1. One-to-Many (1:M)
One record relates to many
Example: One customer → many orders
2. Many-to-Many (M:M)
Both tables contain repeated values
3. One-to-One (1:1)
Each record matches exactly one
Active vs Inactive
- Active: Used automatically
- Inactive: Used only when specified
Cardinality
Defines how tables relate:
- One-to-many
- Many-to-many
Cross Filter Direction
- Single direction
- Both directions
Creating Relationships in Power BI (Step-by-Step)
Method 1: Model View
- Open Model View
- Drag a column from one table to another
Method 2: Manage Relationships
- Click Manage Relationships
- Click New
- Select tables
- Select columns
- Set cardinality
- Choose cross-filter direction
- Click OK
📸 Insert Screenshot: Relationship configuration dialog box
Joins vs Relationships
| Joins | Relationships |
|---|---|
| Combine tables | Link tables |
| Power Query | Model View |
| Used in data preparation | Used in analysis |
Fact vs Dimension Tables
Fact Table
Contains numeric data used for analysis.
Examples:
- Sales
- Revenue
Dimension Table
Contains descriptive data.
Examples:
- Customer
- Product
- Date
Data Schemas
Star Schema
- One central fact table
- Connected dimension tables
Use case: Best for Power BI performance
Snowflake Schema
- Dimension tables split into smaller related tables
Use case: Complex datasets
Flat Table (DLAT)
- All data in one table
Use case: Small or simple datasets
Role-Playing Dimensions
A single dimension used multiple times.
Example:
- Order Date
- Ship Date
- Delivery Date
Common Data Modeling Issues
- Duplicate keys
- Incorrect relationships
- Many-to-many confusion
- Circular relationships
- Poor performance
Best Practices
- Use star schema
- Keep models simple
- Avoid unnecessary joins
- Validate relationships
Conclusion
Data modeling is the foundation of Power BI. Understanding joins, relationships, and schemas helps build accurate, efficient, and scalable reports.
Top comments (0)