In MDX, how is the hierarchy chosen when referencing the All member?

Question

I have a cube with a dimension containing a parent-child hierarchy and several non visible attributes. The only way this dimension is supposed to be queried is via the parent-child hierarchy.

My problem is how the following MDX name is resolved :

[MyDimension].[All].Children

In that case, one of the non visible attribute is chosen (resolved to : [MyDimension].[MyInvisibleAttributeUsedOnlyForOrdering].[All].Children) which leads to unexpected results.

The problem appears when using a CUBEMEMBER/CUBEVALUE function in Excel. As Excel does not take into account the invisible attributes, the autocomplete jumps directly from [MyDimension] to [MyDimension].[All] without asking the user to specify a hierarchy. When another attribute is visible, it works fine because Excel's autocomplete force the user to choose one of the hierarchies, leading to an inambiguous [MyDimension].[MyParentChildHierarchy].[All].[MyCorrectMembers].

One possible workaround is to set all invisible attribute as non-aggregatable, that way [MyDimension].[All] make only sense in the scope of my parent-child hierarchy but i'm looking to a better and more versatile solution.

Thanks in advance for any solution or explanation of SSAS behavior.

Answer 1

When SSAS guesses an attribute hierarchy for dimension, my observation is that it takes the first one as defined in the dimension.xml file.

In order to see/change what attribute is 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 dimension choose View Code, then scroll down to the Attributes element. You can copy/paste the parent-child Attribute and move it to the top and all things begin equal your MDX should resolve against that attribute hierarchy instead of (presumably) the key attribute.

I assume Hierarchy elements behave the same way in the Hierarchies collection, but haven't actually tried that.

Don't know if it will work in the Excel case, but in the general MDX case it is the technique I've been using for years to get my P-C attribute to be the default one.