I am experiencing a problem when retrieving data from cubes using Excel, and when using the Cognos Powerplay Tool.

If I view the same data using the SQL Analysis Server and browse the data in the cube, the response is almost instantaneous.

I have a suspicion the problem lies with the pivottable services, but I don't know how to get around this.

Any suggestions?

I am running SQL 2000 with SP3 on a Win 2000 machine.

I've had this happen and reinstalling the MDAC's on the machine fixed it. If
that doesn't work you might re-install the pivot table services (PTSlite or
PTSfull)

HTH

--
Ray Higdon MCSE, MCDBA, CCNA
--
"Roger Klein" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I have tried that, but it does not seem to make a difference.

It is not all the queries that take long, but only ones where many rows (e.g. 800 000) are returned. Is there a ule of thumb for the number of rows to return to Excel, or any third party reporting tool?

When I look at the task manager, it is the Excel exe that is hogging the processor (100%) and I am using about 300 Mb of memory. It then just runs and runs for 15 to 20 minutes or more

As I said before when doing the same query within Analysis server even though the number of rows is very large, it returns almost instantaneously. The problem only comes in when using external tools

Any suggestions

----- Ray Higdon wrote: ----

I've had this happen and reinstalling the MDAC's on the machine fixed it. I
that doesn't work you might re-install the pivot table services (PTSlite o
PTSfull

HT

--
Ray Higdon MCSE, MCDBA, CCN
-
"Roger Klein" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:95495E83-55B4-4204-BA92-19373556EA4F (AT) microsoft (DOT) com..

Best solution for this is changing cube design, if
possible. Adding more levels and thereby reducing the
overall number of resulting rows is a good idea.

Our customer did not want that, so to ease workload on
the client, we moved all to the server. The server is
dedicated to ETL and AS only now.

Save your Excel workbook in Xml format and alter the
connection string used by it. Add "Execution
Location=3;Default Isolation Mode =1; Locale
Identifier=1033" (1033 for English server, 1031 for
german etc)...

If your client is creating custom sums by using multiple
selection in the page field, that will slow down query
response time a lot, because these sums cannot be
foreseen and preaggregated by AS. Same goes for large
parent child dimensions.

HTH Lutz Morrien