Different ways of referencing objects in MDX

Back to the basics today. Often, I have been asked by colleagues what are the different ways of referencing objects like Dimension, Hierarchies, Levels and Member names in MDX. Most of the times, I have seen people following the way they learnt from their seniors or simply the first way they found to be working without understanding the intricacies. This post is meant to shed some light on the basics.

There are 3 main ways of referencing objects in MDX, namely

  1. By Name :- This is the most easiest way of referencing an object, by just specifying the name. For ex., if you have a member Australia in the level Country, you can just refer to it by specifying Australia.
  2. By Qualified Name :- For a dimension, the qualified name is equal to the name of the dimension in square brackets. Ex. [Time] for time dimension. For a hierarchy, it is the the dimension name followed by the hierarchy name in the following format - [Time].[Calendar Hierarchy]. For the level, it is the qualified name of the hierarchy followed by the level name in the format - [Time].[Calendar Hierarchy].[Year]. For a member, the qualified name is the qualified name of the level or hierarchy followed by the names of all parents of the current member and the name of the current member -  [Time].[Calendar Hierarchy].[2009].[Q2].[May].
  3. By Unique Name :- Analysis Services assigns a unique name to every object and this can be retrieved by using a schema rowset or from the results of another MDX request. Usually, the unique name is generated by using the member key (rather than the name like in the previous 2 methods). For ex., if the key for 2009 was 1412, then it would have been referenced as [Time].[Calendar Hierarchy].&[1412].

Title MDX