Power Bi: Data Modelling, Relationships and Joins

Data Modelling

Data modelling involves organizing, connecting, and structuring data from different tables so that Power BI can analyze and summarize data it correctly.

Why is data modelling important?

1. Makes data easy to analyze as it organizes information into fact and dimension tables.
2. Ensures correct relationships by enabling information from different tables to work together
3. Reduces data errors and duplication as separating information into appropriate tables avoids unnecessarily repeating the same data.

Types of Tables

1. Fact Table

• This contains the quantitative data that represents the transactions and events and holds numeric values that will be analyzed.
• Fact table contains foreign keys. For instance, consider a hospital record dataset, the fact table contains foreign keys pointing to the related dimension tables like information about the departments, diagnosis, the doctors, patient information as well as other information such as admission type, insurance, length of stays.

2. Dimension Table

• This contains descriptive attributes used to analyze the information in the fact table.
• Dimension table contains primary key which creates a relationship with the fact table foreign key. In the hospital records dataset, the dimension tables contain information such as department ID, diagnosis ID, doctor ID, patient ID

3. Flat table
This is a single table where all the information needed for analysis is stored in one place, rather than being separated into multiple related tables.

Types of Schemas

Schema is a way of organizing and structuring tables and their relationships.

1. Star schema
Data is organized into one central fact table surrounded by dimension tables.
The fact table is at the center and the dimension tables are around the fact table.

Advantages of a Star Schema

• Simple and easy to understand
• Reduces data duplication
• Makes analysis easier

Disadvantages of a start schema

• Requires proper relationship management as incorrect relationship can result to inaccurate analysis
• Data needs to be organized into appropriate fact and dimension tables before building the report

When is a star schema appropriate?

• When working with large datasets that contain different types of information.
• Building Powerbi dashboards that require interactive filtering and analysis
• Creating reports that will be updated regularly, because the model is easier to maintain.

2. Snowflake schema
A snowflake schema is a model where dimension tables are broken into sub-dimensions, resembling a snowflake shape.

Advantages of snowflake schema

• Provides better data organization as related information can be separated into logical tables.
• Reduces data duplication as repeated information can be separated into its own table.

Disadvantages of snowflake schema

• More relationships to maintain as incorrect relationships can lead to inaccurate results.
• Can make reporting more difficult for users as users may need to understand several tables before they can identify the information they need.
• May not be necessary for simple datasets

When is a snowflake schema appropriate?

• Where dimensions are extremely large with repetitive hierarchies
• When working with a large and complex dataset

Primary Key

• Think of a primary key as an ID number that help tables in a dataset talk to each other.
• This is a unique identifier in a table. In this instance, the primary key is the dimension table of dim_patients is patients Id in dim_doctors is doctors Id in dim_departments is departments Id

Foreign Keys

This is a primary key used in another table. In this instance, the primary keys from the dimension table are seen in the fact table and therefore referred to as foreign keys.

Understanding Relationships in Power Bi

When working with Power BI, data is often stored in multiple tables. For example, a hospital records dataset may have separate tables for patients, hospital visits, departments, doctors and treatments.

A relationship is a connection between two tables that shows how the data in those two tables are related.

For instance, in the hospital records dataset, we have the following tables:
Dim_Patients

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
2	John Kariuki	Male
3	Grace Naliaka	Female

We also have
Hospital visits table

Visit ID	Patient ID	Department
1001	1	Outpatient
1002	4	Laboratory
1003	3	Pharmacy

Both tables contain Patient ID. This allows the relationship to connect the two tables

In the Patients table, Patient ID is the primary key because each patient has a unique ID.

In the Visits table, Patient ID is the foreign key because it refers back to the patient in the Patients table.

This is a one-to-many (1:*) relationship.

It means that one patient can have many hospital visits.

One-to-One (1:1)
One record in one table corresponds to exactly one record in another table.
For instance, Patient ID and Patient Medical Profile tables

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
2	John Kariuki	Male
3	Grace Naliaka	Female

Patient ID	Blood Group	Insurance No
1	O+	INS 001
2	A+	INS 002
3	B+	INS 003

Here, each patient has one medical profile.

This is a one to one (1:1) relationship

The Patient ID in the Patients table identifies the patient, while Patient ID in the Medical Profile table is used to connect that patient's profile to them.

Many-to-Many (:)
Many records in one table can be related to many records in another table.
The hospital records dataset provides a good example as in a table of Patients and Doctors.

A patient can see several doctors and at the same time, a doctor can see many patients, so many patients can see many doctors

Understanding Joins

Data is often stored in different tables. For example, a hospital records dataset may have separate tables for patients, visits, doctors, and departments.

Step 1:Load the data, then select home and click of transform data to go to power query.

Step 2: Select the first table and head to merge queries. The first table selected will be the left table.

Step 3:In the merge window, select the table you want to join with the first table. From the two table, select the column from each table that are common from both tables.

Step 4: Choose the join type from the dropdown menu

A join allows us to combine data from two tables using a common column. For instance, the Patients and hospital visits tables may both contain Patient ID. This common column can be used to join the two tables.

1. Inner Join
An inner join returns only records that have a matching value in both tables.
In this hospital records table, the dim_patients and the hospital visits table, it will only return the common values in the table which is:

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
3	Grace Naliaka	Female

2. Left Outer Join
A left outer join keeps all records from the left table and adds matching information from the right table.

3. Right Outer Join
It keeps all records from the right table and brings in matching records from the left table.

4. Full Outer Join
A full outer join keeps everything from both tables. Provides everything from both tables, whether they match or not.

For instance the patients table has patients Id 1,2,3 while the visits table has patients Id 1,3,4 Therefore, the

Full outer join will provide 1,2,3,4

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
2	John Kariuki	Male
3	Grace Naliaka	Female
4	Ahmed Ahmed	Male

5. Left Anti Join
This is useful for finding records in the left table that do not exist in the right table.

For instance the Patient table: 1,2,3 and Hospital visits table: 1,4,3.

The left anti join returns 2

6. Right Anti Join
It returns records that exist in the right table but not the left table.

For instance the Patient table: 1,2,3 and Hospital visits table: 1,4,3.

The left anti join returns 4

Recommendation and Conclusion

For a Business Intelligence (BI) project, I would use a star schema as the main data modelling approach, with a fact table at the centre and dimension tables surrounding it.

For example, in a hospital analysis project, the model could contain:

• Fact Visits – records of hospital visits, patients information, departments, doctors, and treatment administered.
• Dim Patient – patient information.
• Dim Department – department information.
• Dim Doctor – doctor information, where required.

The fact table would contain foreign keys that connect it to the dimension tables through one-to-many (1:*) relationships.

Dim Patient (1) → Fact Visits (*)
This means one patient can have many visits, while each visit belongs to a particular patient.

Overall, the goal is to keep the model simple, well-structured, and suitable for accurate reporting and analysis.