Using different data visualization softwares to show data using Parent hierarchy through SQL Server Analysis Services (SSAS)

In this article I will show how the data visualization is different using the “Usage” type as “Parent” to create hierarchies of a table automatically, through a multidimensional cube created by SQL Server Analysis Services (SSAS), through a multidimensional model.

Parent usage from “ParentEmployeeKey”

The data was taken from the AdventureWorksDW2017 backup file made available by Microsoft itself through the download backup files.

With this DW restored in a SQL Server database, we can connect to SSAS and create the desire dimension, in this case we use the dimension table “DimEmployee”, and create a cube with the fact table “FactResellerSales” and “FactSalesQuota”, just for we can connect to the desired data visualization software. Many developers still use the Multidimensional model because of the ease of creating hierarchies in the tables (in addition to other facilities obviously).

Remembering that I’m not going to show you how to create the multidimensional cube or a dimension, only the visualization.

Therefore, below I will show how this hierarchy is represented.

• Power BI:

Example of viewing hierarchy data in Power BI

Power BI Parent Hierarchy

• Tableau:

Example of viewing hierarchy data in Tableau

Tableau Parent Hierarchy

• Excel:

Although Excel is not a native data visualization software, it also has a connection with SSAS MD, and even shows hierarchy data perfectly.

Example of viewing hierarchy data in Excel

Excel Parent Hierarchy

• QlikView: We were unable to reproduce the same visualization in QlikView, as according to our version which is April version of 2019 S1, version number 12.40.20100.0, therefore it is not possible to connect to SQL Server Analysis Services (SSAS).

As we can see, using standard visualizations from these software, some have deficiencies when it comes to displaying the data in hierarchy. Power BI, for example, displays a blank line for each level that we descend into the Matrix Table, unlike Excel and Tableau that shows the levels exactly as it should be.

I used the software that I have available for use with me and also the market leaders according to the February/2020 Gartner quadrant.

Magic Quadrant for 2020 for Analytics and Business Intelligence Platforms

Conclusion

You can already get an idea of ​​how the Parent hierarchy works in some of these data visualization software. Especially in the case of Power BI which is from the same company (Microsoft), but which in my opinion has defects in showing this data correctly.

Therefore, using the Ideas Power BI website to vote or submit new ideas for Power BI, I created the idea to correct this error:

• Microsoft Idea · Adjust the visualization of Parent hierarchy data when using SSAS MD (powerbi.com)