Vijay R

Hi,

Stuck again......

I am creating Dimensions, Cubes, MeasureGroups, Measures, Partitions using AMO dynamically. What I need now is to create Calculated Measures using AMO, which would use some aggregation between one or two measures of the cube.

I did not find any object that would enable me to work with Calculated Measures in AMO. It would be really helpful if I can get some code snippets on this.

Thanks and Regards

Vijay R

Matt Carroll

MdxScripts is what you're looking for. Here's a short discussion on the topic which I think will help.

Multiple MdxScripts and Commands in Cubes

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.)

Vijay R

HI,

Thanks a lot, that gave some insight, and helped me investigate further.

This leads to the obvious question, What MDX should I write in the command, so that it would create a calculated measure, with some formula. meaning what would be the general syntax of the MDX that we would have to write.

I created a calculated measure from the BI studio and then used AMO to get the cube object, I then browsed the cube object: Cube > MDXScripts > Commands

Command Text was: string CmdTxt =
CREATE MEMBER CURRENTCUBE.[MEASURES].CalTestMeasure
AS [Measures].[M1] * 5,
FORE_COLOR = 6776628 /*R=52, G=103, B=103*/,
VISIBLE = 1;

Now I suppose this code must do the same programmatically

AMO.MdxScript MdxScript = NewCube.MdxScripts.Add("test", "test");
AMO.Command comd = new Microsoft.AnalysisServices.Command(CmdTxt);

MdxScript.Commands.Add(comd);

I will try this with some more modifications, but this is surely going to create a problem in the future. Now imagine I want to modify the calculated measures, their calculations programmatically ..etc, What happenes is that all the calculated measures are in one huge string of MDX!!!!!!!, How do i work on it string manipulation that makes it very very error prone.

so we have no other way to do this other than using MDX scripts

Matt Carroll

This is the old script versus object model dilema. A script is generally easier for people to read and maintain while an object model is easier to write code against. When you use the UI, all your calculations are placed in a single Command object so that it can be presented as one nice, editable script (there's a toolbar button that allows you to switch between form view and script view for your calculations). If you use a single command containing the entire calculation script as the UI does, then you will need to parse the string to find individual calculations. Regular expressions can help here, but it gets real hairy if you start dealing with invalid calculations in the script. However, if you are creating all your caclulations from code you can choose to put each of your calculations in a seperate Command object within the MdxScript and no parsing will be necessary on your part. Just be sure not to make any changes in the UI or the UI will save them back into a single Command object containing all of the calculations.

Also, don't forget the "Calculate" command. Without this no aggregation of values will happen in your cube and leaf cells and cells you have explicitly assigned values to will have any data.

Vijay R

Hi Matt,

This is exactly what I was looking for,

Quote > "......However, if you are creating all your caclulations from code you can choose to put each of your calculations in a seperate Command object within the MdxScript......"

1. How do we create calculations from code

2. How and where to use the "Calculate" command that you specified

Please provide a sample line of code or a link to the same, It would help me to understand better.

Thanks a lot for all your help.

Regards

Vijay R

Matt Carroll

1. Here's a very simple code sample:

Server srv = new Server();

srv.Connect( "localhost" );

Database db = srv.Databases[0];

Cube cb = db.Cubes[0];

MdxScript script = cb.MdxScripts[0];

// Append calc to existing command

script.Commands[0].Text = script.Commands[0].Text + "\nCREATE MEMBER CurrentCube.Measures.MyCalcMember as 2;";

// Create calc in new command

script.Commands.Add( new Command( "CREATE MEMBER CurrentCube.Measures.MyCalcMember as 2;" ) );

script.Update();

srv.Dispose();

2. The Calculate command is added to the default script automatically be the UI and assumed by the engine if no script is present. This command is normally the very first command in the script and it is what causes the aggregation of leaf cells into non-leaf cells. (See http://msdn2.microsoft.com/en-us/library/ms145565.aspx)

furmangg

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

So you might play around with that. Sure would be nice if that were part of AMO, huh!