I’m sure you’re all aware of the difference between natural and unnatural user hierarchies in the Analysis Services Multidimensional model (if you’re confused as to what I mean when I say ‘Multidimensional model’, have a quick read of this post from a few weeks ago which explains the terminology). To recap, natural user hierarchies in Multidimensional look like this in BIDS:
There is a one-to-many set of attribute relationships between each level, so each Calendar Year has multiple Calendar Semesters but one Calendar Semester has only one Calendar Year, and they are a Good Thing as far as query performance is concerned. Unnatural user hierarchies look like this:
They work, but there aren’t one-to-many relationships between every level and query performance may be worse than with a natural user hierarchy.
But what does all this have to do with the Tabular model? In SSDT when you create a hierarchy there’s no indication as to whether it’s natural or unnatural:
…and up to recently I assumed that this was an issue that simply wasn’t relevant to Tabular. However, after a recent conversation with Marius Dumitru from the dev team and Greg Galloway I now know this isn’t the case!
If you query the MDSCHEMA_HIERARCHIES DMV as follows:
SELECT
[DIMENSION_UNIQUE_NAME], [HIERARCHY_NAME], [STRUCTURE_TYPE]
FROM $SYSTEM.MDSCHEMA_HIERARCHIES
You can see whether a hierarchy in a Tabular model is natural or unnatural:
In this case you can see that the Calendar hierarchy that I created on the DimDate table is unnatural; SSAS has determined this during processing (specifically during the Process ReCalc stage) by examining the data itself automatically.
It turns out that natural hierarchies in Tabular can result in faster query performance because certain MDX and Formula Engine code paths in the SSAS engine are still not optimised for unnatural hierarchies. I don’t have any specific examples of when this occurs at the moment but if I do find them I’ll be sure to update this post. And if anyone else using Tabular, or even PowerPivot (and I assume this is relevant to PowerPivot too) finds a good example of how changing to a natural user hierarchy improves performance please leave a comment.
In the example above, I created the Calendar hierarchy in the Tabular model by simply dragging the CalendarYear, CalendarSemester, CalendarQuarter, EnglishMonthName and FullDateAlternateKey columns underneath each other in the new hierarchy. It’s unnatural because there are only two distinct values in Calendar Semester (the semester numbers 1 and 2), four distinct values in Calendar Quarter (the quarter numbers 1 to 4) and there are only twelve distinct values in EnglishMonthName (the names of the months), so there is a many-to-many relationship between the values in all these columns. I can make it natural by creating three calculated columns that concatenate CalendarYear and CalendarSemester, CalendarYear and CalendarQuarter, and CalendarYear and EnglishMonthName as follows:
DimDate[Calendar Semester of Year] =DimDate[CalendarYear] & " " & DimDate[CalendarSemester]
DimDate[Calendar Quarter of Year] = DimDate[CalendarYear] & " Q" & DimDate[CalendarQuarter]
DimDate[Calendar Month of Year] = DimDate[CalendarYear] & " " & DimDate[EnglishMonthName]
Using these calculated columns for the Semester, Quarter and Month levels of the hierarchy as follows:
…will make the Calendar hierarchy natural:
Hi Chris, by some considerable coincidence I was going to email you my findings on unnatural hierarchies in tabular today! Certainly your post saves me time having to compile my findings and I was completely unaware that tabular was auto-detecting ‘natural-ness’, which is great news for us.
As for an example, I have just that. I was executing an MDX query against the 7th level of a Chart of Accounts hierarchy. It was the simplest query possible with only members on rows and a single measure on columns. Our model is 100M rows and the query was taking 300ms. I rewrote the same in DAX and was able to get the results back in 30ms.
I then happened upon the new VertiPaq Query events in SQL Server Profiler (check Show all events) and was able to see my MDX query converted to SQL in the form:
Level1 = “” & Level2 = “” & Level3 = “” & … down to whichever level was queried
My DAX on the other hand had explicitly filtered on Level7 only and consequently the VertiPaq Query only used that level.
It occurred to me that the 6 extra column joins in the in-memory columnar database were killing the performance. By changing the query to explicitly use the attribute hierarchy level itself we were able to return the MDX in 30ms, equal performance to DAX. Of course, thank you for your additional information on auto-detecting as it provides a better solution for us.
Hi Christopher,
Thanks for the feedback! So was the hierarchy you were using in the original query unnatural, and was the original query faster after you changed it to be natural?
Yes, apologies for not being clear. The chart of accounts hierarchy had not been auto-detected as natural. I was unaware at the time that auto-detection occurred. I had assumed in the absence of attribute relationships that all hierarchies were unnatural.
For this reason I rewrote the MDX query against the same members in the attribute hierarchy of level 7. For the exact same cell results the query now returned in 30ms, equivalent performance to DAX and 10 times faster than previous. The VertiPaq Query also then showed only a single Where condition on level and previously there had been 7.
Now armed with the information from your post we can adjust our data such that BISM can auto-detect the ‘natural-ness’ of our hierarchies. One of the most interesting things revealed to me from these findings was the massive impact of additional column filters on performance. By joining in 6 additional (levels 1-6) columns the columnar database had to reconstruct rather wide rows, the exact opposite of what columnar was designed for, hence the factor 10 impact. It was also exciting to get immediate results from the new information available to us in SQL Profiler.
Thanks again for the good tip.
Regards
I’m sorry but I think I’m missing something here as when I try to run this I’m told there is not a STRUCTURE_TYPE. All I can see is a STRUCTURE and they are all set to 0. Does this mean that all my hierarchies are unnatural?
Your input would be most appreciated.
Thank Kerry
Are you running this on an SSAS 2012 Tabular model? Which build? This example was on RC0.
I don’t have SSDT. I assume this is what creates the Tabular model. Thank you for getting back to me, I shall go and have a play.
SSDT = SQL Server Data Tools = the new name for what was BIDS, ie it’s the environment for working with SSAS projects in Visual Studio. But it should be pretty clear whether you’re creating a Tabular model or a Multidimensional model.
Oh I see! Well I think I will have to give up on this for the moment as we are not using SSAS 2012 and the cubes are being built in VS2008. Thank you for your time and I look forward to your Intro to MDX course on 21-23 March.
“result in faster query performance because certain MDX and Formula Engine code paths in the SSAS engine are still not optimised for unnatural hierarchies”
Not so sure that this is an optimising issue in that if you know Feb2012 is in 2012 then it’s going to be quicker that Semseter-3-2011-2012 may be 2011 or 2012. that being said if you are saying that Semseter-3-2011-2012 is not as quick as it could be then you could be right with the reason of optimising but it is not going to be able to ever complete with what is a simpler/quicker search.
This particular statement is almost exactly what the dev team told me – it’s not about search, it’s about calculation evaluation, and natural hierarchies are more efficient as far as calculations are concerned.
Hi Chris,
This is an interesting read, I do have a query though regarding parallel hieraches and if Tabular can support these or how to build them, basically I have an extract from Oracle Hyperion in a star schema however the Dimensions support a “single child multiple parents” style structure, the fact will have for example a Product Key which links to the Product dimension however there will be multiple entrys in the Product dimension for the same Product Key but with different Parent Product Key’s all relating of course to one single Parent Product Key, how would I build this in SSAS Tabular?
You can’t build a single hierarchy that does what you want, unfortunately. Instead, you’d need to remodel your data and implement a many-to-many relationship. This post is a good place to start with how to do this: http://gbrueckl.wordpress.com/2012/05/08/resolving-many-to-many-relationships-leveraging-dax-cross-table-filtering/