Various (Non-)Ways for Hiding Trivial Members in Hierarchies

This is a link-collection that Martin has been doing when investigating, how and why the auto-hide-member of trivial members (they show a single child with the same semantics and are mostly artificially introduced to render unbalanced parent-child tress into balanced md-trees) does not work with Excel 2007 and SSAS 2008.

The Microsoft Documentation for the HideMemberIf-Property mentions the MDX Compatibility-Level of connections.

Chris Webb and Mark Russo describe how Excel 2007 overwrites the property value no matter what you specified.

There was a time when you could set the level in the registry back with SSAS 2000.
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/aeca1ce0-e6aa-4fdc-b604-cc0e27b7ab5a/
--> in 2005 + 2008 ist der Registry-Eintrag nicht vorgesehen

Greg Galloway hacks Excel to be content overwriting only a first fake property:
Extended Properties="MDX Compatibility=2";MDX Compatibility=2;

However, in our experiments, this rendered the whole subbranch invisible.

Oh no, we do not sacrifice so much for that functionality.

Debugging Attribute Relationships in the ROLAP Basis

I guess that this is quite obvious and could as well be published in our ROLAP blog, but just for the sake of completeness:

The following SQL-Statement lists all values of a source attribute

src

in your dimension source table/view

dim

which violate an attribute relationship (path) to a destination attribute

dest

.

select src from dim group by src having count(distinct dest)>1

Extending this a bit delivers all the bad rows:

select * from dim where src in (select src from dim group by src having count(distinct dest)>1)

How to translate the [All] member

I was always hesitating on that issue because I did not want to affect a thousand lines of MDX-Code in which [Dimension].[Hierarchy].[All] is a common construct, e.g., to exclude unwanted detail-calculations from a calculation scope.

But per default, there is no translation row in the dimension editor that allows one to override the display name of that ubiquitous and ugly (though, comprehensible) node.

Here comes the (straightforward) step: Simly set the property "AttributeAllMemberName" of the relevant dimensions explicitely to the default value "All" and - voilà - you have the translation rows for your pleasure without having to reinterpret your scripts.

Please be aware that the member must be also (re-)translated for all the hierarchies (these rows will also automatically appear). Similarly, you can treat the "UnknownMemberName" ...