Detailed Article on Data Modelling using Power BI

Data modelling is one of the most important aspects of data analysis, regardless of the tools we use, and Power BI is not an exception. After we import data into the data model, we need to create and manage relationships, create analytical calculations and implement the business logic available for data visualization.

Relationships in Data Modelling

Relationships between tables are necessary to accurately calculate the results and visualize the correct information in the report. 

When we create a relationship between two tables, we are creating a linkage between the data stored in those tables in one of the following ways. Let’s imagine we have two tables, Table X and Table Y:

1. A row of data from Table X is related to one and only one row of Table Y. This type of relationship is called a One-to-one relationship. Like when we stored the personal details of a customer in a table called Customer and we stored the customers’ address in a table called Address and a customer has only one address. 
2. A row of data from Table X is related to many rows from Table Y. This type of relationship is called a One-to-many relationship. In our previous example, a customer can have many sales orders, so if we stored the sales orders in a Sales table, then each customer from the Customer table relates to many rows of sales data from the Sales table. 
3. A row or data from Table X is related to many rows of data from Table Y AND a row of data from Table Y is related to many rows of data from Table X. This type of relationship is called a Many-to-many relationship.

Now that you know about the different types of relationships in Power BI, let’s see how to identify the columns contributing to a relationship.

Identifying Key Columns in Power Query

To be able to create a relationship, we firstly need to identify the key columns in both related tables. There are two types of key columns, Primary Key and Foreign Key.

Primary Key and Foreign Key

In data modelling, we refer to tables as Entities. A table consist of columns holding the data. Each column describes an Attribute of an Entity. 

The columns (attributes) hold the data that describe Records of columns (attributes). 

Each record of data in a table (entity) is a row. 

For instance, in a Product table (entity), the columns (attributes) of the table contain the product data. Each row (record) of data describes a single product. 

In data modelling, we normally have a column or a combination of columns who can describe a unique row, hence we do not need to mention all columns to describe a single unique row (record) of data. The column or columns that describe a unique row are so-called Primary Key columns. The primary keys consist of a combination of columns and are so-called Composite Keys. 

We always create relationships between the Primary Key of a table and its corresponding Foreign Key in another table. Let’s look at our imaginary tables - Product and Sales - to get a better understanding of the relationships.

The ProductKey column is the Primary Key of the Product table and a Foreign Key in the Sales table. 

We create a relationship between Product and Sales using the ProductKey column in both tables. As you can see in Figure 36, every single value of ProductKey from the Product table has many corresponding values in the Sales table; the relationship between the two tables is a One-to-many relationship. Creating a relationship between the two tables will look like Figure 2: the (1) resembles the One side of the relationship and the (*) resembles the Many side of that relationship.

Let’s add another set of tables to our existing set of imaginary tables, Customer and Customer Address:

each customer from the Customer table has one and only one corresponding address in the Customer Address table. Therefore, the relationship between the two tables is a One-to-one relationship.

Power BI is not only a reporting tool. It’s an analytical tool that you can create data models. The data model in Power BI includes tables and their relationships. There’s a specific tab in Power BI called the Model View which is placed in the left pane of the Power BI Desktop.

Power BI automatically detects some relationships

To manage relationships click on manage relationships.

So this blog was on relationships you can handle the relationships on your own data by correctly identifying primary and foreign keys. Always remember many problems can be solved if you establish proper relationship between the data features. Data modelling is one of the very important step when analysing data using Power BI.

I hope you liked the blog. We will again meet in the next blog.

Happy Learning !