I used Excel 2003 to show the cubes in SQL 2000 analysis services.
when I show the lowest level of the customer, then add the product dimension
and Expand to the lowest level. the responsible time is very horrible.

When I look at the CPU performance on the Server, the Server is not busy.
less than 5%. But the client is running at 100% CPU for a long time. upto
hours.

When I use the Office Web components 2003 to performance the same way, the
response time is much better.

Is there any way to improve the excel performance?
I try to use Excel 2007 Beta to performance the same way, the performance is
also horrible.

Excel is known to provide bad performance when you populate a large number
of cells
this is due to the Excel calculations, unlike the OWC, Excel evaluate if the
new content of the cell required some calculations to be recalculated.
(also Excel will reevaluate the cell format)
so this slow down the rendering time.

you have to train your users to retrieve only the required members from the
cube instead of a large resultset.

but hours... there is a possible formula issue
AS2000 push some calculation in the client side rather the server side.
try to change your connection string to add the "cache policy=7" option and
"cache ratio=0.01" option
read this:
http://www.sql-server-performance.com/wp_msas_7.asp

sometimes formulas perform better on the client side (due to the local
cache, dynamic DCount are better here) sometimes the formula perform better
on the server side (like ratios)

good luck.



"Kam" <Kam@discussions.microsoft.com> wrote in message
news:075D3E04-0188-4967-8794-71C9EE2A6C43@microsoft.com...
>I used Excel 2003 to show the cubes in SQL 2000 analysis services.
> when I show the lowest level of the customer, then add the product
> dimension
> and Expand to the lowest level. the responsible time is very horrible.
>
> When I look at the CPU performance on the Server, the Server is not busy.
> less than 5%. But the client is running at 100% CPU for a long time. upto
> hours.
>
> When I use the Office Web components 2003 to performance the same way, the
> response time is much better.
>
> Is there any way to improve the excel performance?
> I try to use Excel 2007 Beta to performance the same way, the performance
> is
> also horrible.
>

The bottleneck is: AS 2000 calculations up to a defined size are done on the
client.
Please check the Microsoft SQL Server 2000 Analysis Services Performance
Guide
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
Look for: "Use the Execution Location setting of 3 and the Default Isolation
Mode setting of 1."

and for "Set an appropriate value for the Large Level Threshold property."

Upgrading to SQL Server 2005 should help, because AS 2005 does calculate
always on the server.

HTH, Helmut

"Kam" <Kam@discussions.microsoft.com> schrieb im Newsbeitrag
news:075D3E04-0188-4967-8794-71C9EE2A6C43@microsoft.com...
>I used Excel 2003 to show the cubes in SQL 2000 analysis services.
> when I show the lowest level of the customer, then add the product
> dimension
> and Expand to the lowest level. the responsible time is very horrible.
>
> When I look at the CPU performance on the Server, the Server is not busy.
> less than 5%. But the client is running at 100% CPU for a long time. upto
> hours.
>
> When I use the Office Web components 2003 to performance the same way, the
> response time is much better.
>
> Is there any way to improve the excel performance?
> I try to use Excel 2007 Beta to performance the same way, the performance
> is
> also horrible.
>