Configuring ExecutionLocation
-
Dear Listers,
I am a SQL DBA and do not often come into contact with AS. The following is posted in an endeavour to resolve a recent developer issue. He is seeking assistance to find the means to configure PivotTable Service properties:
I have built cubes (using Analysis Services) that are accessed via a Crystal Analysis client. I'd like to get any generated MDX queries to be executed on the server, instead of the client which currently seems to be taking a lot of the processing load.
I've read documentation that alludes to being able to manipulate some connection properties in PivotTable Service to provide a direct means of controlling location of processing of MDX queries.
One source recommends adjusting the value of the OLE DB property ExecutionLocation, but haven't found any concrete examples on how this should be done. The PivotTable Service itself appears only to have a programmatic interface. Is there a means of specifying this behaviour in any of the tools used.
TIA
John
-
Hi John,
You should be able to specify the ExecutionLocation in the connection string (not having used Crystal to access AS, cant tell you where to find it, but it will have to be there somewhere). A default type connection string will look something like MSOLAP;Data Source=localhost; TO alter this to include the execution location, just add the value pair at the end of the string so Provider=MSOLAP;Data Source=localhost;ExecutionLocation=3;
Notes on this setting:
possible values are
0 - same as 1(V7 support)
1 - PTS selects the exec locn (server or client)
2 - client
3 - server
Have been informed by Msft that pre SP3, this setting was somewhat 'ignored', so ensure you have SP3 on client (PTS SP3) and server (AS SP3).
Other info gained along the way -> ensure that the locale on the client and server are the same, otherwise causes client processing. This can be modified thru another conxn string setting (CompareCaseSensitiveStringFlags=4;)
Should also use DefaultIsolationMode=1 when using the execution location setting.
For a good background on these settings, check out George Spoffords 'MDX Solutions', has a chapter on conxn string settings.
Lastly using a connection string of MSOLAP;CompareCaseSensitiveStringFlags=4;Execution Location=3;Default Isolation Mode=1;
Msft (testing with our cubes) have made particular queries execute completely in the MSMDSRV.exe process (ie the server) where they were previously executing in the client process.
HTH,
Steve
Steve.
-
Hi Steve,
I use openrowset with the above connection string, it works; however, it is not working when I use openquery and specify the string setting on link servers. Do you have any ideas?
Jolene
-
What is your definition of not working? the linked server won't take the conxn string; or that the queries still run on the client?
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply