Power BI

Creating Product Hierarchy in a Table

Power BI training sessions always uncover common issues. The aim of this blog series is to address the common issues occurring in our Power BI training in Sydney, Melbourne, Brisbane, Perth, Adelaide and Canberra. Here’s the link to the blog series: http://staging3.powerbitraining.com.au/category/data-modeling-issues-and-solutions/ In this blog post, a step-by-step guide on creating product hierarchy is presented but let’s summarize the concepts covered so far:

1. Importing Data

Importing data refers to procuring the data tables into Power BI desktop or service. This step involves creating a connection with the data base to access the data. For a step by step guide on this: http://staging3.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. In this phase, the raw data is manipulated to extract useful information from it. For a step by step guide on this: http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/ 3. Creating Date Table Date Table is important for temporal analysis. The date table is created using the CALENDER() function or the CALENDERAUTO() function. Both of these functions have different input parameters and produce different results. For a step by step guide on this: http://staging3.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. The tables are linked on the basis of primary and foreign keys. If these keys are absent then the table cannot be linked to another table.
Power BI training in Sydney

For a step by step guide on this issue: http://staging3.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 is the unique identifier in any table. It is important to create a relationship between the two tables. This key acts as a primary key in one table anf foreign key in the other.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/

6. Merging Queries

Merging queries involves combining two data tables based on a common column. This column should have similar values in the both tables.

For a step by step guide on this:  http://staging3.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.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-7-power-bi-training-in-sydney-power-bi-training-in-melbourne/

8. Flattening the Product Table 

Flattening of the table refers to linking the tables attached to the ends of the schema. In data modeling terminology it means changing the snow flake to star schema because star schema is the most preferred data model by industry experts.

Flattening in Power BI training in Sydney

Flattening of Product table is important due to two reasons. Firstly, it helps to organizes the data model and secondly, hierarchy can be created in the same table of the data model.

For a step by step guide on this:  http://staging3.powerbitraining.com.au/data-model-common-issues-and-solution-8-power-bi-training-in-sydney-power-bi-training-in-melbourne/

  1. Creating Product Hierarchy

Power BI allows you to create Product Hierarchy for columns present in the same table.

To create Product Hierarchy:

1. Click on Model Tab in the left Ribbon.

2. Right Click on Product.

3. Click on Create Hierarchy.

Creating Hierarchy in Power BI training in Sydney

4. In the General Section, Type Product Hierarchy in the Name field.

5. In the Advanced Section, Click on Product Type in the Hierarchy drop-down menu.

6. In the Advanced Section, Click on Product Line in the Hierarchy drop-down menu.

Setting properties in Power BI training in Sydney

7. Click on Apply Level Changes in the Advanced Section.

Power BI training in Sydney

Notice that a Product Hierarchy is created in the Product Table with Product lowest level of granularity and the Product Line as the highest grain.

Conclusion:

A data model consists of the right tables linked to each other with the correct relationship between them. This may seem as simple as loading the data into Power BI and creating the relationship using drag and drop. But in reality, it is not. When the data model is created in the form of star schema, many problems start to emerge such as missing surrogate key and hanging dimensions. Another commonly used feature is the hierarchy. However, hierarchy cannot be created unless all the columns are present in the same table.

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.

 

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *