Setting a default member of a hierarchy in the MDX script

Setting a default member is a tempting option which looks like it can be used on any dimension we would like. The truth is far from that. Default members should be used as exceptions and not as a general rule when designing dimensions.

The reason for that is not so obvious. The feature looks self-explanatory, and it is hard to anticipate what could go wrong. If we are not careful enough, our calculations can become unpredictable, especially on complex dimensions with many relationships among attributes.

Default members can be defined in three places. The easy-to-find option is the dimension itself, using the DefaultMember property found on every attribute. The second option is the role, on ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.