Using AMO to manipulate cube calculations

We have a cube with a bunch of calculated measures that were defined using BI Studio. I'm now looking at using AMO to manipulate the calculations (enumerate, modify, add, remove, etc). But what I see in the AMO object model is one MdxScript object with one Command object. That Command object has one very large Text property, with all of the calculated measure definitions munged together in one giant string. Not too easy to manipulate programmatically.

Is there anything that I can do to make this easier to deal with Or is this just the nature of the beast with calculations, and I need to munge around in the giant script Is there some best practice (splitting things into multiple scripts, for example) that we missed I noticed that the AW database looks the same, so I'm guessing this is just the way it is. Sorry if this is a dumb question - outside of defining simple calculated measures, I still don't entirely grok MDX script.

Answer this question

Using AMO to manipulate cube calculations

BW
Could you be more specific about what you have tried and what is not working A calculated member is just an MDX statement withing a Command object within the default MdxScript as described previously in this thread.
Omar Khan
Interesting. Cube.MdxScripts[0].CalculationProperties is an empty collection in my cube, bbut it isn't in the AW cube. How does that collection get populated I don't see any obvious differences in how the calculations are defined in my cube version the AW cube when I look at it in BI Studio.
Corey Wirun

A cube can contain multiple MdxScipts, however only one is active at a given time. Which script is active can be controlled by setting the DefaultScript>false</DefaultScript> XMLA tag. (The default value of <DefaultScript> is true so omitting this tag is equivalent to setting it to true. However, since only one script can be the default, the first script with <DefaultScript> omitted or explicitly set to true wins and all other scripts considered non-default regardless of their setting. This means that the first script encountered without <DefaultScript>false</DefaultScript> is the real default script.) The UI will only work with the active script.

Each MdxScript can contain multiple Command objects, all of which are active (provided the script is active). Each Command object can contain an arbitrary number of calculation statements within its text body. Normally a cube will only contain one MdxScript with one Command which contains all the calculations. If you have multiple commands in the active script, the UI will merge them into one when they are displayed and if you make any changes in the UI, they will be saved as one command. (The value of having multiple commands is that it is possible for some to be completely unparsable and yet the others still work. This is useful in migration since it preserves this capability. However, for new cubes it is recommended that your calculations should at least parse, and the UI will help ensure this, so multiple commands is of little value.)

The CalculationProperties collection contains special XMLA properties of calculations contained in Command objects. Such properties are associated with script elements by a CalculationReference which matches the name used in the creation of a set, calculated member, or calc cell in the script. CalculationProperties are generally just used for Display Folder, Associated Measure Group, and Translation as these three properties cannot be set in the script itself. (In the UI you can see Display Folder and Associated Measure Group calculation properties in the Calculation Properties dialog which can be launched from the Calculations tab of the cube editor.)
DBoldt

Since it's not supported in its current location, I've posted a Connect suggestion that this code be moved into AMO so it will be supported:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=252730
AntonioP
Have you tried MdxScripts It's a collection member variable on a Cube object.
mhammingh
This is not intended for public use and should be protected by a link demand. If you are able to use it be aware that it has not been tested for general use, is not supported, and is subject to change.
Michael Boehm
I think Cube.MdxScripts[0].CalculationProperties may be what you are looking for.
Maorik

Correct, there is currently no publicly available parser for scripts. You could try using regular expressions to build a simple parser to parse commands at a high level.
twistedinferno

Whether or not this is intended to be public, I don't know... but it works...

new Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c)

That returns a Scripts object which gets you everything you need, I believe. I think you need a reference to C:\program files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Design.DLL

I haven't looked to see if that could help you make changes then convert it into a big long script string.
tj.
Greg,
Yes, the MdxScripts collection contains the one MdxScript object that I mentioned in my post. That's where the gigantic string defining all the calculations resides.
Doug Gerlach

Hi,

Using AMO, How to create calculated member in a cube programatically

Note: I tried using Cube.MdxScripts. It did not work for me. Any help
Robert Campbell
Matt,
Thanks for the detailed response. Sounds like I'm pretty much out of luck here. Perhaps this is an area where AMO can be enhanced in the future, to make it easier to programmatically manipulate calculations on a more granular level.
Using AMO to manipulate cube calculations

Answer this question