THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

[Date].[Calendar].[Year].&[2007].&[1]
[Date].[Calendar].[Year].&[2007].&[1].&[1]
[Date].[Calendar].[Year].&[2007].&[1].&[1].&[March]

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

[Date].[Calendar].[Semester].&[H1-2007]

[Date].[Calendar].[Quarter].&[Q1-2007]

[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

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

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