SSAS: Parsing Calculated Measures from MDX Script

There was a question recently on the Analysis Services forum on how to enumerate calculated measures from AMO here. Unfortunately you can't do this from AMO. The best you can do is to get the MDX script and then parse it. If you need to do this the following Regular Expression should come in handy.

 

(\bCREATE\s*MEMBER\s*.*\[?measures\]?[\n\r]*\.[\n\r]*\[?(?<CalcMeasure>[^\]]*)\]?\s*as\b

|(?:\bCREATE\s*(\[(?<CalcMeasure>.*)\]|(?<CalcMeasure>[^\s]*))\s*\=))

 

I have wrapped up this regex in a function here

http://projectdistributor.net/Files/73/227/381_Source.zip

http://cid-98546e1b65a78a74.skydrive.live.com/self.aspx/Public/Blog/Parsing%20Calculated%20Measures/ParseCalcMeasures.zip

I have also included the nUnit tests that I used to confirm that it was working how I thought it would. This regex supports both the old and new calculated measure syntax that fellow MVP Chris Webb blogged about here.

 

If anyone is interested, here is what each section of the RegEx means (the color coding is not significant beyond showing the main sections of the expression):

 

(     Start of first Alternative
  \b word break
  CREATE the literal word "CREATE"
  \s* any amount of whitespace
  MEMBER the literal word "MEMBER"
  \s* any amount of whitespace
  .* any characters 
  \[? 0 or 1 "[" character
  Measures the literal word "MEASURES"
  \]? 0 or 1 "]" character
  [\n\r]* 0 or more line breaks
  \. the literal "." character
  [\n\r]* 0 or more line breaks
  \[? 0 or 1 "[" character
  (?<CalcMeasure>[^\]]*) any series of characters (excluding "]")
  \]? 0 or 1 "]" character
  \s* any amount of whitespace
  AS the literal word "AS"
  \b word break
|     Start of Second Alternative
  \b  
  CREATE the literal word "CREATE"
  \s* any amount of whitespace
  (   Start of alternative 2a
    \[ the literal "[" character
    (?<CalcMeasure>.*) any series of characters
    \] the literal "]" character
  |   Start of alternative 2b
    (?<CalcMeasure>[^\s]*) any series of characters (excluding whitespace)
  )    
  \s* any amount of whitespace
  \= the literal "=" character
)      

Update [28 Dec 2008]: New link to example files

Print | posted on Thursday, August 10, 2006 9:10 PM

Comments on this post

# re: SSAS: Parsing Calculated Measures from MDX Script

Requesting Gravatar...
Great job! Thanks a lot!
Left by Konstantin on Sep 07, 2010 4:36 PM

Your comment:

 (will show your gravatar)