I have a users dimension, which represents the hierarchy of many shops. For instance, shops are split into Country - > Region -> Town/Village - > Actual Shop. So when you look at it from a hierarchy perspective, it is Level1, Level2, Level3, Level4 etc.
Now the strange thing is, if I execute the MDX query and filtering in the WHERE clause, by the hierarchy, for a specific user no data displays. However, if I execute the same exact MDX not filtering by the hierarchy, but filtering by the attributes, records are shown.
It is important to mention that the user hierarchy [UserIdHierarchy] contains a hierarchy of the following members
These are the 2 cases, which should return exactly the same results...
Where clause filtering with user hierarchy :
where
(
DESCENDANTS([Dim User].[UserIdHierarchy].&[#12345],0, self)
)
Where clause filtering without user hierarchy :
where
(
DESCENDANTS([Dim User].[UserLevel3Id].&[#12345],0, self)
)
Why don't both of the filters, bring up the same data for this particular user?
The reason was that the User hierarchy was using historical approach. Therefore, when a user had more then one hierarchy (as shown below), for some strange reason the MDX got messed up and stood with the first item in the dimension hierarchy. Below, I am showing 3 different setups of a user, after his registration into the system. To fix my problem, instead of just doing
[Dim User].[UserIdHierarchy].&[#12345]
I'm filtering in the where clause all user members, i.e.
{[Dim User].[UserIdHierarchy].[UserLevel1Id].&[#12345],
[Dim User].[UserIdHierarchy].[UserLevel2Id].&[#12345],
[Dim User].[UserIdHierarchy].[UserLevel3Id].&[#12345]}
Then the actual filtering is done on the FACT data. This way I include all data for all members in the user hierarchy which match my user - which in this case was #12345.