Dimension hierarchy not displaying all data

I have a user dimension, which is a hierarchy of many stores. For example, stores are divided into countries β†’ region β†’ city / village β†’ actual store. Therefore, when you look at it from the point of view of hierarchy, it is Level1, Level2, Level3, Level4, etc.

Now it’s strange if I execute a query and filter MDX in a WHERE clause by hierarchy, data is not displayed for a specific user. However, if I execute the same exact MDX, which is not filtered by hierarchy, but records are displayed when filtering by attributes.

It is important to note that the user hierarchy [UserIdHierarchy] contains the hierarchy of the following members

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

These are two cases that should return exactly the same results ...

In the case of filtering offers with a user hierarchy:

  where 
 (
     DESCENDANTS ([Dim User]. [UserIdHierarchy]. & [# 12345], 0, self) 
 )

Placing an offer without a user hierarchy:

  where 
 (
     DESCENDANTS ([Dim User]. [UserLevel3Id]. & [# 12345], 0, self) 
 )

Why don't both filters display the same data for this particular user?

+4
source share
1 answer

The reason was that the User hierarchy used a historical approach. Therefore, when the user had more than one hierarchy (as shown below), for some strange reason, MDX got confused and stood up with the first element of the dimension hierarchy. Below, after registering in the system, I show 3 different user settings. To fix my problem, instead of just doing

[Dim User]. [UserIdHierarchy]. & [# 12345]

I filter in the where clause all user elements, i.e.

{[Dim User]. [UserIdHierarchy]. [UserLevel1Id]. & [# 12345], [Dim User]. [UserIdHierarchy]. [UserLevel2Id]. & [# 12345], [Dim User]. [UserIdHierarchy]. [UserLevel3Id]. & [# 12345]}

Then the actual filtering is performed according to FACT. Thus, I include all the data for all members in the user hierarchy that correspond to my user, which in this case was # 12345.

0
source

Source: https://habr.com/ru/post/1481372/


All Articles