SSAS: Setting the Default Measure Using MDX

I recently used a nifty bit of code to set the default measure for a cube using MDX. 

The most obvious way of setting the default measure for a cube using is relatively easy.  In the cube editor, under the Cube Structure tab, highlight your cube (at the top of the measure group tree). In the properties on the right of the screen (shortcut F4) there is a property called DefaultMeasure.  Click the dropdown and select a measure from the list.

 On closer examination, however, none of the measures in the drop down are calculated measures. So what if you want the default measure to be just that?  The answer is that the default measure can be set using MDX in the Calculations tab of the cube editor.  The code do it is as follows:


ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER = [Measures].MyDefaultMeasure;

That's all there is to it. Put whatever measure you like here, calculated or not. 

Additionally, if you now want to change the default measure to be a different one, that would previously have required changing the DefaultMember property mentioned earlier and then redeploying the entire cube (using the Deployment Wizard, direct from BIDS etc), which can have other consequences.  However, by assigning the default measure in the calculations tab it has the added benefit that making a change only requires deployment of the MDX script and not the rest of the database. Using a tool like BIDS Helper (from codeplex) means this can be done in a single click from the Calculations tab with no impact to the rest of the cube design.

 

SSAS: How to run multiple XMLA scripts in a single batch - Processing

This post is a follow-on from this article:

http://sqlbanana.blogspot.co.uk/2013/08/ssas-how-to-create-multiple-partitions.html
-----------


Further to my last post, I thought it would be helpful to extend multiple script to commands to processing tasks.  As we explained, we can perform multiple commands inside a single Batch, and therefore run them at once in a single session.

In this short example we will see how we can easily process multiple partitions or dimensions in serial, parallel or any combination.

Similar to previous, we open the Batch tag and include everything inside.  The other tag to pay attention to is the Parallel tag.  Anything within a single parallel tag runs, surprisingly, in parallel.  Note that you still need a parallel set of tags for the items not run in parallel, but they will appear within their own individual start and end Parallel tags.

In the script below we are processing DIM1 and DIM2 in parallel and then afterwards DIM3 will be processed.  This can easily be extended to refer to measure groups, partitions etc.

Note that if an item in the batch fails, the entire batch is rolled back, not just the failed item.



<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ErrorConfiguration 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:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
KeyErrorLimit>-1</KeyErrorLimit></
ErrorConfiguration><
Parallel>
<!--
Process DIM1--><
Process 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:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM1</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process><!--
Process DIM2--><
Process 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:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM2</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel><
Parallel><!--
Process DIM3--><
Process 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:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM3</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel></
Batch>