Dimension hierarchy not showing all data

Question

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

[UserLevel1Id]
[UserLevel2Id]
[UserLevel3Id]
[UserLevel4Id]
[UserLevel5Id]

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?

[Dim User].[UserIdHierarchy].&[#12345] itself is a valid member if you use it in a general mdx query? — michele
When I browse the SSAS cube to see the hierarchy, the member is there. So yes it is a valid member. I cannot understand, or see the problem, while when accessing through the Hierarchy it is not finding the member, but when I access the member through the specific level attribute - data comes up. — Mez
If I understand, if you use this: [Dim User].[UserIdHierarchy].[UserLevel3Id].&[#12345] it will return data, isn't it? [Dim User].[UserIdHierarchy].&[#12345] it's a valid contraction of the uniquename, but it's a contraction so I will give the full unique name a try. — michele
No, if I pass from the Hierarchy, the member will not come up. So not even in this manner : [Dim User].[UserIdHierarchy].[UserLevel3Id].&[#12345]. To get the member, I need to do this which does not make sense : [Dim User].[UserLevel3Id].&[#12345] — Mez

Mez Mez · Accepted Answer · 2013-06-10T19:50:07

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.

Dimension hierarchy not showing all data

1 Answers