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.