Regular Expressions for MDX


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 */).


Regular expression

Single-line comment


Multiple-lines comment


MDX Script command



Script Sample1.vbs prints commands from MDX script:

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


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


Regular expression

MDX object



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.


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


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



Regular expression

MDX set


MDX tuple







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.

This entry was posted in MDX. Bookmark the permalink.

100 Responses to Regular Expressions for MDX

  1. 旺立 says:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s