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.