Dear all,

In the past, my Excel Pivot Table can fetch the result within 10 mins. But
now, after adding 200,000 records into the fact table, the query speed
dropped greatly (I fetched the same result). I stopped the query after one
hour even though Excel hasn't finished its job.

What are the possible problems in my AS, Excel or other things else? I hope
somebody can reply as soon as possible coz this problme is urgent!

Thanks a lot first!

Polly

well... you talk about rows in a fact, but I presume you have reprocessed
your cube...
how many rows your cube had before these 200 000?
have you done a full process? (in both dimensions and cubes)
when you plan far more rows then your test data, setup the targeted number
of rows in your cube. With this number AS design the aggregates regarding a
higher number of rows then the reality.

also, use the usage based analysis to identify if the performance issue is
at the server level or at the client level.

if your query takes less then 5 seconds on the server, then the problem is
the client and the complexity of your calculated measures.

How many rows do you display in Excel?

"Polly" <Polly (AT) discussions (DOT) microsoft.com> a écrit dans le message de news:
708E7B1A-7959-479E-B1C7-D084D9247DC7...soft (DOT) com...

what the usage analysis says?
does the query takes a lot of time to be processed by the server?
setup the log query interval to 1 in your servre properies to log every
query sent to your cubes.
play with the cube, and see the results with the usage analysis.
if the response time is lower than your display time then the problem is at
the render level. (for example, the rendering process takes 1minute and the
server process time takes only 5 seconds, then too many calculations are
done by the client)
Try a tool like www.reportportal.com (there is a free version and an
evaluation version) this tool support the paging feature, so you'll display
only 100 rows (or other number) by page.

Try to play with the client connection string to insure you use server side
calculation instead-of client side. (the client can retrieve more rows from
the server to calculate the measures at the client side,

How many members has your biggest dimension?
how many members are displayed in OWC (or other tool) at the same time?

read this article:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

maybe the section : Optimizing Clients for Slow Network Connections


"Polly" <Polly (AT) discussions (DOT) microsoft.com> a écrit dans le message de news:
3585EFA1-5790-41B2-A4A7-A47000232CAA...soft (DOT) com...

maybe your biggest dimension is too large for your server memory.
AS load the dimensions in memory, if it's too large, then the VLD (very
large dimension) system is used.
In this case, the large dimension is always read from the database and not
from the cube.


"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> a écrit dans le message de news:
%23eiWiDJlEHA.208 (AT) TK2MSFTNGP12 (DOT) phx.gbl...