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.

I have one environment that is similar (SQL 2008 R2, Excel 2010). I can't reproduce your issue. Does it only occur when the only visible hierarchy in the PC dim is the PC hierarchy? Mine has one other visible attribute. — Mike Honey
Exactly, only the PC hierarchy is visible. If another attribute is visible, it works fine, because Excel force me to explicitely choose one of visible hierarchies in the CUBEMEMBER functions. — Ndech
That suggests that this is actually an issue with Excel formula autocomplete, not SSAS or MDX. It also suggests you could work around it by adding a visible hierarchy. — Mike Honey

Zoltan Grose Zoltan Grose · Accepted Answer · 2016-06-29T15:54:41

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.

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

2 Answers