Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Ragged Hierarchies, HideMemberIf and MDX Compatibility

Here’s something that I suspect a few people out there will be surprised I didn’t know – but as far as I’m concerned, if I didn’t know it then it’s worth blogging about.

Anyway, it regards ragged hierarchies, the HideMemberIf property and the MDX Compatibility connection string property. Now you probably know that if you want to turn a user hierarchy into a ragged hierarchy (perhaps to avoid using a parent child hierarchy) you need to use the HideMemberIf property on the user hierarchy. For example, imagine you were using the following SQL query as the basis of your customer dimension:

We could build a dimension off this with attributes for Country, State, City and Customer, and for two out of our three customers that would be fine. However the Pope lives in the Vatican, which is (at least for the purposes of this exercise) a Country with no concept of City or State; and in the case of customers who live in the Vatican, we just want to be able to drill down on the Country ‘Vatican’ and see all of the Customers who live there without drilling down through a meaningless State and a City.

So what we can do is build a user hierarchy on our dimension with levels Country, State, City and Customer, and on the lower three levels set the HideMemberIf property to OnlyChildWithParentName:

Then, with any sensible client tool, we can connect to the cube and browse the dimension as we want:

I saw ‘sensible’ client tool, because of course this only works if you set:MDX Compatibility=2…in the connection string. And of course Excel 2007 hard-codes MDX Compatibility=1 in the connection string and doesn’t allow you to change it, so you can’t use ragged hierarchies properly.

This much I knew.

However, what I didn’t realise until last week when I was moaning about this to TK Anand from the SSAS dev team at PASS, is that for some ragged hierarchies you don’t need to set the MDX Compatibility connection string property at all.

For example, if in our case we duplicate the Customer upwards rather than the Country downwards, like so:

…and then build the dimension, setting HideMemberIf on our user hierarchy to OnlyChildWithParentName, we can get the result we want without setting the MDX Compatibility property. Here’s a screenshot of this new dimension in Excel just to prove it:

The difference here is that we’re hiding all members below the State level right down to the bottom of the hierarchy, rather than hiding members somewhere in the middle of the hierarchy. Truly, this is one of those “Doh, if only I’d known!” moments… this at least means that in some of the scenarios where you’d use ragged hierarchies you can get them to work with Excel, even if it means that we have to hack the data (‘The Pope’ is a Customer, not a State or a City). On the other hand there are plenty of scenarios where you do need to hide members in the middle of a hierarchy, and frankly I don’t see why Excel 2007 can’t set MDX Compatibility=2 in its connection string so they work properly.

Share this:

Like this:

Related

Post navigation

52 responses

Good article, happy to read it: I was discussing this fact last week with one of my colleagues, an d I thought I was becoming crazy or something. I think we have no alternative except always putting the "repeating" data on the bottom of the hierarchy. I\’ve tested on several SSAS versions and the problem seems to be quite old. Nevertheless thanks for this!

Hi, Chris. By coincidence I was researching this exact issue yesterday and came upon this post: http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/2a36d561-fdea-4b78-8d5f-3d383ed6d6a1I tested furmangg\’s "mystical" suggestion (adding Extended Properties="MDX Compatibility=2";MDX Compatibility=2; to the Excel connection string) and it worked…kind of…Members were hidden but all members below the hidden members (even if they did not meet the HideMemberIf condition) were also hidden. Using your first example, you\’d get Vatican with no children at all. Not what I expected…

Chris, Thanks for the article. I followed your example above on 2008R2 and while browsing the hierarchy in the dimension browser, the state and City are not being hidden. What i see is
Vatican->Vatican->Vatican->The Pope. Any advice please!

Ah, hold on, I’ve just read your comments properly – yes, the whole problem is that if you do Vatican-Vatican-Vatican-The Pope, then you can’t get this to work in Excel or in the browser. This approach will only work if you can set the MDX Compatilibility property, which isn’t possible in Excel. You’ll have to remodel your data if you do want it to work.

Hi Chris,
Do you mean BIDS browser is not ‘sensible’?
I can accept Excel not be able to display this property, but BIDS browser…???
How can it be possible BIDS browser can’t display its own properties?
Thanks in advance.

I’m looking at using this approach but SSAS R2 Books Online say that HideMemberIf is being deprecated in the next version. As this is the only real way to get pre-aggregated data into a cube without using P/C do you have any idea what it is going to be replaced with?

Hi Chris,
I am trying to do something similar with an organisation structure dimension. I have eventually written a stored proc. to manually remove the spaces/repeats at each level. Now I have my ragged hierarchy how do I get it to display correctly in SSRS? I can see it perfectly in Excel pivot table but SSRS is a different matter. Only branches that extend to the last level are displayed. Even a Parent/child hierarchy does not display properly in SSRS. If the answer is too long perhaps you know of a good resource? Thanks for a good article! Matt.

The point is you can’t use a null value – you need to copy the value from Level 3 up if you want to avoid using the MDX Compatibility property (you should also avoid using null values because it will stop you building effective attribute relationships). You can’t set MDX compatibility in the dimension browser; I’ve not tried, but you should be able to add it to the connection string in the connection object in PerformancePoint.

If I put this in a cube and set the HideMemberIf property to OnlyChildWithParentName and look at it in Excel, I get the following:
Project1 – Project1 – 5

Project2 isn’t shown because it has no hours. But since Project1 is the only one shown, I want level2 to be hidden; because it -optically- validates to OnlyChildWithParentName (though in the dimension it’s not true).

By the looks of it, it seems he builds the hierarchy first including the HideMemberIf property AND THEN eliminates the rows that have no measures associated, but fails to look back if he can ‘redo’ the HideMemberIfProperty…

Hi Chris, i just tried it several times. It made excel crash everytime i activated “show empty rows”. The strange thing is, that it excel obviously can read the whole dimension with correct hidden members, but only when used in background.

Hi Chris,
I just followed your example and I can get them hidden in Dimension Browser. But as soon as I put this Hierarchy with a measure in Cube Browser, I get no data (or I get only data for measures that reference a dimention element that have a valid values on all dimension levels.) Measures that reference a dimension element with hidden levels won’t be aggrigated/displayed!

Have you tried browsing your cube in Excel? I don’t use the cube browser built into Visual Studio any more – it’s so bad and there are so many problems with it, it’s not worth bothering with. This could be an issue that only the cube browser has.

What tool are you using to view this hierarchy in the bit below ‘Then, with any sensible client tool, we can connect to the cube and browse the dimension as we want:’?

It looks like SSMS but I cannot get this to work like this, I have numerous levels of Vatican in VS2013 and SSMS 2014. I have tried setting Compatibility level in Excel 2013 but it won’t save it and keeps reverting to 1. I am keen to sort this out as something I am working on has space in the mid levels in certain areas which I could use this for.

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk