AMO: Enumerate Calculated Members?

Hi,

Apologies if I'm missing something obvious here. I'm trying to enumerate the calculated members in a cube using AMO (as part of programmatically building perspectives), however I can't see an easy way to do this.

The collection of CalculationProperty objects on the cube's MdxScript object(s) would seem perfect (and indeed works for Adventure Works), but for my own hand-built cubes this collection is empty; CalculationProperty settings don't seem to be exposed anywhere in the BIDS UI so I assume they can only be set programmatically, which is of no particular use to me in the general case when operating on potentially manually-produced cubes.

Aside from using CalculationProperty the only way I can see to do this within AMO would be to parse the Command text associated with the MdxScript, but this is a heinous thing to have to do (by which I mean I'm surprised if this is required, rather than it being technically difficult). There are various ways I could get the information I need outside AMO, for example through ADOMDClient/ADOMDServer or firing off some MDX behind the scenes, but again I'd rather not unless necessary - surely it must be possible purely within AMO? How is BIDS doing it on the perspectives tab?

Many thanks for any suggestions,
Jon

Hi Jon,

I think I can answer half your question.

The calculationProperty are exposed in BIDS throught the calculation tab. There is a little properties button - 12th button from the left, just to the left of the script view button. This allows you to add/set all the CalculationProperty settings for all the calculated measures in your script.

I don't know how BIDS figures out what the calculated measures are. I can't see anything that might help in AMO.

|||Hi Darren,

Thanks for the helpful reply - it's funny, I've been switching back and forward using the Form View and Script View buttons in BIDS since the pre-beta days, and yet never noticed that Calculation Properties button right next to them :)

This does indeed partially solve my problem since at least I now have a non-hacky way of setting calculation properties for my own manually-built cubes (and hence can make use of CalculationProperty objects in AMO whenever I can mandate that calculation properties must be set in BIDS). However it still seems rather unsatisfactory that I have to require this surplus metadata to be added to the cube when all I want within AMO is to be able to get at calculated member names ... is this really the best I can do (if so then I still may resort to parsing the script command text to extract the names, rather than rely on metadata that cannot be guaranteed to be present)?

Thanks,
Jon|||Unfortunately, you do in fact need to parse through the MdxScript to enumerate calculcations from AMO. I agree that it's unfortunate. Fortunately, it's not too difficult - the script is nicely delimited.|||

You can parse the script with a Regular Expression too. The following seems to work reasonably well (although I am no RegEx expert so it could probably be improved upon)

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

I setup a sample project with some NUnit tests to check it was working properly here http://projectdistributor.net/Files/73/227/381_Source.zip

This expression works with both the "old" and "new" syntax for calculated measures.

I have an explanation of what the regex is doing that I plan to post on my blog, but my provider is having issues at the moment.

Cheers

Darren

|||Hi guys,

Kevin, thanks for confirming what I feared might be the case. Darren, thanks for the regex - I'd decided do go with a regex parse myself but hadn't actually got round to doing it yet, so it looks like you've saved me the effort.

At least the rest of AMO is pretty sensible from what I've seen ... don't get me started on the SSIS API though

Cheers,
Jon|||No worries - it was an interesting puzzle for my lunch break |||Some people go to the pub during their lunch break ... others write regexes

I'm glad you did though!

Jon|||

turns out there is an API for parsing the calc script. It's not "officially supported" but works great. See mention of Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c) at...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324224&SiteID=1

You might also vote for that connect issue mentioned at the end of the thread.