Introduction

Nearly two years ago, Darren Gosbell put online a project to help parsing calculated measures in a MDX script. The implementation was based on the use of regular expressions.

Regular expressions can be useful to decompose a MDX query (with elements explicitly defined, i.e. not using MDX functions) and retrieve involved objects (i.e. dimensions, levels, members, etc.). I often used them in performance improvement processes or for MDX pre-validation purpose.

Parsing MDX Script

MDX Script is made of commands. Each command can be multi-lined, but is always terminated by a semicolon.

MDX script can also contain single-line (starting with //) and multiple-lines comments (enclosed by /* and */).

Sample

Script Sample1.vbs prints commands from MDX script:

It starts be removing comments from retrieved script, and then isolates each MDX command.

Limitations

The MDX Script command won't parse correctly if it contains a MDX object or a string with a semicolon.

This is the case for the following command in Adventure Works cube MDX script:

If IsEmpty(([Measures].[Amount],[Scenario].[Scenario].[Budget]))

Then Format_String([Scenario].[Scenario].[Budget Variance %]) = "#;;;Not Budgeted"

End If;

Parsing MDX Command

We will focus here in retrieving MDX objects:

• Cubes
• Dimensions
• Hierarchies
• Levels
• Members
• Measures

Sample

The purpose is to check object existence before running a MDX command. At some stage, we might need to classify MDX object: is it a dimension, a level or a member? To do so, we will query metadata.

Script Sample2.vbs extends Sample1.vbs by parsing each MDX Script command, and guessing MDX objects type.

Limitations

These regular expressions will only work for "well formed" MDX, where objects are enclosed in square brackets.

Resources

If you are interested in regular expressions, here are some useful links:

• Tutorial: http://www.regular-expressions.info/.
• Another tutorial: http://www.codeproject.com/KB/dotnet/regextutorial.aspx.
• Regular expressions with VB Script: http://msdn2.microsoft.com/en-us/library/ms974570.aspx.
• Online tester: http://gskinner.com/RegExr/.
• Online library: http://regexlib.com/Search.aspx.

Miscellaneous

Conclusion

While regular expression will never replace the use of a true parser, they can still be very handy in any situation where MDX command extraction is required.