INTRODUCTION
I'm currently learning Power BI and I keep hearing about data modeling. Honestly it sounded very complicated but when i researched and practiced, I realized it's just about organizing data in an organized manner.
In this article I'll explain data modeling in a simple way. I'll cover joins, relationships, schemas and how do do them on power BI step by step.
What is Data Modeling?
Data modeling is simply arranging your data into tables and connecting them so that Power BI can analyze them properly.
SQL Joins Explained
Joins help in combining data from different tables. They include inner join, left join, right join, full outer, left anti and right anti join.
Let's say for example we have this sample data:
Customers Table
| ID | Name |
|---|---|
| 1 | John |
| 2 | Alice |
Orders Table
| Order ID | Customer ID |
|---|---|
| 001 | 1 |
| 002 | 2 |
| 003 | 3 |
Inner Join
The inner join returns only rows that have matching values in both tables. Therefore using our data, inner join will only show customers who have data that is Customer ID 1&2.
Left Join
This returns all rows from the left table (table1), and only the matched rows from the right table (table2).
Good for checking inactive customers.
Right Join
This returns all rows from the right table (table2), and only the matched rows from the left table (table1).
Helps in finding data errors.
Full Outer Join
This returns all rows when there is a match in either the left or right table.
Useful when comparing datasets
Left Anti Join
This shows customers with no orders.
Helps in businesses when finding inactive users.
Right Anti Join
This shows orders that don't have customers.
Helps clean bad data.
How to Do Joins in Power BI
- Open Power BI Desktop
- Click Transform Data
- Click Merge Queries
- Select the your tables[in this case we have two]
- Select the matching column[like customer ID]
- Choose join type
- Click OK
- Expand the columns
- Click Close & Apply
Relationships in Power BI
Joins and relationships may seem alike but they are not. Relationships connect tables without merging them.
Types of Relationships
- One-to-Many One Customer to many orders
- Many-to-Many Many items relate to many others
- One-to-One Used when splitting tables
Cardinality
This just means how tables are connected:
- One-to-one
- One-to-many
- Many-to-many
Cross Filter Direction
This determines which direction filters can flow between tables. A single-direction flow (from dimension to fact table) is preferred to prevent ambiguity and performance issues.
Active vs Inactive Relationships
Active relationships ( appear as solid lines) automatically filter data in visuals, while inactive relationships (appear as dashed lines) exist but are ignored by default. Only one active path can exist between two tables to prevent ambiguity.
How to Create Relationships
- Go to Model View
- Drag one column to another OR
- Go to the Modeling tab and click Manage Relationships
- Click New to open the creation dialog box
- Select the two tables you want to connect
- Click on the column(s) in each table that share data.
- Configure the Cardinality (usually One-to-Many 1:) and **Cross- filter direction* (usually Single).
- Click OK
Join vs Relationships
Joins combine tables while relationships connect tables.
Joins are done in Power Query while Relationships are done in Model View
Facts and Dimension Tables
Just to put it in a simple way; Fact Tables contain numbers[sales, revenue]while Dimension Tables contain descriptions[customer name, product name].
Schemas
A schema refers to the structure and organization of data within a data model. Schemas define how data is connected and related within the model, influencing the efficiency and performance of data queries and reports. There are two types of schemas: Star schema and Snowflake schema.
Star Schema
It has the fact table at the center connected to the others[Dimension tables]. This is commonly used.
Snowflake Schema
The snowflake schema is a normalized version of the star schema but the dimension tables are further divided into related tables, resulting in a more complex structure.
Flat Table
A flat table combines all columns into one table. It is commonly used when the dataset is small and is beginner-friendly.
Common Modeling Issues in Power BI
Most Power Bi problems come from a bad data model. Here are some of the common problems:
- Flat Tables may result to too much repeated data.
- Many-to-Many relationships lead to wrong totals.
- Incorrect data types cause failure in calculations.
- Wrong relationships lead to inefficiency of the data.
Conclusion
Learning Power Bi may seem a bit confusing or overwhelming . But over time you realize that it's really about one simple idea: Organizing your data in a way that makes analysis easy and accurate. Therefore understanding data modeling in power BI is very essential for building accurate reports.
Top comments (0)