Getting Started
For this blog, we will disable the auto detection of relationships by Power BI. This is done to create an environment where you can dive deeply into model creation. Disabling relationship detection is done purely to enhance concept building and is not preferred in ideal conditions. 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/Importing Data
The Importing Data phase of a data modeling involved procuring the right tables that can answer the business question at hand. To Gathered Data for 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.
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/
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.
Power BI guesses the data type of the column based on top 1000 Rows.
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.
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/
Setting Model Design
Now that we imported the required data tables and created the date table, it’s time to design a model using this data. But before we design a data model, we will see what does the absence of relationship between these tables looks like.
To understand the effect of absence of a relationship between two tables.
1. Click on the Table visual in the Visualization Pane.
2. Drag the Product Type field from the Products Table to the Values.
3. Drag the Quantity Sold field from the Sales Table to the Values.
It can be seen that the visual shows incorrect values as since 1485 products are sold for each product type and the total quantity of products sold is also 1485.
Now, we set the Model Design:
4.Click on the Model Tab in the left Ribbon.
5. Arrange the Sales Table in the middle and all other tables around it.
Each dimension table is linked to another table using its primary key that contains distinct values.
6. Click on the Product Type in the Product Line Prod Type Table and drag to the Product Type Column in the Product Table.
A one-to-many relationship is created. As each Product type falls under one Product line but has multiple Products as its sub categories.
7. Click on Product Code in the Product list Price Table and drag to the Product Code in the Product Table.
A one-to-one relationship is created. As each Product code has a single price associated with it.
8. Click on Product Code in the Product Table and drag to the Product Code in the Sales Table.
A one-to-many relationship is created as a single product is sold multiple times in a fact table.
9. Click on Order Date in the Sales Table and drag to the Date Table.
A many-to-one relation exists because the date table contains a distinct date entry against multiple orders on the same date in the sales table.
Now that we have linked all the other tables and we do not find a key column to link the Sales Person table. Therefore, no relationship has been created for Sales Person table.
10. Now Click on Report Tab in the left Ribbon.
The quantity sold for each product type has been updated.
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 trouble shooting. In this blog post, the concept of setting a good model design 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.
