AS 2005 - RTFC - bulk change calculated members

I was playing around with MDX and calculated measures in AS 2005. One of the nice things in AS 2005 is the script view. The Script view gives you all the calculations in a "text editor". This is really great if you need to do bulk changes to your calculated members. E.g. change the format for all uour calculated measures.

Have you tried that in AS 2000? Well I did, it takes some DSO programming Been there, done that during the last weeks.

So I was playing around in the script view, copy/pasting, global replacing ...and all of a sudden my cube was empty... How come? Well here's what I did wrong.

Open the sample solution that ships with SQL Server 2005 Samples (C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson4). Deploy the cube and play arround in the cube Browser. You should see some data.

Now, go to the Calculations tab, and choose the Script view. You will see something like this:

/*
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;

If you create calculated measures (or other calculations) they will come in here. If you create a new dummy calculated measure the script view might look as follows:

/*
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;

CREATE MEMBER CURRENTCUBE.[MEASURES].[dummy]
AS [Measures].[Reseller Sales-Discount Amount]*2,
VISIBLE = 1 ;

After you deployed the cube you will see the dummy calculated measure in the cube browser.


Now let's go back to the Calculations tab and just delete the line "CALCUATE;"


Redeploy, go back to the browser and what will you see: NADA!!


So well, what have we learned today:

1. This Script View thing is great!
2. But be carefull!
3. RTFC (Read The F* Comments): If the CALCULATE command is deleted or modified, the data within the cube is F*.