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
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?
Thanks for the post. I tried to use it to deploy my MDX. The script showed error.
XML parsing failed at line 103, column 138: Illegal name character.
The script is the slicing of one dimension: xxx.&[0]
It doesn’t like “&[”
But the same script worked fine in SSAS MDX.
How can I solve this problem?
You may have to escape special characters when running them in script mode from SSMS; i.e. try replacing & with “& amp;” (but with no space!)