Getting Started
To enhance learning, 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 involves 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.
The image below was created using Product Type from the Product table on the rows and Quantity Sold from the Sales Table on the values field for a Matrix Visual.
It can be seen that the visual shows incorrect values because1485 products are sold for every product type and the total quantity of products sold is also 1485. This means that the filter is not able to propagate because no relationship exists between the tables of the data model.
However, after we set the Model Design the tables are connected via relationship as indicated by the lines emerging from one table and ending on the other.
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. However, let’s have a look at the matrix visual again:
The quantity sold for each product type has been updated.
For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/
Creating Surrogate Key
Now you will create a Surrogate Key that can link the isolated Sales Person Table to the existing Data Model.
1. Click on Home Tab in the Ribbon Menu.
2. Click on Edit Queries.
Query-Editor opens.
3. Click on Sales Person Query in the Queries Pan.
4. Click on Add Column Tab in the Ribbon.
5. Click on Index Column.
6. Click on From 1.
7. Rename the newly created column as Person ID.
8. Click Save and Apply.
Person ID is the Surrogate Key for the Sales Person Table.
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 creating Surrogate key using the query editor in Power BI desktop 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.
