Power BI Data Modelling, Relationships and Joins: A Practical Guide to Building Effective BI Models

Introduction

Businesses and organisations collect large volumes of data in their day to day operations. Many a times, these data in itself does not answer management questions or rather help in decision making. It is therefore important for the data to be analyzed.
The data collected is analysed, cleaned, structured, connected and thereafter analysis made from the data.
Powerbi is a microsoft office software solutions that allows data analyst to transform data into interactive dashboard and reports and behind the interactive dashboard is a well thought out data model that determine how tables interact.
Consider an institution like an hospital that stores patient data, doctors data, sickness data, visits data, ward data, lab data, pharmacist data etc. Data from various departments will be stored in tables/spreadsheets. It therefore becomes imperative for one to align/model the tables in a way they can interact with each other so as to answer business questions in a seamless manner. For this to be possible, one would need to understand data modeling, relationships and joins.
Data Modeling is the Process of organising your table and defining relationship between your tables so that tables can work together effectively within Powerbi. A well thought out model helps Powerbi understand how the tables are related.
There are two distinct concept when building up a model. These are;

• Relationships - connects tables in Powerbi
• Joins - Is performed in power query during the data preparation stage where columns from two different tables can be combined to a query. This article will delve into principles behind Powerbi modeling, discuss concepts such as fact table, dimension table, star schemas, snowflake schemas, cardinality, Primary Key and Foreign Key among others. I will use a practical example of Hospital records in this article so that I can be able to demonstrate the concepts clearly.

Understanding Data Modelling in Power Bi

What is Data Modelling
Data Modelling is the process of organising your table and defining relationship between your tables so that you may use Power Bi to summarise/use your data correctly.
A Power Bi Model will contain several tables containing different aspects of business records. For a example, an institution like a hospital may have records of patients in one table, doctors records in another table, another table containing sickness records, another table containing Lab records, another with Pharmacy records, another with Finance records etc. Data modelling will thus eatblish a relation how this records/tables will interact to form meaningful data presentation.
A Power Bi model will likely to contain a fact table connected to several dimensional tables.
Its important therefore to clearly understand the differences between a fact table and a dimensional table.
Facts table - is the table that holds specific events/transactions that you are looking forward to making analysis. It is characterised with the following features.

• It contains numerical records or events
• Holds data metrics
• Holds data in quantities
• Helps to get a grain about the data. Grain refers to a description of what each record in a table consists of.
• Contains primary keys and foreign keys

Dimension table - A table that gives a description of specific events held in the fact table. It is characterised with the following features;

• They have descriptive contents
• Mainly you will see texts that describe events
• Every dimensional table has a primary key

The model will basically look as follows;

Above is an example of a star schema.

The model may also be presented in the following manner as a snowflake schema.

The main difference is that a star schema has one fact table surrounded by dimensional tables while snow flake schema is like a star schema, however, the dimensional tables have been broken down to smaller tables.

The fact table and dimensional table will be developed from the flat table. A flat table is one table containing all information and you will find a lot of duplication.

The advantages of a star schema are;

• Easy to understand
• Simple DAX calculations
• Less data repetition
• Easy reporting
• Easy to maintain

The Disadvantages are;

• It may necessitate one to have many tables
• It requires relationship as wrong keys or table may result in misleading results
• Some repetition may still remain

Star schema is frequently applicable in the following cases;

• When the data contains clear business figures and descriptive values
• Where users need to evaluate the same figures from different scenarios.

The advantage of Snow Flake Schema is as follows

• It reduces redundancies
• Shared information can be stores at a glance
• Useful for complex structures

The disadvantages are;

• Potentially more complex DAX calculations
• There may be need to have many relationships to manage
• May make the model complex rather than simplyfying it

Snow flake schema is applicable in the following scenarios;

• Complex or highly structured dimensions
• Business with high level of hierachy
• When you have different entities that need to be handled independently
• Where reducing duplication is very important.

NB - Grain referes to level of information represented by each row in a fact table. It will answer the question what does each row in the fact table represent.

Relatiosnhip in Power Bi

Relationship refers to relationship between two tables therefore making it possible for Power Bi to analyse two tables without having to combine them. It is very important especially where data are stored in fact table and several dimensional table.
The relatiosnhip is established by matching the foreign key in the facts table to the primary key in the dimensional table.
A primary key is a column that uniquely identifies each record in a table while a foreign key is a column in another table that refers to a primary key in another table usually in a dimensional table. Therefore we establish relationships by matching primary key on the dimensional table to the foreign key in the facts table.

Relationship cardinality

Refers to how data in one table relates to data in another based on the numerical count of matching rows.
The types of cardinality are;

• Relationship cardinaility
• Column cardinality

Relationship cardinality

These shows how tables are connected.

In Power Bi, the main relationship cardinality are;

• One to Many cardinality - one row matching to many rows in another table. This is the most common type of cardinality and most applicable in a star schema. Mainly used when one record in a dimensional table is associated with many records in the fact table. For example one doctor in dim table can attend to many patients in the fact table.

• Many to One cardinality - Many rows in a table matching one unique row in another table.
  A good example in our case is when many visits can occur in one ward.

• One to One - A row in a table that can only match/link to one row in another table.
  For example Patient dim to Patient details dim. One patient has one patient records and one patient records belongs to one patient and therefore the relationship is one to one. It is applicable when each record in one table corresponds exactly to another table.

• Many to many - Multiple rows that can link into multiple rows in another table. This is when many items in one table can be associated with other many items in another table. For example one patient can have various diagnosis and at the same time various one diagnosis can relate to many patients.

Column cardinality

Show the uniqueness of data inside a single column.

Filtering

This determines how filtering travels between two relatable tables.

Joins

Joins allows one to combine two tables using matching columns. In the current case of hospital records, joins allows us to join patients visits records with doctors dim, ward dim etc

The type of joins are;
Types of Joins

• Inner Join - Used when you want to retain records that match (keeps only the records that match on both tables)
• Left outer join - Used when used want to retain/keep all rows from the left cable (first table) and matching rows from the second table
• Right outer join - Used when you want to keep all the rows from the second table and the matching rows from the first table.
• Full outer join - used when you want to keep all the rows from all the tables whether they match or not
• Left Anti join - used when you want to keep only the rows in the first table that have no match in the second table
• Right Anti join - used when you want to keep only the rows from the second table that have no match in the first table.

Conclusion

Behind every dash board sits a well outlined data model with well structured fact and dimensional table whose relationship have been clearly set. The primary keys and foreign keys relate well across the table. At the centre we have patient visits facts table surrounded by dimensional tables such as Doctors_dim, ward_dim, department_dim, Procedure_dim, Diagnsosis_dim and thus forming a star schema. Having a clear understanding of fact table, dimensional table, primary keys, foreign keys, grain, relationship, cardinality, filter directions and joins helps one to develop a good model.