# Understanding Data Modeling in PowerBI: Joins, Relationship and Schemas.

Introduction.

Data Modeling is a good backbone of making Data Science and Analytics in PowerBI very successful. We are going to look into:

• Data modeling fundamentals.


• SQL Joins.


• PowerBI relationships.


• Facts vs deimension tables.


• Data schema.

The big Question; WHAT is data modeling.

Well Data modeling is the process of organizing and structuring data from multiple sources into a logical format for analysis.

Here it is all about:

• The Table connection.


• Defining relationships.


• Structuring data into facts and dimension tables.


• Optimizing performance.

Just imagine all this like making your data easy and smooth to read and interpret.

SQL joins.

This is basically where we use joins to make data jointed from multiple tables using a common column.

1. INNER JOIN.

This only returns matching records in both tables.

2. LEFT JOIN.

Here in the records are all returned from left side of the table and matching the records from the right.

3. RIGHT JOIN.

This is basically the vice versa of LEFT JOIN, the records from the right table and matching ones from the left.

4. FULL OUTER JOIN.

This returns all records from both tables whether matched or unmatched.

5. LEFT ANTI JOIN.

The returning record in the table that do not exist in the right.

6. RIGHT ANTI JOIN.

The returns records in the right table that do not exist in the left.

Steps in the creation of join in PowerBI.

1. Open PowerBi desktop.


2. Select the datasets you want to use.


3. Click on transform data.


4. Select a table.


5. Click on merge queries.


6. Choose the second table.


7. Select on matching columns.


8. Choose the join type of your choice.


9. Click OK and expand the columns

PowerBI Relationships.

The datasets can connects to each other without ever merging them.

Types of Relationships

1. One to many. (1:M)

Here is where a table from dataset A relates to many tables in dataset B. Example; A customer can make many orders.

2. One to One.(1:1)

In this relationship the table matches exactly one in another table.

3. Many to Many.(M:M)

Here both tables from datasets contains duplicate values.

Key concepts.

Cardinality.

This is where tables are defined by how they relate;

• One-to-many.


• Many-to-many.


• One-to-one.

Cross filter direction.

In this we get to understand;

Both directions and allows two way filtering but may cause ambiguty.

Single direction.

Active and inactive relationships.

In this relationship the active is usually the default and indirect usually requires the DAX.(User relationship)

Facts vs Dimension Tables.

Fact Table.

This is where it contains data that can be measured.

Example: Sales Amount.

Dimension Table.

Here the data is descriptive.

Example: Name of a customer.

DATA MODELLING SCHEMAS.

They are;

1. Star Schema.

Structure:

• One Central fact table.


• Multiple dimension tables.

2. Snowflake Schema.

They usually normalize structures where dimensions are split.

It has an advantage of saving storage.

3. Flat Table.

In this schema all the data is stored in one table.

It is very simple but also has poor performance on large datasets.

CONCLUSION.

Data modelling in PowerBI is essential for building accurate and high-performing dashboards. By understanding joins, relationships, schemas and table structures you can create models that are both efficient and scalable.