How to Create Data Modeling in Power bi Step by step process

 

Introduction to Data Modeling in Power BI

Overview Of Power BI

Microsoft Azure provides a secure hosted cloud service with plenty of tools to help you manage and visualize your data effectively. Power BI is a business analytics tool that offers interactive visualizations with a simple interface. It is used to create business reports and dashboards with better data visualizations. Also, it is much faster than Excel in terms of processing.

Power BI Tools

• Power BI Desktop – It is a desktop application for computers
• Power BI Service – It is available online and can be accessed on any browser
• Power BI Apps – Available on Android & iOS platform for mobile devices

What Is Data Modeling?

Data Modeling is the process of creating a data model. These Data Models are abstract data that defines the data structure, properties and relation. To create a Data Model, multiple entities or values for a given scenario must relate to each other. A data model can be of different types based on structure shape and properties. Some types of data models are the Hierarchical Model, Relational Model, Network Model, Entity-Relationship Model and many more. For Power BI, we will be more focused on the Entity-Relationship Model.

Entity-Relationship Model

The entity is an object, subject or event in a model. For Example, colour, engine model & mileage are entities that define the car. Thus Entity-Relationship model is the relation between multiple entities. This model is also known as ER Model.

Table 1
ID1	Name	Type
01	Porsche	Sports
02	Porsche	Sports
03	Volkswagen	Hatchback

Table 2
ID2	Car ID	Model	Colour
A	01	Model 1	Red
B	01	Model 1	Yellow
C	02	Model 3	Blue

In a table, Entities are the values (like Porsche, Sports, Red), whereas Entity Attributes are the characteristics and properties (like ID, Name, Type, Colour). The ER Model is not complete without the knowledge of some important keys. In a table, these keys represent a special property.

Types of Keys

• The Primary key is unique in nature and doesn’t repeat itself in records. In the above table, ID is the primary key that doesn’t repeat and is useful in identifying a unique record.
• The Foreign key is the key that makes a relation to another table. This key may or may not be unique in nature. In Table 2, ‘Car ID’ is the Foreign Key that adds up the values to the previous table.

In data modelling, cardinality plays an important role in defining the type of relation. To understand data models, it is crucial to learn cardinality.

What is Cardinality?
Cardinality defines the number of relation from one table to another table. There are mainly 3 types of cardinality in a table.

• One to One Cardinality – In this type of cardinality, One entity attribute creates only one connection with another attribute.
• One to Many Cardinality – In this cardinality, one entity attribute creates multiple connections with another attribute. In the above tables, the entity (01) from Table 1 (ID1) creates multiple links with the entity (01,01) from Table 2 (Car ID).
• Many to Many Cardinality – In this cardinality, multiple entities from one table creates multiple relations with other tables. In the above tables, the entity (Porsche, Porsche) from Table 1 (Name) creates multiple links with the entity (Model1, Model1) on Table 2 (Model).

Check Out: How to create a Paginated Report in Power Bi. Click here

Getting Familiar With Power BI

You can use power BI in two ways. The first method is to use Power BI Desktop on your system. Another method is to use the Power BI service online using the browser. Power BI Desktop provides more tools and features than Power BI service. But both provide the same functionality to create, manage & save data visualization. For this tutorial, we will be using Power BI Desktop.

1. Install Power BI Desktop

You can download the Power BI desktop application from Microsoft official Store or visit this link. You will need a corporate or school email address to Sign-Up for Power BI. If you don’t have one, create a free Microsoft Azure Portal account to get an email address with the domain onmicrosoft.com.

2. Know Your workspace

1. Here you can Sign-Up for Power BI using your corporate email address or Azure Portal Account.
2. This will be the name of your project.
3. This is the menu provided by Power BI.
4. ‘Reports‘ tab where you can see your created visualizations and reports.
5. ‘Data‘ tab to create and manage the data you have imported in Power BI.
6. ‘Model‘ tab will help you manage the data models and relationships.
7. This is the area you can use to fill in your visualizations and reports.
8. These are different chart types and visualization you can use to design your reports.
9. This is the ‘Field‘ section which will be helpful to see and manage your all datasets imported in Power BI.

3. Import Sample Data

Note: You can also download the Shop Records 👈

