RE: PivotTable connected to OLAP performance issue

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bas Kersten [MSFT] (bask_at_online.microsoft.com)
Date: 05/10/04 

Date: Mon, 10 May 2004 11:46:12 GMT

Hi,

I noticed that your Analysis version is 8.00.194 this is the release
version, are you aware that Anaysis server has it's own servicepack? Please
install these from:
sql2kasp3.exe at:
http://www.microsoft.com/downloads/details.aspx?FamilyId=90DCD52C-0488-4E46-
AFBF-ACACE5369FA3&displaylang=en

Als make sure that you have installed ptsfull.exe from the above service
pack on the Excel client, so that both sides are on sp3.

If you want to remote the query to the Analysis server use the Execution
Location setting of 3 and the Default Isolation Mode setting of 1.

The Execution Location and Default Isolation Mode properties are always
used together.

The Execution Location property controls whether a query is executed on the
client or on the Analysis server. By default, the execution of the query is
split between the client and the server in order to distribute the
processing load across multiple computers. Using an Execution Location
setting of 3 in the connection string causes most queries to be executed on
the Analysis server. This setting also reduces the amount of data that is
returned to the client over a slow network connection. In most cases, only
the final result is returned to the client, and the client processor
resources are not used to help resolve the query. The value of the
Execution Location property is set in the connection string when a session
is established. It can be changed during the session. However, every client
using this setting adds to the load on the Analysis server.

The Default Isolation Mode property controls the refreshing of the cache on
the client. Using a Default Isolation Mode setting of 1 causes the cache to
be invalidated whenever a query statement is executed. When this setting is
used in conjunction with the Execution Location setting of 3, you can
ensure that most queries are resolved on the Analysis server rather than at
the client. By default, PTS will use data in the local cache to resolve
queries when possible, which conflicts with the Execution Location setting
if you are attempting to execute as many queries as possible on the
Analysis server. The value of the Default Isolation Mode property is set in
the connection string when a session is established. This value can also be
changed during a session.

To use this property (connection string) in Excel you must edit the
Microsoft Excel OLAP Query File (.OQY). By default, the Microsoft Excel
OLAP Query File is stored in the C:\Documents and
Settings\<User>\Application Data\Microsoft\Queries folder.

Keep in mind that there are a few issues with using the remote query
(execution location = 3) option, most seen are:
*versions need to be the same (both sp3 for example)
*server and client has different locals for example US server with a Dutch
client, if this is true add "local identifier = 1033" in the connection
string, AS will see it now as a US client and remoting works.

See also the AS performance guide for more information on this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx

For your second issue have a look at:

INFO: Using Analysis Server Format Properties with the Office XP WGID:228
ID: 318122.KB.EN-US
http://support.microsoft.com/default.aspx?scid=kb;en-us;318122

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

Relevant Pages

  • Re: PivotTable connected to OLAP performance issue
    ... > pack on the Excel client, so that both sides are on sp3. ... > If you want to remote the query to the Analysis server use the Execution ... > The Execution Location and Default Isolation Mode properties are always ...
    (microsoft.public.sqlserver.olap)
  • Re: PivotTable connected to OLAP performance issue
    ... if I set the security to 'enforce on server' will this have the same ... > pack on the Excel client, so that both sides are on sp3. ... > If you want to remote the query to the Analysis server use the Execution ... > The Execution Location and Default Isolation Mode properties are always ...
    (microsoft.public.sqlserver.olap)
  • Re: Execution Location Problem
    ... Analysis Services 2K query processing will always be split ... The client and the server can both do some ... execution location is by looking at Task Manager on the client and on the ...
    (microsoft.public.sqlserver.olap)
  • Re: Long Connection Time
    ... > 1) add a file share to the Analysis server and create a restricted ACL ... > 2) create a machine account on the Analysis server and a machine account on ... > your client. ...
    (microsoft.public.sqlserver.olap)
  • Problem connecting to remote Analysis Server
    ... I am running SQL Server 2000 Analysis Server on a Windows 2000 Advanced ... and the client on XP Home. ...
    (microsoft.public.sqlserver.olap)