There’s been an interesting discussion on the MSDN Analysis Services Forum recently about the interaction of user hierarchies and attribute hierarchies in the following two threads:
Basically the problem is to do with what happens when you have an attribute hierarchy and a user hierarchy which is based on that attribute hierarchy, and you include the two in the same MDX query – the behaviour when you put one of them in the WHERE clause and one on a visible axis is different from when both are included on a visible axis and regardless of whether what’s happening is by design or not (and I’m not sure, but I suspect it is) it’s certainly the case that users are going to be very confused by what they see. This is one of those issues that you can’t believe you’ve never seen before.
For me this raises the whole question of whether it’s good design to only expose attribute hierarchies to users, or to only expose user hierarchies, or to expose a mixture of both. Up to now I would have said that it didn’t matter what you did but now I’m not so sure – perhaps it is better to only expose a given attribute either in its attribute hierarchy or in a user hierarchy, but never in both. But what happens when you want to expose the same attribute twice in two user hierarchies? Hmm, some more testing/thinking needed on this topic… in the meantime, I’d be interested in hearing everyone’s thoughts on this too…
UPDATE: as per David’s comment below, it looks like this will be fixed in SP2.
I think that when you read an explantation about it you understand the concept that they are trying to convey. However, when it is demonstrated like it is in the forum examples it is clear as to why it can be very confusing to a user. I have discussed tihs with some of the guys over at ProClarity and I think this is what Steve Pontello was calling the "Do Nothing Slicer" because the where clause is getting ignored but it is not obvious to the user. From what I have heard this behavior was by design, I just that they did a horrible job of explaining it.
My opinion is that there should be a configuration on the server some where that would allow you to toggle this behavior on and off. You never know when this type pf flexability will come in handy.
I do not think that many users will slice on the same dimension that they have on an axis. Some will find out about this feature by accident and ask questions about it. Most of the MDX-reports that I have designed slice on other dimensions that are on an axis.
Whats is strange is that I have only read about it in the ProClarity whitepaper and that this feature is not mentioned in other publications, blogs or newsgroups.
Sorry.Correction:" Most of the MDX-reports that I have designed slice on other dimensions that are not on an axis"
Looks like a bug (thank goodness). One of the threads you listed (see below) says it will be fixed in SP2. It seems to me that this is a significant change in behavior. Unless there is some kine of optional configuration setting (as Dan suggested) then I guess we\’ll need to plan for this change as we carefully write our forward-compatible MDX.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=555394&SiteID=1