Geek Answers Handbook

In MDX, how is a hierarchy selected when referencing an All member?

I have a cube with a size containing a parent-child hierarchy and several invisible attributes. The only way this size should be requested is through the parent-child hierarchy.

My problem is how the following MDX name is resolved:

[MyDimension].[All].Children

In this case, one of the invisible attribute is selected (allowed: [MyDimension].[MyInvisibleAttributeUsedOnlyForOrdering].[All].Children ), which leads to unexpected results.

The problem occurs when using the CUBEMEMBER/CUBEVALUE in Excel. Because Excel does not account for invisible attributes, autocomplete moves directly from [MyDimension] to [MyDimension].[All] without asking the user for a hierarchy. When another attribute is visible, it works great because Excel's autocomplete forces the user to select one of the hierarchies, resulting in a two-digit [MyDimension].[MyParentChildHierarchy].[All].[MyCorrectMembers] .

A possible workaround is to set the entire invisible attribute as non-aggressive, so that [MyDimension].[All] only makes sense in the area of ​​my parent-child hierarchy, but I'm looking for a better and more universal solution.

Thanks in advance for any decision or explanation of the behavior of SSAS.

+5
source share
2 answers

When SSAS guesses the hierarchy of attributes for a dimension, my observation is that it takes the first one defined in size.xml.

To see / change which attribute first (i.e. the first child attribute of the Attributes element), you need to import your SSAS database into a Visual Studio project, right-click the size, select "View Code", then scroll down to the attribute element. You can copy / paste the parent-child attribute and move it to the beginning, and all things will start equal to your MDX to allow this attribute hierarchy instead of the (supposedly) key attribute.

I assume that the elements in the hierarchy behave the same in the Hierarchies collection, but have not actually tried to do this.

I don’t know if it will work in the case of Excel, but in general MDX is a method that I have used for many years to get my default PC attribute.

+1
source

Does the StripCalculatedMembers function StripCalculatedMembers in your situation?
I think this can remove more than you want to remove.

The MSDN link is here: https://msdn.microsoft.com/en-us/library/ms145983.aspx

0
source

More articles:


All Articles