Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Ragged Hierarchies, HideMemberIf and MDX Compatibility

with 48 comments

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:

SELECT        ‘UK’ AS Country, ‘Bucks’ AS State, ‘Amersham’ AS City, ‘Chris Webb’ AS Customer
UNION ALL
SELECT        ‘Italy’ AS Country, ‘BI’ AS State, ‘Biella’ AS City, ‘Alberto Ferrari’ AS customer
UNION ALL
SELECT        ‘Vatican’ AS Country, ‘Vatican’ AS State, ‘Vatican’ AS City, ‘The Pope’ AS customer

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:

image

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

image

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:

SELECT        ‘UK’ AS Country, ‘Bucks’ AS State, ‘Amersham’ AS City, ‘Chris Webb’ AS Customer
UNION ALL
SELECT        ‘Italy’ AS Country, ‘BI’ AS State, ‘Biella’ AS City, ‘Alberto Ferrari’ AS customer
UNION ALL
SELECT        ‘Vatican’ AS Country, ‘The Pope’ AS State, ‘The Pope’ AS City, ‘The Pope’ AS customer

…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:

image

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.

Written by Chris Webb

November 11, 2009 at 11:36 pm

Posted in Analysis Services

48 Responses

Subscribe to comments with RSS.

  1. 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!

    François

    November 12, 2009 at 9:39 am

  2. 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…

    Brian

    November 12, 2009 at 10:17 pm

  3. Hi Chris!
    It’s really a stupid thing..
    Just a note… your solution work if you have oneToMany relation inside dimension, right?
    regards,
    Pedro

    PedroCGD

    October 19, 2010 at 11:05 pm

    • What do you mean by a ‘one to many’ relationship? Do you mean attribute relationships? In any case, the solution should work for all dimensions.

      Chris Webb

      October 20, 2010 at 12:43 pm

  4. 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!

    Sam

    November 19, 2010 at 6:41 pm

  5. It’s the same in excel aswell. It works only if all the levels can be hidden as in Vatican->The Pope->The Pope->The Pope. But in my case that’s not possible…

    Sam

    November 22, 2010 at 3:19 pm

  6. I’ve just tried this on my R2 instance, and it’s working fine. Can you check your data and that you’ve set everything up correctly?

    Chris Webb

    November 24, 2010 at 1:19 pm

  7. 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.

    Chris Webb

    November 24, 2010 at 1:21 pm

    • 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.

      pitix

      November 3, 2011 at 2:28 pm

      • I know, I know. But it’s true – the BIDS browser doesn’t do this.

        Chris Webb

        November 4, 2011 at 9:15 am

  8. Thanks Chris!

    Sam

    November 29, 2010 at 4:47 pm

    • Hi Chris,

      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?

      David

      David

      December 22, 2010 at 1:10 pm

      • Hi David,

        It’s not true that HideMemberIf is being deprecated – I’ve seen that before and asked the dev team, and BOL is wrong. So feel free to use it.

        Chris

        Chris Webb

        December 22, 2010 at 5:14 pm

  9. 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.

    Matt

    January 4, 2011 at 7:12 pm

  10. I could probably comment on every single post you make, Chris, but this one was particularly helpful today! Thanks for helping me navigate the wild, wacky world of Microsoft BI.

    Shawn

    April 1, 2011 at 12:48 am

  11. Hi Chris,

    Great article. Still struggling to get a ragged hierarchy to display as expected. Take the example below:

    Level 1->Level 2-> Null/Empty -> Level 3 -> Leaf Level

    As per the notes I can get things working if I repeat the ‘Leaf Level’ up the parent levels and set [HideMemeberIf] to ‘OnlyChildWithParentName’.

    However, if in my case I need to hide the level below 2. Setting the [HideMemeberIf] to ‘NoName’ does not appear to work. Any ideas?

    Can you also please explain where I can set MDX Compatibility in SSAS dimension browser and in the PerformancePoint data source.

    Thanks in advance

    Mo Bhatti

    June 3, 2011 at 9:14 am

    • 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.

      Chris Webb

      June 3, 2011 at 9:21 am

      • I tried the following:

        1) Method #1
        Level 1-> Level 2-> Level 3 -> Level 3 -> Leaf Level

        [HideMemeberIf]=’ParentName’ for all levels.

        2) Method #2

        Level 1-> Level 2-> Empty -> Empty -> Leaf Level

        [HideMemeberIf]=’NoName’ for all levels. Empty refers to empty strings, i.e. ”, .

        I guess what has been confusing me is that you cannot see the true results in the dimension or cube browser.

        What’s the easiest of visually checking if the ragged hierarchies are working as expected?

        Mo Bhatti

        June 3, 2011 at 9:53 am

      • You can manually alter the connection string that Excel uses.

        Chris Webb

        June 3, 2011 at 9:56 am

  12. Thanks for your help. I will give a whirl in Excel.

    It’s a real shame that the cube browser or dimension browser do not show ragged hierarchies appropriately. Can’t imagine why we need to go to Excel.

    Mo Bhatti

    June 3, 2011 at 10:43 am

    • Hi Bhatti,

      i also getting the same result like it is coming repeating level if we put that property.

      chris can u tel me where to set mdxcompatability to 2

      thanks in advance

      Vamshi

      August 3, 2011 at 10:21 am

      • You need to set it in the connection string you use to connect to SSAS

        Chris Webb

        August 6, 2011 at 8:23 pm

  13. Hello Chris,
    Nice post and regarding this topic, I am also drilling somewhat like this as Can we hide the Dimension Hierarchy level ?
    I have open a thread on this too

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/9692a772-c503-4968-b9b5-c8e2344b9b7b?prof=required

    Hope to hear from you.

    Thanks,
    Anil Maharjan

    Anil

    March 29, 2012 at 12:31 pm

  14. Hi Chris,

    I have the following data:
    – Project1 is parent of itself and Project2
    – There are hours related to projects, in this case only 5 hours were spend at Project1, none at Project2.

    Flat data looks like: (like in SQL with project LEFT join hours):
    Parent – Project – Hours
    Project1 – Project1 – 5
    Project1 – Project2 – NULL

    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).

    Is this ‘works as designed’ or is there something I’m missing?

    Thanks in advance!

    Regards,
    Lars Pauwels

    Lars Pauwels

    August 29, 2012 at 9:12 am

    • That’s not working as it should be – have you set HideMemberIf on the correct level?

      Chris Webb

      August 29, 2012 at 10:19 am

      • Thnx for your quick response.

        The hierarchy is: Parent – Project, I’ve set the property on Project (not on Parent because it’s the highest level and I never want to hide that).

        Lars Pauwels

        August 29, 2012 at 10:36 am

      • Can you try setting it on both levels to see what happens?

        Chris Webb

        August 29, 2012 at 10:38 am

      • Makes no difference…

        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…

        Lars Pauwels

        August 29, 2012 at 10:44 am

      • No, that’s not the case – something else must be wrong here. What happens when you browse the dimension in the dimension editor?

        Chris Webb

        August 29, 2012 at 1:02 pm

      • Like I expect:

        – All
        – Project1
        Project1
        Project2

        Lars Pauwels

        August 29, 2012 at 1:06 pm

  15. great advice

    gordon

    August 12, 2013 at 3:31 pm

  16. Hi,

    thx for your great work until now, it already helped me a lot understanding ragged hierarchies and excel.

    but i still can´t get it done properly in a special situation.
    my hierarchy looks like this: 10 levels plus leaf level – only level 1 and 2 are always filled, the rest is optional. my attempt was like: ‘All’ – ‘Level1′ – ‘Level2′ -‘Level2′ – … – ‘Level2′ – ‘LeafElement’.

    i´ve set the excel mdx compatibility = 2.
    HideMemberIf = ParentName.

    it can be displayed properly on all levels in excel when i choose the dimension as background dimension.
    if i put the dimension in rows and i try to drill down, then my leaf level is always hidden.

    any ideas why leaf level is hidden in that case?

    Martin Bierbaumer

    October 7, 2013 at 3:23 pm

    • Hi Martin, does the same thing happen when you check the ‘Show Empty Rows’ box in the PivotTable options?

      Chris Webb

      October 7, 2013 at 4:18 pm

  17. 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.

    Martin Bierbaumer

    October 7, 2013 at 4:36 pm

    • It made Excel crash? That sounds like a bug… you might need to open a support case.

      Chris Webb

      October 7, 2013 at 4:38 pm

  18. Great sample. So esry to follow

    Ken

    November 2, 2013 at 5:50 am

  19. 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!

    Any thoughts about that issue?

    Thanks a lot

    Saad

    March 20, 2014 at 12:54 pm

    • 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.

      Chris Webb

      March 20, 2014 at 1:45 pm

      • Hi Chris, I have tried it in Report Builder and in excel using PowerPivot plugin, but I’m getting the same result as both are using the same tool as in Cube Browser!

        Saad

        March 20, 2014 at 2:35 pm

      • Yes, but what happens when you try querying your cube through an Excel PivotTable?

        Chris Webb

        March 20, 2014 at 2:39 pm

    • Yes, from PivotTable I’m getting the correct results. I’m wondering now how that doesn’t work in SSRS!

      Thanks anyway for your help
      Best

      Saad

      March 20, 2014 at 3:12 pm

  20. Yes, from PivotTable I’m getting the correct results. I’m wondering now how that doesn’t work in SSRS!

    Thanks anyway for your help
    Bes

    Saad

    March 20, 2014 at 3:11 pm

  21. Hi Chris,

    How can I get this to work if I have NULL in the first Level (e.g. Level1 in Level1->Level2->Level3). Also will this work if I have NULLS or do I have to populate it with a value?

    Thanks,
    Vidya

    Vidya

    May 6, 2014 at 6:37 pm

  22. […] BottomUp approach by Chris Webb (Ragged Hierarchies, HideMemberIf and MDX Compatibility): http://cwebbbi.wordpress.com/2009/11/11/ragged-hierarchies-hidememberif-and-mdx-compatibility/ […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers

%d bloggers like this: