How to Build a Power BI Data Model: 2 Easy Steps

Companies use different techniques to organize their data. Data plays a prominent role in business development and decision-making. Data Visualization makes it easier for business users to understand data better. 

Power BI is a Business Intelligence tool that helps companies create reports and visualizations. Before performing Data Visualization techniques on the data and generating insights, it is essential to make Power BI Data Model. With the help of the Power BI Data Model, Data Visualization becomes easier because it helps in organizing data.

Power BI Data Model makes it easier for the users to understand data better. In this article, you will learn about Power BI Data Model and why it is important. You will also go through the steps to build a Power BI Data Model.

What is Power BI?

Power BI is a proprietary Business intelligence tool designed for Data Analytics and Data Visualization and is a part of the Microsoft Power Platform. It offers many in-built connectors and file support to easily import data to create reports. With the help of Power BI users can easily aggregate, analyze, visualize and share data. Power BI is available for Desktop, mobile, and on-premise servers.

Power BI provides many graphs, and charts KPIs to accelerate analysis and better understand data. It even supports reading data from web pages, XML, JSON, and CSV to import data from multiple data sources and create reports.

Key Features of Power BI

Some of the main features of Power BI are listed below:

• Quick Insights: Power BI helps users to easily create subsets of data and automatically perform Data Analysis on the data.
• Customizable Dashboards: Power BI dashboards consist of multiple visualization tiles and allow users to customize the dashboards. 
• Faster Data Sharing: Power BI reports can easily integrate with other apps such as Microsoft Teams and SharePoint Online to share reports quickly.
• Hybrid Development: Power BI integrates with many connectors that allow users to connect to various data sources and consume data easily.

What is a Power BI Data Model?

Power BI Data Model is the process of connecting data from multiple data sources and using some relationships. It is one of the prominent pillars of Power BI. With the help of the Power BI Data Model, other users can understand your data easily. It also helps in building interactive visualizations on multiple data sources.

Power BI Data Models are abstract data that define the data structure, properties, and relation. With the help of Power BI Data Model features, users can build custom calculations on existing tables that can be directly used in visualizations.

Importance of Power BI Data Model

Power BI Data Model allows business users to create a structure for collaboration between your IT team and your business teams. It is essential to create a good Data Model in Power BI to meet specific business requirements and better understand the data. The end goal is to enable the users to navigate data without writing the same queries every time. 

The description of the Power BI Data Model with metadata enhances the collection of tables and relationships allowing a generic client tool to offer a customized experience. 

Steps to Build a Power BI Data Model

Now that you have understood about Power BI Data Model. In this section, you will learn about the steps to build a Power BI Data Model using sample data from Sales Analysis. The following steps to create a Power BI Data Model are listed below:

• Step 1: Creating Model Relationships
• Step 2: Configuring Tables
• Step 3: Calculate And Measure Data
  • Create Table
  • Create Column
• Step 4: Create Visualization

Step 1: Creating Model Relationships

• Open the Power BI Desktop application and click on the “File” ribbon.
  
• Click on the “Open” option and import the data for making Power BI Data Model.
  
• For this tutorial, sample data from Sales Analysis will be used.
  
• Click on the “Model View” option from the left side of the Power BI, as shown in the image below.

• Here, you can view each table and relationship.
  
• In the “Fields” pane right-click on an empty area and click on the “Expand All” option to view all the table fields.
  
• Now, create a visual table by selecting the “Category” field inside the “Product” table, as shown in the image below.

• For adding a column to the table, go to the “Fields” pane and select the “Sales” from the “Sales” table, as shown in the image below.

• There is no relationship between tables, so it will not filter the “Sales” table. 
  
• Navigate to the “Modeling” ribbon, and from the “Relationships” group, select the “Manage Relationships” option, as shown in the image below.

• Click on the “New” button to create a new relationship.
  
• It will open the “Create Relationship” window. 
  
• Select the “Product” table from the first drop-down list, then select the “Sales” table from the second drop-down list, as shown in the image below. 

• The “ProductKey” columns from both the tables are automatically selected as common columns because it has the same data type and name.
  
• Click on the “Ok” button.
  
• Now notice that the table visual has been updated to display values for each product category.
  
• If you open the “Model View”, you will find a connector between the two tables that were not present before, as shown in the image below.

• Another way to create a relationship between tables is by dragging one column from one table to another.
  
• In this Model View, select the “ResellerKey” column from the “Reseller” table and drag it onto the “Resellerkey” column of the “Sales” table, as shown in the image below.

• Similarly, create the following relationships by dragging columns. listed below:
  • “SalesTerritoryKey” column from the “Region” table to “SalesTerritoryKey” column of “Sales” table.
  • “EmployeeKey” column from the “Salesperson” table to the “EmployeeKey” column of the “Sales” table.
    
• Now save the Power BI file.

Step 2: Configuring Tables

