Enquiry Message Chat Now
 

Building Dimension Hierarchies in Microsoft Power BI

Background

Power BI desktop

 

Fig 1   Power BI Desktop with Date Dimension

 

Whenever we build any Business Intelligence solution then at the heart of it is Design of robust data warehouse. Any data warehouse will consist of  cubes or facts and dimensions.  

Within dimension the dimension hierarchies  are  very useful to group the dimension members in the respective order of their hierarchy in the group. This can provide consolidated information about key business performance indicators. 

For example  it’s  a very common business scenario  to view  Sales revenue of a company by Year, Half year, Quarter and by month. Or  the user may want to slice and dice the data by different product categories or by Sales channels such as retail, wholesale or online. 

So we need to create different hierarchies within the dimensions of our interest.

Another typical scenario with Time dimension is to have separate hierarchies for Financial year, Calendar Year or Year to date hierarchies.

So now that I have covered different use cases where the dimension hierarchies will be useful. Let us  take a deep drive into building them using Microsoft Power BI Desktop.

 So we will cover it step by step in this blog.  

 

Creating Time dimension hierarchy:

So let us start to build a Time dimension hierarchy to group the members by Year,  Half year, Quarter and Month.  I have already written a blog on how to build Time dimension  using Microsoft Power BI. If you would like to refer to it then please use the link  “How to build Time dimension using Microsoft Power BI” .

 

Date dimension with members

Fig 2 Date dimension with it’s key members

Launch Power BI desktop. If you don’t have one then you can download it for free using the link here.  I have launched my Power BI project for Sales data analytics. We can see all the members  of Date dimension. I have created a full  video based tutorial on Power BI. If you are interested in watching the complete playlist then please refer to the link “Sales Data Analytics a Case Study using Microsoft Power BI”.

 

Creating hierarchies is easy.  As we want the Year  member of the Date dimension  to be our Top level member we will right click on it  and use the  create hierarchy option.

We will name it as “By Year-Half Year-Quarter Year-Month Year”.

 

You can then right click on the Half Year member and use the option Add to hierarchy → By Year-Half Year-Quarter Year-Month.  Please repeat the same steps  for Quarter Year and Month Year members respectively. The order of members added to hierarchy should be the order in which you would like to group them. The newly created hierarchy is as shown below.

Date Dimension

Fig 3: Time dimension hierarchy

 

Let us see how it looks in a Visual widget.  We will use a slicer widget from the “Report” view.  We can drag and bring the slicer widget  in the blank area of “Report view”

Time dimension hierarchy

     Fig 4: Hierarchy with Year 2019 node expanded

 

We can see the members of Date dimension. I have expanded the 2019 node so you can see all the members below it and how they have grouped together in hierarchical fashion.

 

Building Geography Dimension Hierarchy

Let us now build the Geography dimension hierarchy now. We would like to group them by Territory→ Country→ Region→ City→Postcode 

Again now we will right click on our top level member which is Territory  and select “Create hierarchy option”.  We will then add Country, State, City, Post Code members.  We can name the hierarchy as By Territory-Country-State-City hierarchy. 

Date Dimension

Fig 5: Geography Dimension with all members

 

Let us see how it looks in the Report view. We will drag the Slicer widget to the Report view area. We will then drag the hierarchy we created above and  drop it on the slicer widget. The hierarchy will look as shown below. I have expanded APAC territory to show how different members under it are grouped together.

Hierarchy with expanded node

Fig 6 Geography hierarchy with APAC node expanded.

 

You can see how easy it is to build dimension hierarchies in Microsoft Power BI. I hope you will find this Blog useful. 

 

Share

Leave a Reply