Designing More Complex Dimensions - Expert Cube Development with SSAS Multidimensional Models - page 77

We can then set HideMemberIf to OnlyChildWithParentName on all of the levels of
the user hierarchy, and so be able to make the hierarchy appear as follows in your
client tool:
Notice how, for Monaco, the hierarchy only shows three levels, whereas for the UK
and Italy there is a fourth level visible showing the State. The important point to make
here is that this only worked because the value Prince Albert has been copied up from
Customer to City , and the value Monte Carlo has been copied up from City to State . If
we model the data differently, for example, as shown in the following screenshot:
Here, we will find that we may get our ragged hierarchies to appear correctly in
some client tools, but not in the dimension browser in SSDT and not in Excel. This
is because the connection string property MDX Compatibility=2 needs to be set to
make ragged hierarchies work properly in all cases, and you cannot set this property
in Excel—even if you modify the data source file manually.