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.
Table of Contents
What is Power BI?
Image Source
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.
To know more about Power BI, click here.
Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Power BI, Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 100+ data sources and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.
Get Started with Hevo for Free
Its completely automated Data Pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
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
- 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.
Image Source
- 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.
Image Source
- 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.
Image Source
- 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.
Image Source
- 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.
Image Source
- 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.
Image Source
- 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.
Image Source
- 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.
Image Source
- Similarly, configure the “Region” table by creating a hierarchy named “Regions” with the 3 levels of hierarchy as listed below.
Image Source
- 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.
Image Source
- 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.
Image Source
- Create another hierarchy named “Geography” with 4 levels of hierarchy listed below:
- Country-Region
- State-Province
- City
- Reseller
Image Source
- 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.
Image Source
- 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.
Image Source
- 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
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.
Image Source
Create Table
Image Source
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.
Image Source
- 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.
Image Source
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 100+ data sources such as Power BI 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!
Aditya has a keen interest in data science and is passionate about data, software architecture, and writing technical content. He has experience writing around 100 articles on data science.