Power BI allows importing data from various sources. Here we will import from an excel sheet that contains tables. These tables represent the relationship with data inserted on rows & column. Power BI automatically recognize the data & its relation in a table on a single import. To import data, visit the ‘Home‘ menu from the top, then click on ‘Get Data’ and select the method you want to use. It will take some time to process and show results on your screen. If you feel that your data is properly loaded, click on the ‘Load’ button. Else, click ‘Transform Data’ to make some changes.

Also Check: Our blog post on Data Analysis in Power Bi. Click here

Learn Data Modeling In Power BI

We will be using simple electronics shop sale records as our data to create Data Models. This data includes the Shop Sales with parameters as customer, country, product, cost, month and year.

1. Data Models In Power BI

Power BI automatically detect all the possible relations between different sets of data. Sometimes we need to create a relation between the data manually.

After the data import is complete, visit the ‘Models’ tab from the left side, as highlighted in the above image. Here you will see some automated relation created by Power BI. All the lines visible here in the ‘Models’ tab depicts the cardinality and direction of the relation from one table to another. You can create & modify this default relationship created by Power BI using the Manage Relationship tool given on the top menu as highlighted in the image.

2. Create And Manage Relationship

After clicking on ‘Manage Relationship,‘ a similar screen will appear, as shown in the above image. You can see all the active relations here from one table to another. All the things you can do with these relations are explained below:

1. New – This option will help you create a new relationship between tables.
2. Autodetect – Using this option, Power BI automatically detects the relationship between data present in tables.
3. Edit – This option will help you to edit your data relationship.
4. Delete – It deletes the selected relationship between the tables.

Now after clicking on ‘New‘ or ‘Edit‘, a similar screen will appear in front of you, as shown above. Here we will explain to you the purpose of all the options one by one.

1. The first drop-down menu will allow you to select your table from which you want to create a relation.
2. The selection in the second drop-down menu will create a relation from the first table to the second.
3. You will select the Cardinality relation here, but you can not force Power BI to select a specific cardinality that doesn’t exist.
4. Here you can select the direction of the relationship as ‘Single’ or from ‘Both’.

3. Calculate And Measure Data

If you are familiar with Excel, you may have worked on the DAX (Data Analysis Expression) formula. If not, then no worries. I will explain in short and simple words. Like programming, DAX is a set of instructions used to calculate data from the tables. These expressions include commands for Addition, Multiplication, Average, Percentage and others with various filters.

For Example :

1. Table 1 = DISTINCT(Table 2[Column_1]), this expression will fill all the unique values in Table 1 from Column_1 present in Table 2.
2. Column1 = RIGHT(Table1[Column_Name],3), this expression will get last 3 characters from Column_Name to fill in Column1.

Let’s create some calculations with our shop data present in Power BI. Visit the ‘Data‘ tab from the left menu as highlighted in the image below. Here you will see some tools to calculate your data. We will be using these on Power BI.

Create Table

After clicking on ‘New Table‘, we need to enter the DAX expression shown in the above image.

1. The first part of the expression defines the name of the table.
2. The second is the filter; the ‘DISTINCT’ function will select only the unique values from the column.
3. We need to pass the parameters inside the ‘DISTINCT’ function, and these parameters are the location from which we will extract our data. So we have passed the table and column name where our country codes are present. When your expression is complete, click ‘Enter’.
4. After applying the expression, we will get our new column with the default name and results. To rename a column, you can double click on it.

Create Column

Click on ‘New Column‘ from the top menu to create a calculated column.

1. The DAX expression will calculate all the revenue from the table ‘Revenue‘ with filter as ‘Country‘. Without this expression, we might have spent hours calculating the individual revenue generated from the country.
2. This is the result we get from the expression.

Although Power BI suggests you write an expression, it might be difficult to remember all the expressions. If this is the case, you can use the Quick Measure tool. You will only need to fill in the parameters and function for calculation. This tool will then generate the expression automatically, depending on your selection. These measurement tools are also helpful when you want quick calculations for your reports.

4. Create Visualization

We only had sales, sales by country and product price in our imported data. Using the above methods, we created a separate table that contains the revenue generated from each country. Here we have shown the revenue from the country on the world map using different visualizations. Similarly, you can also create and manage data models in Power BI.