When I display the cube with OWC ( Office Web Component ) in Visual Basic, it works fine. If i display the same cube in Excel with Pivot table, it became extremely slow, Can anybody help?

Fishey

are you talking about using olap/analysis services, or an offline cube?

Both local cube (.cub) and analysis server cube.

Got any idea?

the OWC would be using resources on the Webserver, where Excel pivot table would be using resources on your desktop.

Is your webserver faster CPU and more memory that your desktop?

Does it have a faster network connection to the db server?

i personally hate excel-- i think that it is a dog.

i am an access guy, and i am currently writing an alternative to OCW-- what dont you like about OCW?

Actually, OWC ( Office Web Component) does not only use on webserver, we can use it on local also. It's ActiveX controls.

I didn't use it on web, just for local, in Visual Basic project.
So i think it has nothing to do with webserver.

I do like OWC, but our clients will use Excel to display cube. That's the reason.

Any other ideas?

in excel, if you select the pivot table, and then show the pivot table toolbar, and select table options, you can turn off 'refresh on open' and this should have a dramatic improvement in performance.

is it slow on open, or is it slow everywhere?

also, you can do things like process the cube more, etc....

Actually, i didn't check 'refresh on open' in Excel.

I have 5 dimensions in the cube and when i just put 2 of dimensions in Excel, the speed is OK, if i put a third one, the speed slowdown, i didn't get result for more than half an hour.

How to "do things like process the cube more, etc", i didn't get it.

are you doing SQL Server Analysis Services or Offline Cubes?

the last time i asked you, you said BOTH.

i dont know if you can do this in Excel.

if you are using SQL Server driven Analysis Servcies, then i can help you.

also, are yuo using multiple ranges in your spreadsheet?

is some in excel, and some in OLAP?

I created a cube with analysis manager or use DSO to create a cube in visual basic, this kind of cube is called server-side cube, it exist in analysis database, right?

If using CREATE CUBE to create a cube, this kind of cube is called a local cube, it exsists in a .cub file, right?

Then i can display both kinds of cube in Excel with Pivot table, right?

But i got the speed is very slow if i put more than 3 dimension in the pivot table. Got any idea?

"multiple ranges in your spreadsheet", i didn't get it. Could you explain it?

Thank you so much.

then id stop showing so much data in excel-- or turn off drilldown, or something.

you either have too many rows or columns (which is causing a table scan on the server (the root of all performance problems))

are you using ROLAP, HOLAP, MOLAP?

when you calculate aggregations, have you tried calculating to a higher percent?

have you done things like in Excel <TOOL>, <OPTION>, <CALCULATION>, Calculation = manual

i dunno man. i wish i could help more. I HATE Excel more than anything (because there are WAY too many people that try to use it as a database, or for database reporting (and it cant compete with Access Reports for that)

Actually i didn't use drilldown at all.

THERE ARE thousands of rows in the cube if i put more than 3 dimensions in Excel.

I use MOLAP.

for performance, it's 80%

<TOOL>, <OPTION>, <CALCULATION>, Calculation = automatically

One question: if i put one dimesion in Excel, it will expand all data at the same time, is there a way to prevent it from doing that?

Thank you very much.

can you turn off totals on columns or totals on rows?

(under table options in the pivot table)

I turn off totals on columns and totals on rows, but nothing changed.

I wonder the problem is: When put one dimesion in Excel, it will expand all data at the same time.

when you say it takes a half hour.. is that on a 233 or on a 1gig?

From: Fishey (fish_funny@hotmail.com)
Subject: Display cube in Excel extremely slowly


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-24 15:04:50 PST


I created a cube with 5 dimensions. When i just display 2
dimensions in Excel, the speed is ok, if i put one more
dimension, it slow down extremely and got a message said:
Low Virtual Memory. If i display the cube in Visual Basic
with OWC, it works fine. Does anybody have idea about this?
Post a follow-up to this message

Message 2 in thread
From: Andrea Worley (k_rage@hotmail.com)
Subject: Display cube in Excel extremely slowly


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-25 06:16:05 PST


What Excel are you using? Excel 2002 is much, much faster
than 2000. It also has more features. If you're using
Excel as a client, it's worth the upgrade to Office XP.
Also, does the slowdown happen with any third dimension or
are you always trying the same one? For example, I have
two dimensions, CustomerName and CustomerNo. When I use
them both, it is slow because there's actually a 1:1
ratio. But any other dimensions are quick.


>-----Original Message-----
>I created a cube with 5 dimensions. When i just display 2
>dimensions in Excel, the speed is ok, if i put one more
>dimension, it slow down extremely and got a message said:
>Low Virtual Memory. If i display the cube in Visual Basic
>with OWC, it works fine. Does anybody have idea about this?
>
>
>.
>
Post a follow-up to this message

Message 3 in thread
From: Fishey (fish_funny@hotmail.com)
Subject: Display cube in Excel extremely slowly


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-25 07:13:53 PST


Thank you very much, Andrea.

I used Excel 2000. Because i created cubes for our
clients, so i can't ask them to upgrade to Excel 2002 or
Office XP.
Yes, it slowdown when i add any third dimension. in the
cube, actually, dimension3 and dimension2 have 1:n ratio.
But if i display the cube in Visual Basic with OWC, it's
very fast.

If i use excel 2000 as client, is there any other solution
for this?
Post a follow-up to this message

Message 4 in thread
From: Andrea Worley (k_rage@hotmail.com)
Subject: Display cube in Excel extremely slowly


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-25 08:54:08 PST


Not that I know of. I have some users who use Excel 2000
and it runs very slow.

Some other reasons for Excel 2002:
Excel 2002 allows you to select multiple items on the page
filters. It also allows you to check and uncheck all
items at once, making it easier to choose a few fields out
of hundreds to filter on. And the grand totals actually
change when you filter, as opposed to some of the
filtering in Excel 2000. You can also add code so that
the user can drillthrough to the detail records.

But, if you can't switch, you can't switch. Sorry, I
can't help more.


>-----Original Message-----
>Thank you very much, Andrea.
>
>I used Excel 2000. Because i created cubes for our
>clients, so i can't ask them to upgrade to Excel 2002 or
>Office XP.
>Yes, it slowdown when i add any third dimension. in the
>cube, actually, dimension3 and dimension2 have 1:n ratio.
>But if i display the cube in Visual Basic with OWC, it's
>very fast.
>
>If i use excel 2000 as client, is there any other solution
>for this?
>
>.
>
Post a follow-up to this message

Message 5 in thread
From: Fishey (fish_funny@hotmail.com)
Subject: Display cube in Excel extremely slowly


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-25 11:24:10 PST


Andrea, Thank you very much.
I appreciated your suggestion, i'll try to tell this to
our clients.
Post a follow-up to this message

Message 6 in thread
From: Bluetooth (szymon_dot_slupik@cdn.com.pl)
Subject: Re: Display cube in Excel extremely slowly


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-25 11:20:02 PST


> But if i display the cube in Visual Basic with OWC, it's
> very fast.
>
[SS] It seems to be simple to explain. When you nest >1 dimension on an axis
in Excel, it expands all the rows. Doing the same in OWC the rows are
collapsed, just a "+" sign appears to notify an expansion option. This way
the MDX generated by both tools is different. OWC is in many aspects
superior to the big Escel brother.

HTH
Szymon Slupik, CDN S.A.
Krakow, Poland
Post a follow-up to this message

Message 7 in thread
From: Fishey (fish_funny@hotmail.com)
Subject: Re: Display cube in Excel extremely slowly


View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-26 08:40:23 PST


Yes, i also think that's the reason.