Matching on MDX Monday

According to the Essbase Tech Ref, there is no equivalent MDX function for the @MATCH function. However, there is a function called IsMatch() which can be combined with Filter() to give equivalent results. Maybe the documentation hasn't been updated in a while. Anyway, let's sort it all out here. IsMatch() uses regular expressions which are really powerful. You can read the specification that Essbase uses here.

Let's try to reproduce the three examples given in the Tech Ref for @MATCH. It's not mentioned, but these are obviously being taken from Sample.Basic.

@MATCH(Product, "???-10")

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Product.Members,   
4:    IsMatch(Product.CurrentMember.MEMBER_NAME, "^[0-9]{3}-10$")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

OR

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Product.Members,   
4:    IsMatch(Product.CurrentMember.MEMBER_NAME, "^[A-Z0-9]{3}-10$")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

In this case I'm using a few special characters to ask for 3 characters followed by "-10". The first character is the ^ which forces the regular expression to start at the beginning of a word. In the first example I have [0-9] which asks the engine to match any digit. In the second example I have [A-Z0-9] which will match any alpha character or digit. Next comes {3} which tells the engine to match the preceding statement three times. So the [0-9] or the [A-Z0-9] will need to be matched exactly three times. Next I add "-10" which will need to match that text exactly. Finally, I added the $ which matches the end of the line. In this case either example works because we only have products with numbers in the first three characters. The second example would match a product like "abc-10" as well.

@MATCH(Year, "J*")

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Year.Members,   
4:    IsMatch(Year.CurrentMember.MEMBER_ALIAS, "^J")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

This is a fairly straight forward regular expression. I'm using the ^ to specify that

the string needs only to start with a J. One other difference here is that I'm using

the MEMBER_ALIAS property for Year to do the matching.

@MATCH(Product,"C*") 

1:  SELECT   
2:  {} on AXIS(0),   
3:  Filter(Product.Members,    
4:   IsMatch(Product.CurrentMember.MEMBER_ALIAS, "^C")  
5:  )   
6:  ON AXIS(1)   
7:  FROM Sample.Basic;    

This is basically the same request as the last one. Grab all of the Products whose aliases start with C.

Conclusion

With the ability to use regular expressions, the IsMatch() function is far more powerful than @MATCH. If you come up with some challenges for the regular expression engine, feel free to pass them along.