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.
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.
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
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?
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
Then run the following statement:
([Measures].[Internet Order Quantity], [Date].[Day Name].&)=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.
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.