Blog

4 Steps to create a Data Model | Power BI training in Sydney | Power BI training in Melbourne

You are currently viewing 4 Steps to create a Data Model | Power BI training in Sydney | Power BI training in Melbourne
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: https://powerbitraining.com.au/best-data-modeling-practices-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 on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/ 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.
Data Model – Common Issues And Solution | Power BI Training In Sydney | Power BI Training In Melbourne

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 on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

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: https://powerbitraining.com.au/data-model-common-issues-and-solution-3-power-bi-training-in-sydney-power-bi-training-in-melbourne/

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 absence of a relationship between two tables and the method to create relationships visit: https://powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/

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: https://powerbitraining.com.au/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.

Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.

More Checkouts for Power BI Training in Australia.

Leave a Reply