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

Tags: , ,

« Previous Post
Next Post »