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:
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:
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:
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.
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…
Hi Chris!
It’s really a stupid thing..
Just a note… your solution work if you have oneToMany relation inside dimension, right?
regards,
Pedro
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, 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!
What do you see in Excel?
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…
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?
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 know, I know. But it’s true – the BIDS browser doesn’t do this.
Thanks Chris!
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
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
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.
Hi Matt, this is a tricky one – I suspect the best answer would be not to use ‘traditional’ SSRS drilldown at all, but instead use an approach like the one I describe here: http://cwebbbi.wordpress.com/2009/02/16/implementing-real-analysis-services-drilldown-in-a-reporting-services-report/
… but I’ll have a think about it and get back to you.
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.
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
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.
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?
You can manually alter the connection string that Excel uses.
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.
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
You need to set it in the connection string you use to connect to SSAS
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
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
That’s not working as it should be – have you set HideMemberIf on the correct level?
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).
Can you try setting it on both levels to see what happens?
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…
No, that’s not the case – something else must be wrong here. What happens when you browse the dimension in the dimension editor?
Like I expect:
– All
– Project1
Project1
Project2
great advice
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?
Hi Martin, does the same thing happen when you check the ‘Show Empty Rows’ box in the PivotTable options?
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.
It made Excel crash? That sounds like a bug… you might need to open a support case.
Great sample. So esry to follow
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
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.
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!
Yes, but what happens when you try querying your cube through an Excel PivotTable?
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
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
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
Dude, what a champ of a post! This saved my life! Thanks for sharing Chris.
Reblogged this on macguires and commented:
Struggling to get ‘Ole Excel to handle Ragged Hierarchies? Look no further than Chris Webb’s blog post.
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.
Hi,
thanks for the great article! I have a question.
I have a customer hierarchy where each customer on different levels has a different property:
Customer A – blue eyes
Customer B (child to customer A) – green eyes
Customer C (child to customer B) – blue eyes
Customer D (Child to customer A) – blue eyes
My problem is when I’m trying to filter customers by eyes color – blue
I’m getting result Customer A, Customer B, Customer D
When trying to filter by – green:
Customer A, Customer B
I need to combine this different levels in one but it seems to be impossible 🙁 I
Any ideas about how to make it happen?
Thanks!
Hi Chris,
I have been trying to implement the same thing that is setting the HideMemberIf property to ‘OnlyChildWithParentName’ but as soon as I process the cube it’s throwing below warnings and unable to process.
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Dimension_Coworker’, Column: ‘Level4ManagerName’, Value: ”. The attribute is ‘Level4 Manager Name’.
Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate. Attribute: Level4 Manager Name of Dimension: Dimension Coworker from Database: Sales Coworker Productivity Data Mart v3, Record: 69.
Please help me out.
Thanks,
Deepu
Take a look at your attribute relationships – it sounds like you have defined an attribute relationship in your dimension that isn’t correct. The dimension health check feature in BI Developer Extensions will help here: https://bideveloperextensions.github.io/features/DimensionHealthCheck/
Hi Chris,
I am unable to process the cube when I set this property. Am I doing anything wrong?
Thanks,
Deepu