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
-
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.
-
Comments :- If you have multiple objects with the same name, for ex., having Australia in 2 dimensions, then the result would be ambiguous. Also, it hits the performance badly as all the dimensions and hierarchies have to be iterated to resolve the member name. Moral of the story – you might not want to use this approach unless you want to get fired by your boss. This is the worst way to reference an object in MDX.
-
-
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].
-
Comments :- This method is faster when compared to the previous method and works well in most cases. The only issue with this is that if the qualified name for the member is created by concatenating all the parent levels, then the qualified name becomes immobile. It will get outdated the moment a child changes it parent and it can happen especially in cases like where a customer changes his city.
-
-
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].
-
Comments :- This is the most correct way of referencing a MDX object. However care should be taken that the unique name should never be generated by the MDX programmer, and should always be retrieved from the server. The reason is that the generation of unique names is a complex task and the providers that support MDX may have different algorithms for generating unique names. Also, the rules might change from version to version and hence, to make sure that the application we build is compatible with the next version, never generate unique names on your own.
-
So what is the moral of the whole story? The first two should or cannot be used and the same holds for the last one in most scenarios when you want to run a query without being able to get the unique name from the server. Basically you are saying that if you want to write an MDX, you are out of luck because you cannot reference a member?
What I was saying is that the solution depends on the scenario. Option 1 should not be used as much as possible. Option 2 can be used as long as you dont have duplicate names or composite key structures which can change. Option 3 should suffice in almost all (if not all) cases. It is possible to reference a member in MDX. If you let me know your scenario, I could advise you better.