4月2日
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.
工具 
帮助
