Altering Calculations for a Deployed (Live) SSAS Cube

18 Jun
June 18, 2013

This is a pretty simple post to show how to alter (add, remove or edit) a calculated field in an SSAS cube without redeploying the whole project, a useful technique if you do not have the SSAS cube project handy or wish to quickly implement changes on a live cube.

In order to edit a live cube’s calculated fields, we will need to run an ALTER command in XMLA (XML for Analysis) format on the Analysis Services server where our project is deployed. The format for the XMLA command looks like this:

<Alter ObjectExpansion="ObjectProperties"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>[CUBE-DB-ID]</DatabaseID>
 <CubeID>[CUBE-ID]</CubeID>
 <MdxScriptID>MdxScript</MdxScriptID>
 </Object>
 <ObjectDefinition>
 <MdxScript xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <ID>MdxScript</ID>
 <Name>MdxScript</Name>
 <Commands>
 <Command>
 <Text>
/*
 The CALCULATE command controls the aggregation of leaf cells in the cube.
 If the CALCULATE command is deleted or modified, the data within the cube is affected.
 You should edit this command only if you manually specify how the cube is aggregated.
 */
 CALCULATE;
...
...
</Text>
</Command>
 </Commands>
 </MdxScript>
 </ObjectDefinition>
</Alter>

In order to run an XMLA command, right click on the cube database and select “New Query” then “XMLA“.

Now lets take each part of the ALTER command and explain the required value:

  • DatabaseID: The cube’s Database ID needs to be added here, this can be found out using SSMS by right clicking on the database node and then clicking Properties, there should be a field called “ID”.
  • CubeID: In order to extract the Cube ID, you will need to expand the Cube Database, right click on the relevant cube, and then click on the Properties option, there should be a field called “ID.
  • Text: Which can be found by navigating the XML above using the path “Alter -> ObjectDefinition -> MdxScript -> Commands -> Command -> Text“, this field should include the definition for all calculated fields in the cube, as whatever in there will replace what you already have in the cube being altered.

The best way to handle the Text field is by grabbing the existing calculated fields from the cube, then altering the command text itself as required, and then upload it again using the ALTER XMLA command above, in order to get the existing cube commands you can generate a CREATE script of the cube in question, this can be done by right clicking on the Cube Database, selecting “Script Database as” then “CREATE To” then “New Query Editor Window“, if you do a find command on the resultant XMLA and search for “MdxScript” you should find all existing commands.

I recommend keeping a backup of the existing script, just in case you need to roll back the calculated fields changes.

And that is it really, once the command is executed, the result should look something like this:


<return xmlns="urn:schemas-microsoft-com:xml-analysis">
 <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
</return>

In addition to a message that confirms the command has been executed successfully.

Tags: , , , , , ,
3 replies
  1. tim says:

    Also, need to replace characters such as &

    Reply
  2. Travis says:

    An alternative to this is you can import the live db to BIDS (File, open SSAS db), go to calculations add/change what you need in terms of calculations, under the calculation tab (truly doesn’t matter where) hit process, it will refresh the configuration on the deployed db, the “process cube” window will appear, close out and browse the cube to make sure the new calculations are available/updated. It truly comes down to “what way you feel more comfortable with”. If you do it through BIDS, I recommend using “script view” over the “form view” when adding/changing calculations—I’ve experienced some serious latency with using “form view”. I’ve done both of the aforementioned methods and both essentially accomplish the same thing. The “Going through BIDS” method I used when a supporting vendor overwrote calculations I had created because he deployed it from an outdated project that he found on the server; the changes were instant. I like the method mentioned in the article, on the go, no BIDS available–its a good option.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>