1. Importing Data
The Importing Data phase of a data modeling involves procuring the right tables that can answer the business question at hand. 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/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.
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. 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
The model design consists of creating relationship between the data tables procured for the task at hand.
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. 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/
5. Creating Surrogate Key
Surrogate Key links the isolated Sales Person Table to the existing Data Model. Creating a surrogate key in a table where it does not exist and then linking the Sales Person Table to the Sales table using this key is also a part of data modeling.
6. Merging Queries
In this blog post, we will address the problem of missing column using merging queries.
The Person ID column is still missing in the Sales Table. In order to create a relationship between Sales Person and Sales table, Person ID must be present in Both Tables. Therefore, we will use Merge query to add Person ID column in Sales Table.
To Merge Queries:
1. Click on Home Tab in the Ribbon Menu.
2. Click on Edit Queries.
Query-Editor opens.
3.Click on Sales Query in the Queries Pan.
4. Click on Merge Queries in the Combine Section.
Merge option box opens.
5. Click on Sales Table in the drop-down menu for second table.
6. Click on Sales Person First Name. Press Ctrl on Key Board. Click on Sales Person Last Name from the Sales Table.
7. Click on Sales Person First Name. Press Ctrl on Key Board. Click on Sales Person Last Name from the Sales Person Table.
8. Click on Double Headed arrow at the end of sales table.
9. Click on Person ID Column. Click OK.
10. Remove the Sales Person First Name and Sales Person Last Name in the Sales Table.
11. Click Close and Apply.
12. In the desktop file, click on Model Tab in the Left Ribbon.
13. Click on Person ID in the Sales Person Table and drag to the Person ID in the Sales Table.
A one-to-many relationship is created as a single person sold multiple products in a fact table.
All the tables have been linked and the data model is created.
Conclusion:
A perfect data model consists of the right number of tables with the right relation between the tables. The steps involved in creating a typical data model are importing data, data preparation, creating date table, setting model design. One of the common issue that occurs while creating the data table is the missing surrogate key. In the last blog from the series, the issue of creating surrogate key was discussed while in this blog post, the issue of missing column has been addressed using the merging queries option.
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.
