THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Natural and Unnatural Hierarchies in #powerpivot and #tabular

Last week I wrote an article about Natural Hierarchies in Power Pivot and Tabular where I show why it is important creating natural hierarchies for performance reasons. I’d like to spend a few words about the reasons why this is so important, so you might read the article in advance before moving forward.

I had to investigate on a performance issue in a Tabular model of a customer. Initially, I wasn’t able to reproduce the issue creating a similar query in a test environment, because I didn’t focused my attention on the presence of the hierarchy, and I was generating similar queries without the hierarchy at all. Once I realized that the problem was present only in MDX and not in DAX, I started analyzing the query created by Excel and I found a number of workarounds teaching users how to avoid dangerous combinations of attributes on the rows of a pivot table. After more investigations and some help from people in MS (thanks Marius!) I realized that the problem was the use of unnatural hierarchies. I was aware of the problem (I always explain to use natural hierarchies whenever possible), but I was not aware of the order of magnitude of the problem in certain conditions, as I described in the article.

So, I think it is interesting to highlight how to detect the problem when you just look at the Profiler. If the data model uses unnatural hierarchies, you will find in MDX code that certain members have names such as:


These names, when you use a natural hierarchy, will be:



[Date].[Calendar].[Month].&[March 2007]

The reason why performance might be slow and the query starts consuming memory is because of the Non Empty operation performed by the engine in order to eliminate tuples that are not included in the result. It is important to know that this does not happen for any query, and is very sensitive to the number of combinations resulting by the Cartesian product of all the attributes included in an axis of the MDX query. For example, if you use the Country name instead of the City in the example that I included in the article, also the unnatural hierarchy works in a reasonable time.

Lesson learned: always use natural hierarchies also in Power Pivot and SSAS Tabular!

Published Tuesday, February 04, 2014 3:12 PM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Prashant N said:

Thanks Marco for sharing this. I am trying to figure out from you example as to why you have YearSemesterNumber, YearQuarterNumber, YearMonthNumber in the model.  These columns does not appear to be used any where. Are these columns required for performance ?

November 25, 2014 10:55 AM

Marco Russo (SQLBI) said:

They are used for the "Sort Column By" of the YearSemester, YearQuarter, and YearMonth columns, used in hierarchies.

November 25, 2014 12:00 PM

Prashant N said:

Thanks Marco. I am seeing 20% gain after making hierarchies natural / unique within each level. Does ragged or un even hierarchies also impact ssas tabular performance ? Will filling out blank members with parent names improve performance ? Thanks again.

December 1, 2014 5:55 AM

Marco Russo (SQLBI) said:

No, there should be no performance differences for other syntaxes, just usability differences.

December 1, 2014 10:29 AM

Prashant N said:

Hi Marco, I am facing this peculiar issue with Hierarchies in Tabular. When users add hierarchy to Pivot Table filter and filter by selecting values from multiple levels in hierarchy, the performance goes down by 6x. Is there a workaround or patch for this issue ? My Hierarchies are natural.


January 7, 2015 9:36 AM

Marco Russo (SQLBI) said:

If the hierarchy is natural you shouldn't be affected by this issue, but you have to check that you have a natural hierarchy (it's automatically detected based on data and just one item that breaks the rule will create problems - even just a blank one!)

January 7, 2015 9:38 AM

Prashant N said:

Then my understanding of Natural Hierarchy was incorrect .... Is there a way to tell from querying internal views if Tabular evaluates a hierarchy to be natural ? Thanks you very much!

January 7, 2015 12:17 PM

Marco Russo (SQLBI) said:

Run this query:


the last column (STRUCTURE_TYPE) shows Natural or Unnatural depending on the hierarchy type found by Tabular.

Use DAX Studio ( if you have some doubt about how to run the Query (just click in the DMV pane and drag'n'drop the MDSCHEMA_HIERARHIES table in the query window)

January 7, 2015 1:48 PM

Prashant N said:

Hi Marco, I reworked the hierarchies and verified from $SYSTEM.MDSCHEMA_HIERARCHIES that the hierarchy was Natural :

Following are my results for filter on multiple levels with in hierarchy :

Unnatural Hierarchy : 70 Sec average

Natural Hierarchy : 55 Sec average

Filter on only one level: 15 seconds (natural or unnatural)

Could this be due to tabular limitation when filter is against multiple columns with in one expression... like there is a best practice to have separate filter arguments specific to each fields ?

Also as number of items filtered increases (even with in the same level) so does the query time ... is this normal ?

Thanks, I got 20+ % improvement following your advise ...


January 8, 2015 6:55 PM

Marco Russo (SQLBI) said:

I suspect you have some slow measure that is slowing down the nonempty calculation. Do you have same performance using a simple SUM measure?

January 8, 2015 7:17 PM

Prashant N said:

Yes, with simple measure performance is fast, but the use case is a finance report with 300 rows and 20+ measures of YTD, YOY, YOY% (Vs Plan and YTD LY) type.  Some how the slowness is getting amplified when using filter on multiple levels within hierarchy.  Main Fact table is 41 mil rows.

Working on reducing data set from 5 years to 3 and returning null for 0 values ....


January 8, 2015 11:55 PM

Marco Russo (SQLBI) said:

I think you see the result of complex MDX statements generated by the pivot table combined with a non-optimal DAX query plan.

It could be a challenge to optimize and you should consider many factors. It would be useful to know whether an OLAP cube was faster with the same data model (it could be for several reasons, in particular for the different cache you have on OLAP cubes)

January 10, 2015 5:43 AM

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement