I'm having two issues with SQL Server / Analysis Services that I hope someone can help me out with. The first is far and away the more serious. The second is an annoyance but still..

The configurations

Server
OS Name Microsoft(R) Windows(R) Server 2003, Standard Editio
Version 5.2.3790 Build 379

Analysis Services
Microsoft SQL Server Analysis Service
Microsoft Corporatio
Version: 8.00.19

SQL Server
SQL Server 2000 with SP

Issue 1: I've created an OLAP cube from an underlying SQL Server table that contains about 2.1 million rows. There are 10 dimensions and 6 measures. There are also about 8 calculated fields. In order to maximize the performance and because I have a surfeit of disk I'm storing 100% of aggregations. When I connect to the cube using a PivotTable in Excel and when I select 2 or 3 of the dimensions as row elements the CPU on the client machine goes crazy: pegged at 100% use for about a minute. Why? The CPU and disk on the server (4 Xeons!) barely blip. I can create the result using a SQL query in 8 seconds but the OLAP takes 1 minute+ and is doing heavy calculation on the client side. I'm confused

Issue 2: The formats that I set for the field in Analysis Services are not propogated to the PivotTable in Excel. How can I fix this

I would be very, very appreciative of any help. Thanks in advance

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/d...52C-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/pro.../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...b;en-us;318122

HTH,
Bas

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

Sorry to jump in, but this is a very useful answer.

I am using SP3 of Analysis sevices and Excel 2003, do I still need to SP3
for PTS?

Also, if I set the security to 'enforce on server' will this have the same
effect, i.e. making queries run on the server only.

I went to edit the .OQY file but the settings you mentioned were not
present, do they have to be added?

Many thanks.
""Bas Kersten [MSFT]"" <bask (AT) online (DOT) microsoft.com> wrote

Bas, Thank you for your response. The answer you gave to the 2nd question
had to do with HTML access to a cube. Is there a way to get Excel (via PTS)
to use the cube formatting?

I tried entering the lines:

UseProviderFormatting = True
DisplayCellColor = True
into the Query file, but that had no visible effect.

(Yes, i changed the properties for the cube measures and saw their impact in
the Analysis browser.)

Thanks for your input.

3rd question...I keep hearing rumours of an Excel Plugin for analysis
services...available soon...can you shed any light on that?

""Bas Kersten [MSFT]"" <bask (AT) online (DOT) microsoft.com> wrote

Bas

Thanks so much for your response.

The sysadmin here applied the SP3 to the analysis server and ran ptsfull.exe on my computer. This solved the problem of the client side machine CPU going crazy (after I modified the *.oqy file) but the response time running on the server is still way, way too long. For a cube created from an underly sql server table with only 100,000 rows I'd expect the response time to changes in the pivot table to be nearly instantaneous. As a matter of fact when I load the underlying data into a pivot table my desktop PC can navigate around the pivot table very easily. The server (4x3.0Mhz Xeons, 2GB RAM, RAID 0 temp drive, RAID 5 data array) won't provide a similar response. I checked the task manager and the CPU is pegged at about 15% and the System Monitor shows the disks are hardly doing anything. I'm tempted to reinstall analysis server and see if that helps because I'm at a loss

Regarding the formats, the solution seems worse than the symptoms. Hopefully you guys will change how that works with Yukon.