none
Using AMO to manipulate cube calculations

    Întrebare

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






    30 martie 2006 00:15

Răspunsuri

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

     

    31 martie 2006 00:28
  • 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.
    18 ianuarie 2007 23:50
  • 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.
    9 mai 2007 17:44

Toate mesajele

  • Have you tried MdxScripts? It's a collection member variable on a Cube object.
    30 martie 2006 21:43
  • 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.
    30 martie 2006 22:03
  • I think Cube.MdxScripts[0].CalculationProperties may be what you are looking for.
    30 martie 2006 22:28
  • 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.
    30 martie 2006 23:30
  • 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.)

     

    31 martie 2006 00:28
  • 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.




    31 martie 2006 02:32
  • 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.

    4 aprilie 2006 18:40
  • 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.

    18 ianuarie 2007 20:32
  • 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.
    18 ianuarie 2007 23:50
  • 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

    19 ianuarie 2007 00:04
  • 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?

    9 mai 2007 17:04
  • 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.
    9 mai 2007 17:44
  • Hi,

    can anybody give me a hint how to add a MDXScript to a cube using AMO?

    I have two measures in the cube, lets say price and amount. I would like to add the following MDXScript, so that the cube contains this calculated measure, named "PricePerItem":
    -----------------------------------------------------------------
    IIF( round([Measures].[price],2) = 0, NULL, IIF(round([Measures].[amount])= 0 , NULL,  [Measures].[price]/ [Measures].[amount])  )
    -----------------------------------------------------------------
    I want to add the script using AMO. Is there a little documentation about AMO objects. I cannot find really good docs, only small examples which are relativley short.


    Thanks a lot and have a nice day!
    Alex
    18 octombrie 2008 11:28
  • ok I got it working partially (the MDX was wrong):


    Code Snippet

    [....generate cube and dims and measures...]

    // write cube to server

    cub.Update(UpdateOptions.ExpandFull);

    StringBuilder strMdxCommandSb = new StringBuilder();
    strMdxCommandSb.Append("CALCULATE; ");
    strMdxCommandSb.Append("CREATE MEMBER CURRENTCUBE.[MEASURES].PricePerItem");
    strMdxCommandSb.Append("AS IIF( round([Measures].[price],2) = 0, NULL, ");
    strMdxCommandSb.Append("IIF(round([Measures].[amount])= 0 , NULL, ");
    strMdxCommandSb.Append("[Measures].[price]/[Measures].[amount])), ");
    strMdxCommandSb.Append("NON_EMPTY_BEHAVIOR = { [price], [amount] }, Solveorder = 10, ");
    strMdxCommandSb.Append("VISIBLE = 1;");

    MdxScript ms = cub.MdxScripts.Add();
    ms.Commands.Add(new Command(strMdxCommandSb.ToString()));
    ms.Update();

    cub.Update(UpdateOptions.ExpandFull);





    The cube is processed without any error now. I also can see the new calculated measure "PriecPerItem" but it seems to be never really calculated, because I can't see the contents in the Scriptview in BIDS nor are there values in the cube browser when adding the PricePerItem measure.

    Do I have to do something else with AMO while creating the cube?

    18 octombrie 2008 12:36
  • Ok, problem solved. I had to install SP2 for SQLServer 2005. :-(
    18 octombrie 2008 14:07