Comparing members in MDX

By Mosha Pasumansky, November 2004

Often I run across MDX expressions which require to apply different formulas based on whether or not current member is some hierarchy is equal to some particular member. For example, we may need to check whether current time member is the very first month, or whether current account is Flow. While the best way to approach those problems is try to split calculation into several calculations, each one with its own scope, it is not always possible, and the checks for member equivalence are done as part of the MDX expression itself, usually inside condition of IIF function. So let's see what is the best way to perform such comparisons both from correctness and performance point of view. I will use the example of check for current account being Flow. So we need to compare two members: On one hand we have MDX expression for the current account - [Account].CurrentMember, and on another hand we have unique name of the Flow account - [Account].&[Flow]. It is not uncommon to see people trying to write something like following:

[Account].CurrentMember = [Account].&[Flow]

While the above expression looks very natural, it is completely wrong ! The reason is that AS2000 implementation of operator "=" can only compare numbers or strings. Therefore the implicit data type conversion from member object to scalar is applied (I hope to write an article about MDX data types and type conversions soon) by converting [Account].CurrentMember to [Account].CurrentMember.Value and converting [Account].&[Flow] to [Account].&[Flow].Value . So instead of comparing the member objects, this will compare their cell values ! Needless to say, that beyond the performance hit, this is also serious correctness issue, i.e. if there happen to be another account which accidentally has the same value as Flow account, the comparison will say they are equal. So realizing the problems of the above approach, people try to fix it by using property Name of the member, i.e.

[Account].CurrentMember.Name = "Flow"

Here, the name of the current account is compared against the string "Flow". This is better then the previous one, but it is also not entirely correct. This expression makes an assumption, that Account hierarchy won't contain members with the same name. Analysis Services certainly allows different members in the same hierarchy to have the same name, even if they are children of the same parent. In order to fix this problem, one could improve the expression by relying on the unique names of the members through using UniqueName property:

[Account].CurrentMember.UniqueName = "[Account].&[Flow]"

The thinking here is that unique name is called unique because it guarantees uniqueness within the whole cube, and certainly within the hierarchy. I.e. there could be no other member in the cube with unique name "[Account].&[Flow]". Now the expression is correct, but what about performance. UniqueName property needs to build a string which represents unique name of the member. The algorithm for building unique name can be quite complex, because it has to guarantee uniqueness across the cube, and it involves lots of operations, such as quoting names in [ ], string concatenation, conversion of key from their native data type to strings etc. All this happens during runtime, together with final phase of string comparison between the newly built unique name of the member and string constant "[Account].&[Flow]". This is really lots of overhead, and all of it can be avoided by using MDX operator IS which was designed especially for object comparisons. Using IS operator, the condition will become

[Account].CurrentMember IS [Account].&[Flow]

What happens now is that during parsing of this expression, engine will resolve constant member object [Account].&[Flow] to efficient internal representation (ID of that member), and then during query execution runtime, IS operator will simply compare ID of the account member in the current context. Therefore this approach is most efficient and correct way of comparing the members.