MdxScrpit and MdxScriptCollection

Skip to first unread message


Jan 16, 2006, 6:01:51 AM1/16/06
Using AMO I have created multiple MdxScripts and added them to the
MdxScriptCollection of my cube. I would like now to select one of the
defined scripts and "execute" it to simulate different scenarios.

Is there a way of achieving this ?

Do I need to reprocess and to what extent my cube ?

Any help would be appreciated. I have been struggling with the
MdxScript "Submit" method without success.



Jan 16, 2006, 6:09:11 AM1/16/06
Sorry I was not enough accurate in my preceding post. More precisely I
don't want to change the default script used for all the cube. Each of
my scripts will impact a subset (say one division) of my data.

Chris Webb

Jan 16, 2006, 11:33:04 AM1/16/06
Hi Franck,

In my understanding, only one Script can be active on a cube at any time and
that's the first one in the cube's definition that has its <DefaultScript>
tag either omitted or set to true. So if you want to switch the active Script
then you'll certainly have to change the cube definition, which in turn will
require some form of processing (although I don't know what the minimum
processing required would be).

However I'm not 100% clear on what you want to do. Can you provide more
details? It sounds like having a Scenario dimension might be the way to go -
you could use one Script and use a SCOPE statement to divide up the
calculations by each scenario you want to test.


Blog at:


Jan 16, 2006, 12:23:45 PM1/16/06
HI Chris,

Thank you for your reply. I do read on a regular basis your blog and I
share your comments about SSRS (lack of) support of SSAS MDX dynamic
queries. (varaible number of columns and rows)

Now back to MdxScript and what I'm trying to do. I would like to offer
to the end user (say a Financial Controler) a GUI that let him define
simple models. He could then select to "execute" or make active one of
his scripts and analyze the results and impact of his scrpt (or
simulation). This is the multidimensional spreadsheet paradigm at the
user disposal.

I cannot consider changing the current active script defined by the
people who have designed the cubes data structure.

There must be a solution otherwise, why is there a collection attached
to a Cube if only one script is applied?


Chris Webb

Jan 16, 2006, 1:00:04 PM1/16/06
Hi Franck,

Well, I'm not sure why there are multiple MDX Scripts in a cube - it might
just be the legacy of some other feature which didn't make it into the RTM

However I think you may be able to do what you want without touching the
cube's MDX Script. You can actually execute any assignment you find in an MDX
Script like you would a SELECT statement, and the result is that the
assignment stays around for the lifetime of the session. This is, I think,
how the MDX Debugger works.

Here's an example on Adventure Works. Using MDX Sample app (unfortunately
you can't do this with SQL Management Studio), open a connection to your
server and after the server name add the following:
; cube=adventure works
This simply adds the above to the connection string you're using. Now run
the following query:
SELECT [Measures].[Internet Order Quantity] ON 0,
[Date].[Day Name].MEMBERS ON 1
[Adventure Works]
Then run the following statement:
([Measures].[Internet Order Quantity], [Date].[Day Name].&[1])=0;

Now, if you rerun the original SELECT statement you'll see that the value
for [Sunday] has been changed to 0.

If your models are simple enough, then I guess you could store each of the
statements in the 'script' your users wanted to test in your app, then
execute them in turn on a new session to mimic the effect of a Script on the
server. If there are some calculations that you want to have defined on the
server Script anyway then you *might* run into some solve order problems, but
I think it could all work. Unfortunately this is all so new you'll have to
test it yourself! I'd definitely be interested to hear how you got on if you
did take this approach though, and if you found any issues.


Jan 16, 2006, 1:14:33 PM1/16/06

Thank you for the suggested solution. I'll give it a try and keep you
I'll try also to put an asignment instruction within a SCOPE.


Reply all
Reply to author
0 new messages