0845 643 64 63

Deploying MDX calculation scripts with xmla

If you’re a Business Intelligence developer I assume you have BIDS Helper installed. If not then stop reading this post and go and install it. Now. It adds a number of very useful features to the Business Intelligence Development Studio which provide help with many aspects of SSIS, SSRS and SSAS development.

One of my favourite utilities is the Deploy MDX Script function. This takes the calculation script for an SSAS cube (named sets, calculated measures, scope logic, etc.) and deploys it in isolation without having to redeploy and rebuild the entire cube. This is a life saver when trying to write and test complex MDX calculations, and has saved me days if not weeks of waiting around.

The Deploy MDX Script button works perfectly when deploying updated script to the development environment, but what if you want to deploy the same script changes to a testing or live environment? Is there a way of scripting the change without redeploying the entire cube?

Yes there is, by using the following xmla script. Just change the DatabaseID and CubeID elements of the Object element to point to your Analysis Services database and cube, and paste your MDX calculation script in between the <Text> and </Text> tags. Run the script in SQL Server Management Studio and it should update the cube with the new script.

This script works for SQL Server 2008 and SQL Server 2008 R2.

<Alter AllowCreate="true" ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:as="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>YourDatabaseName</DatabaseID> <CubeID>YourCubeName</CubeID> <MdxScriptID>MdxScript</MdxScriptID> </Object> <ObjectDefinition> <MdxScript> <ID>MdxScript</ID> <Name>MdxScript</Name> <Commands xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"> <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; ------------------------------------------------ --Paste your MDX Calculations here ------------------------------------------------ </Text> </Command> </Commands> </MdxScript> </ObjectDefinition> </Alter>

And there you have it, you can update your MDX calculated members outside of BIDS without doing a full deploy.

Frog-Blog Out

4 Responses to Deploying MDX calculation scripts with xmla

  • A very nice trick. I have a problem with translations. When I script a cube in SSMS I get the MdxScript without CalculationProperty-Elements. Running that script results in desired changes in Members and Sets but loosing all Translations. I found the CalculationProperty-Elements in the deployment-Files for the Wizard, only. Adding those to the script is a solution.

  • Hi while using above script I am getting below error :

    Executing the query …
    Errors in the metadata manager. Either the cube with the ID of ‘CEE POS BI’ does not exist in the database with the ID of ‘CEE POS UA’, or the user does not have permissions to access the object.
    Execution complete

    Can you please help me to resolve this?

    • I’m assuming that you’ve already checked the permissions? In which case the next most common cause for this is a difference between the IDs and names of the cube and databasae. Usually the ID and name are the same, but if you rename a cube or database then the name changes but the ID doesn’t.
      The script needs the ID, so double check the IDs are correct and try again.

  • Thats a great script! Thanks. Do deployments like that could cause any impact performance wise? Would you recommend planning for a cube full rebuild after?

Leave a Reply to Alex Cancel reply

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

HTML tags are not allowed.

366,726 Spambots Blocked by Simple Comments

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon