MS Excel & PivotTable: location of MDX query execution (Server/Client)

Hi guys!

Please, does someone knows how I can tell to PivotTable in MS Excel where it
should to execute an MDX query (on server or on the client)? Thanks a lot
for help in advance!



Vlado

 

You can edit the Connection String of Excel Pivot table to
include 'Execution Location=3;' property.

The different values which you can specify for the
Execution Location property is listed below.

Value - Description
0 - Default. For compatibility with earlier versions, this
means the same as Value 1. The meaning of this default
value is subject to change in future versions.

1 - PivotTable® Service selects the query execution
location (client application or server) that will provide
the best performance.

2 - Queries are executed on the client application.

3 - Queries are executed on the server. (Queries that
contain session-scoped calculated members, user-defined
sets, or user-defined functions are exceptions.)

(Note: Inorder to change the Excel Pivot table connection
string - Open the Excel Pivot Table - Save the file as XML
Spreadsheet - Open the XML file in any text editor -
Search for <Connection> tag and append "Execution
Location" property to the conection string - Save the XML
file - Open the XML file in Excel application and save the
file back as Excel Workbook (.xls))

HTH.
Cheers,
Sanka

 

Hi Cheers, thx for your quick response! It should by what I'm looking for.
But I have to tell it could be a litle bit complicated procedure for our
users. I have no idea how I'll learn it them. I'm afraid it won't be
acceptable for them (and for me too). There's no another way how to do it
via Excel GUI, or to do this change permanently for every next created XLS
document with PT?

Thx for any idea in advance!


Vladimir Turenic


You can edit the Connection String of Excel Pivot table to
include 'Execution Location=3;' property.

The different values which you can specify for the
Execution Location property is listed below.

Value - Description
0 - Default. For compatibility with earlier versions, this
means the same as Value 1. The meaning of this default
value is subject to change in future versions.

1 - PivotTable® Service selects the query execution
location (client application or server) that will provide
the best performance.

2 - Queries are executed on the client application.

3 - Queries are executed on the server. (Queries that
contain session-scoped calculated members, user-defined
sets, or user-defined functions are exceptions.)

(Note: Inorder to change the Excel Pivot table connection
string - Open the Excel Pivot Table - Save the file as XML
Spreadsheet - Open the XML file in any text editor -
Search for <Connection> tag and append "Execution
Location" property to the conection string - Save the XML
file - Open the XML file in Excel application and save the
file back as Excel Workbook (.xls))

HTH.
Cheers,
Sanka

 

Hi Thomas,

Although there is no default GUI option in Excel
Application. I have a XLA provided by Thomas Greuel which
helps in resetting the conection string parameters
dynamically through a GUI.

Let me know to which email ID should I sent the XLA file
as Microsoft NewsGroups doesn't have a facility to attach
any external files in the Newsgroup.

Cheers,
Sanka

Thomas Greuel : Hope you don't mind if I share your XLA
file.

 

Let me know to which email ID should I sent the XLA file
as Microsoft NewsGroups doesn't have a facility to attach
any external files in the Newsgroup.

I wouldn't mind having this xla either, please.
Just out of curiosity, is it not possible to edit connection in .oqy file.
Would that not be the same?

Thanks.

lc

 

Well, nice try but I see no effect with Execution Location=3 (server). For
whatever reason, SQL server CPU is down to 0% and, on client, Excel
(2003/XP) is taking 100% for a really long period of time (over 2 hours).
The same MDX (taken from log file), runs in MDX sample application in under
2 minutes. Does anyone have **any** idea what's this all about?

TIA

lc

 

Hello

Well according to Microsoft in a Webcast on:

http://support.microsoft.com/default.aspx?scid=%
2fservicedesks%2fwebcasts%2fwc120401%2fWCT120401.asp

you also need to have "Default Isolation Mode=1" as a
property in the connection string

Quote:

The next properties to use are the Execution Location=3
and Default Isolation Mode=1. You will want to use these
together to force the execution of calculated members and
functions on the server side instead of the client. That's
if your cube has many calculated members.

End of quote

Best regards

Martin

 

Tried this but still no go. This option probably works only on AS (2000) and
not on OLAP (7). I read the document and v7 isn't mentioned anywhere. Really
too bad.

Either way, thank a lot.

lc

 

Hello,

You can send it to ! That's my business e-mail address. Thx!
Certainly I'll try it!

Cheers,
Vladimir Turenic


Hi Thomas,

Although there is no default GUI option in Excel
Application. I have a XLA provided by Thomas Greuel which
helps in resetting the conection string parameters
dynamically through a GUI.

Let me know to which email ID should I sent the XLA file
as Microsoft NewsGroups doesn't have a facility to attach
any external files in the Newsgroup.

Cheers,
Sanka

Thomas Greuel : Hope you don't mind if I share your XLA
file.

 

I have the same problem, using Excel 2K, AS2KSP3a and specifying
'Execution Location=3;Default Isolation Mode=1' in my connection string.
Using the exact same connection string in OWC2K is superfast.



Gavin

 

The difference between Execution Location and Default Isolation Mode is
following:

1. Execution Location determines where the AXES will be resolved
2. Default Isolation Mode determines the Isolation Level for getting cells.
When coupled with EL=3, it is effectively determines where the cells will be
calculated.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================