• Expand the “Product” table from the “Fields” pane.
• Now, right-click on the “Category” column and select the “Create Hierarchy” option to create a hierarchy.
• Now, from the “Properties” pane (located left to the Fields pane), replace the text with “Products” in the “Name” box.
• To add the second level to the hierarchy, in the “Properties” pane, in the “Hierarchy” drop-down list, select the “Subcategory” option.
• Similarly for adding the third level to the hierarchy, select the “Product” table.
• Click on the “Apply Level Changes” option, as shown in the image below.

• Similarly, configure the “Region” table by creating a hierarchy named “Regions” with the 3 levels of hierarchy as listed below.
  • Group
  • Country
  • Region

• Now, select the “Country” table and open the advanced properties.
  
• Here, in the “Data Category” drop-down option select the “Country/Region” option, as shown in the image below.

• Now, let’s configure the “Reseller” table by creating a hierarchy named “Resellers” with 2 levels, “Business Type” and “Reseller”, as shown in the image below.

• Create another hierarchy named “Geography” with 4 levels of hierarchy listed below:
  • Country-Region
  • State-Province
  • City
  • Reseller

• Coming to the “Sales” table, here select the “Cost” column and type “Based on standard cost” in the description box from the “Properties” pane.
  
• Now, select the “Quantity” column, and from the “Properties” pane go to the “Formatting” section and slide the “Thousands Separator” property to “Yes“, as shown in the image below.

• Next, select the “Unit Price” column and set the “Decimal Places” to 2 under the “Formatting” section.
  
• Then, from the advanced properties, select the “Average” option from the “Summarize By” drop-down list, as shown in the image below.

• Now, press the “Ctrl” key and select the following columns listed below.
  • ProductKey column from the Product table
  • SalesTerritoryKey column from Region table 
  • ResellerKey column from the Reseller table 
  • EmployeeKey column from the Sales 
  • ProductKey column from the Sales table  
  • ResellerKey column from the Sales table 
  • SalesOrderNumber column from the Sales table  
  • SalesTerritoryKey column from the Sales table  
  • EmployeeID from Salesperson table  
  • EmployeeKey column from the Salesperson table  
  • UPN column from Salesperson table  
  • EmployeeKey column from SalespersonRegion table  
  • SalesTerritoryKey columnfrom SalespersonRegion table  
  • EmployeeID column from Targets table  

• From the “Properties” pane, toggle the “Is Hidden” property to “On“.
• Now, switch to the “Report View” and review the designed Power BI Data Model.

That’s it! You have successfully built a Power BI Data Model.

Step 3: Calculate And Measure Data

• Create Table
• Create Column

Let’s make some computations using any Power BI reference data that is available. Visit the ‘Data‘ tab from the left menu as highlighted in the image below. You can find various tools to calculate your data right here. These will be utilized by Power BI.

Create Table

We must insert the DAX expression given in the following image after clicking “New Table.”

• The name of the table is specified in the expression’s first component.
• The second step is the filter; the ‘DISTINCT’ function will only choose the column’s singular values.
• The arguments that we must supply into the “DISTINCT” function are the locations from which we will extract our data. We have now reached the table and column names that include our nation codes. Click “Enter” once your expression is complete.
• We will receive our new column with the default name and results after applying the expression. You can double-click on a column to rename it.

Create Column

To create a calculated column, select “New Column” from the top menu.

• For instance- in the above example shown the DAX expression will total up all of the revenue from the “Revenue” table with the “Country” filter. Without this phrase, we might have spent hours figuring out how much money each person brought in from the nation.
• We obtain this outcome from the expression.

Even though Power BI advises you to write an expression, it could be challenging to remember them all. Use the Quick Measure tool if this is the case. For calculations, you only need to enter the parameters and function. Depending on your choice, this tool will then automatically generate the expression. When you need swift calculations for your reports, these measurement tools come in handy.

Step 4: Create Visualization

Here, for instance, we’ve used various visualizations to display the country’s revenue on a map of the globe. In the same manner, Power BI lets you construct and manage data models.

Benefits of Power BI Data Model

Some advantages of using a Power BI Data Model are listed below:

• Power BI Data Modeling makes it easier for other users to navigate through the data.
• Power BI Data Model helps in connecting and building relationships between different data sources.
• Power BI Data Model optimizes the query and aggregates data in volume.

Conclusion

In this article, you learnt about the Power BI Data Model and why it is one of the essential pillars of Power BI. Also, you went through a series of steps to build a Power BI Data Model. Power BI Data Model helps build a relationship between multiple data sources. Power BI is one of the widely used BI tools allowing users easily create reports, dashboards and perform Data Analysis.

Visit our Website to Explore Hevo

Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. Hevo Data is a No-code Data Pipeline solution that helps to transfer data from 150+ data sources to desired Data Warehouse. It fully automates the process of transforming and transferring data to a destination without writing a single line of code.

Want to take Hevo for a spin? Sign up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about the Power BI Data Model in the comments section below!