SSAS: Parsing Calculated Measures from MDX Script

<< SSAS: Using XMLA to get a list of Databases and Cubes. | Home | Announcing the Analysis Services Stored Procedure Project >>

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
)