4 Steps to create a Data Model | Power BI training in Australia
Data Modeling is a part of our every Power BI training in Australia. In Power BI Basic course, only the basic concepts related to data modeling are covered whereas Power BI Advanced course is more focused towards data modeling and data transformation. Therefore, understanding what a Data Model is entirely important for both beginners and intermediate level users. This blog is dedicated to understanding the 4 basic steps to set up a data model in Power BI.
Before we see the steps involved, here’s a quick walkthrough for best Practices in Data Modeling in Power BI.
1. Importing Data
The Importing Data phase of a data modeling involves procuring the right tables that can answer the business question at hand. Loading excessive tables which do not provide any information for analysis is not a good practice. It increases the load and eventually the query time increases. On the other hand, if very few tables are loaded, the analysis may be incomplete due to insufficient information.
For a step-by-step guide take an overview on avoiding common issues in data modeling
To Gathered Data for typical Data Model contains:
Product Sheet: It consists of three columns addressing the product details, i.e., Product Code, Product and Product Type. Each Product has a distinct Product Code and falls under only one Product Type.
Product Line Prod Type Sheet: Each Product Type is a subcategory of Product Line. This sheet records the subcategory to category mapping.
Product List Sheet: Product list contains the details of each Product and the list price associated per unit.
Sales Person Per Product Type Sheet.: This sheet contains details of Sales Person associated with the Sales. There exists a many to many relationships between the sales person and the product.
Sales Sheet: Sales sheet contains transactional Data for the Sales.
Now you have five queries loaded in your Query Editor. At this stage your data model should contain five un connected tables as shown in the image below.
2. Data Preparation
Data Preparation involves ensuring that the data is capable of delivering value.
Data Preparation involves:
- Checking the data types and data categories.
- Making sure that all the required columns are present in data set.
- Ensuring all the columns have proper headers.
Power BI guesses the data type of the column based on top 1000 Rows.
For a step-by-step guide take a look on Data Modeling Common Issues and Solutions.
3. Creating Date Table
Date is an important dimension table in any data model. Date Table can be generated in Power BI using either the CALENDER() function or the CALENDERAUTO() function.
Both of these functions generate a single date column table. However, CALENDER(<start date>,<end date>) requires the starting and ending dates as the arguments to the function. Whereas, CALENDERAUTO() automatically detects the first and last date in the dataset and generates a date table accordingly. The date format can then be set according to what is used in the company. Additional date columns can also be created to help the slicing of data at week, month, quarter, and annual levels. Fiscal calendar columns can also be created by simple DAX codes to help in financial reporting.
For a step-by-step guide on this refer to this Power BI training resource.
4. Setting Model Design
Now that we imported the required data tables and created the date table, it’s time to design a model using these data tables. To understand the effect of the absence of a relationship between two tables and the method to create relationships visit this guide on common issues and solutions in Power BI.
Relationships in the data model are important because the link the tables and define the direction of filter propagation in Power BI. Each relationship is defined by some properties. To understand this topic in detail check out the relationship characteristics of a data model.
Conclusion:
Data Modeling consists of pulling the right data into Power BI for smooth analysis. This may seem easy but practically a good data model is the result of good practice and efficient trouble shooting. A typical data model consists of appropriate number of tables which are connected with the relationship. Relationships can be made by drag and drop but they can be tweaked using the GUI. Therefore understanding relationships is as important as bringing in the data tables. In this blog post, four steps to create a data model in Power BI has been discussed.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses: