Regular Expressions for MDX

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

Item

Regular expression

Single-line comment

//[^\n]*

Multiple-lines comment

/\*[^\*]*\*/

MDX Script command

[^;]+;

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

Item

Regular expression

MDX object

\[[^\]]*\](\.(&)?\[[^\]]*\])*

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:

Miscellaneous

Item

Regular expression

MDX set

\{[^\}]*\}

MDX tuple

\([^\)]*\)

String

"[^"]+"

DDL

((alter(\s)+cube)|((create|drop)(\s)+(action|cell(\s)+calculation|member|set)))[^;]+;

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.

Advertisements
This entry was posted in MDX. Bookmark the permalink.

100 Responses to Regular Expressions for MDX

  1. 旺立 says:

    http://www.batteries-shop.nethttp://www.batterieslaptop.nethttp://www.uk-laptopbattery.comhttp://www.wt-batteries.comhttp://www.replicawatchesstore.nethttp://www.cheapteastore.comhttp://www.laptopbatterystore.co.ukhttp://www.batterieslaptop.nethttp://www.batteries-supply.comhttp://www.powertoolsbatteries.co.ukhttp://www.batterygrip.orghttp://www.uk-batteries.co.ukhttp://www.ukbatterystore.co.ukhttp://www.topbatteries.co.ukhttp://www.batteries-shop.net/acer-laptop-battery-c-2.htmlhttp://www.batteries-shop.net/apple-laptop-battery-c-3.htmlhttp://www.batteries-shop.net/asus-laptop-battery-c-4.htmlhttp://www.batteries-shop.net/compaq-laptop-battery-c-5.htmlhttp://www.batteries-shop.net/dell-laptop-battery-c-6.htmlhttp://www.batteries-shop.net/fujitsu-laptop-battery-c-7.htmlhttp://www.batteries-shop.net/gateway-laptop-battery-c-8.htmlhttp://www.batteries-shop.net/hp-laptop-battery-c-9.htmlhttp://www.batteries-shop.net/hp-compaq-laptop-battery-c-10.htmlhttp://www.batteries-shop.net/ibm-laptop-battery-c-11.htmlhttp://www.batteries-shop.net/lenovo-laptop-battery-c-12.htmlhttp://www.batteries-shop.net/lg-laptop-battery-c-13.htmlhttp://www.batteries-shop.net/panasonic-laptop-battery-c-14.htmlhttp://www.batteries-shop.net/samsung-laptop-battery-c-15.htmlhttp://www.batteries-shop.net/sony-laptop-battery-c-16.htmlhttp://www.batteries-shop.net/toshiba-laptop-battery-c-17.htmlhttp://www.uk-laptopbattery.com/acer-laptop-battery-c-2.htmlhttp://www.uk-laptopbattery.com/apple-laptop-battery-c-3.htmlhttp://www.uk-laptopbattery.com/asus-laptop-battery-c-4.htmlhttp://www.uk-laptopbattery.com/compaq-laptop-battery-c-5.htmlhttp://www.uk-laptopbattery.com/dell-laptop-battery-c-6.htmlhttp://www.uk-laptopbattery.com/fujitsu-laptop-battery-c-7.htmlhttp://www.uk-laptopbattery.com/gateway-laptop-battery-c-8.htmlhttp://www.uk-laptopbattery.com/hp-laptop-battery-c-9.htmlhttp://www.uk-laptopbattery.com/hp-compaq-laptop-battery-c-10.htmlhttp://www.uk-laptopbattery.com/ibm-laptop-battery-c-11.htmlhttp://www.uk-laptopbattery.com/lenovo-laptop-battery-c-12.htmlhttp://www.uk-laptopbattery.com/lg-laptop-battery-c-13.htmlhttp://www.uk-laptopbattery.com/panasonic-laptop-battery-c-14.htmlhttp://www.uk-laptopbattery.com/samsung-laptop-battery-c-15.htmlhttp://www.uk-laptopbattery.com/sony-laptop-battery-c-16.htmlhttp://www.uk-laptopbattery.com/toshiba-laptop-battery-c-17.htmlhttp://www.topbatteries.co.uk/acer-laptop-battery-c-1.htmlhttp://www.topbatteries.co.uk/hp-laptop-batteries-c-2.htmlhttp://www.topbatteries.co.uk/sony-laptop-battery-c-3.htmlhttp://www.topbatteries.co.uk/dell-laptop-battery-c-4.htmlhttp://www.topbatteries.co.uk/ibm-laptop-battery-c-5.htmlhttp://www.topbatteries.co.uk/compaq-laptop-battery-c-6.htmlhttp://www.topbatteries.co.uk/toshiba-laptop-battery-c-7.htmlhttp://www.topbatteries.co.uk/fujitsu-laptop-battery-c-8.htmlhttp://www.topbatteries.co.uk/apple-laptop-battery-c-9.htmlhttp://www.topbatteries.co.uk/samsung-laptop-battery-c-10.htmlhttp://www.cheapteastore.com/chinese-black-tea-c-3.htmlhttp://www.cheapteastore.com/chinese-herbal-tea-c-7.htmlhttp://www.cheapteastore.com/chinese-oolong-tea-c-2.htmlhttp://www.cheapteastore.com/chinese-puerh-tea-c-4.htmlhttp://www.cheapteastore.com/chinese-white-tea-c-5.htmlhttp://www.cheapteastore.com/chinese-yellow-tea-c-6.htmlhttp://www.cheapteastore.com/tie-guan-yin-c-8.html

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s