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.
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.
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.
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/
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.
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-6-power-bi-training-in-sydney-power-bi-training-in-melbourne/
7. Creating Calculated Columns
Calculated Columns are created to provide you with insights related to your data set and make data visualization easy and meaningful.
In this exercise, you will use RELATED() and RELATEDTABLE() functions and learn how to use Nested Functions.
A new column is created in the sales table.
This column lists the related Product Type from the Product Line Prod Type Table. However, the Product Type for any transaction can be easily found as a relationship exists between these two tables. Therefore, the RELATED() function has not been put to good use.
A new column listing the discount associated with each transaction.
It is a good use of related function. It can be seen that almost no discount was associated with F18 product code. While some of the products were sold at a higher price and others at a lower price.
This function performs a count calculation on the Sales table.
This is also not a good use of calculated columns. The same calculations can be performed on the report using the relationships instead of creating a separate column as it increases the processing load on Model.
This is an example of use of Nested functions for calculations. CONCATINATE() is called inside another CONCATINATE().
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-7-power-bi-training-in-sydney-power-bi-training-in-melbourne/
Flattening the Product Table
In this blog post, we will work with the Product dimensions. There are two tables linked to the main Product table.
Flattening of Product table is important due to two reasons:
- Organization of Data Model.
- Creating Hierarchy in the Data Model.
To Flatten the Product Table:
1. Click on Home > Edit Queries.
2. Click on Product in the Queries Pane.
3. Click on Merge in the Combine Section.
4. Click on Product list Price in the drop-down menu for second table.
5. Click on Product Code in the Product Table.
6.. Click on Product Code in the Product list Price. Click ok.
7. Now click on Double Headed arrow at the end of Product table.
8. Click ok List Price.
9. Right click on Product list Price Table in queries. Click on tick symbol at the right of Enable Load.
Product list Price Table in the Queries Pane Turns Italic.
10. Click on Product in the Queries Pane.
11. Click on Merge in the Combine Section.
12. Click on Product Line Prod Type in the drop-down menu for second table.
13. Click on Product Type in the Product Table.
14. Click on Product Type in the Product Line Prod Type Table. Click OK.
15. Now click on Double Headed arrow at the end of Product table.
16. Click ok Product Line.
17. Right click on Product Line Prod Type Table in queries. Click on the arrow at the right of Enable Load.
This disables the load for Product Line Prod Type Table.
18. Click Close & Apply at the top in Query Editor.
Only four tables are visible in the Fields now.
19. Click on Sales in the Fields.
A warning can be seen with the Calculated Column for Discount Amount because the referenced table no longer exists in the fields.
20. Click on Discount Amount.
21. Type Product at the place of Product list Price in the DAX expression.
The red underlying warning showing incorrect table disappears.
22. Now click on Model Tab in the left Ribbon.
A clear data model containing four tables can be seen.
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 and the issue of missing column has been addressed using the merging queries option. Whereas, in this blog, the flattening of tables is 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.
