I recently implemented a BI solution for a large organization. Before this,
they had an Excel pivot table based on a big query that fed Excel with flat
data worth of 70Mbytes. The new solution is based on SSAS and gives an Excel
XP pivot client of about two hundred Kbytes.

Some technical details of the cube are: There are 3 dimensions, one of which
is time. The other two dims have 2 hierarchies and about 1.5 thousand members
at the leaf level. The fact table consists of 6 facts. Each of them splits to
two calculated the negative and the positive values based on a CASE
statement. The fact table has about 500 thousand rows (for the time being).

The problem is that the users are experiencing a delay of 5 to 8 seconds for
each pivoting operation they do, while the old solution gives almost instant
pivoting operations. I know that there are a lot of issues to investigate. I
also understand that a slower pivoting operation is expected as Excel
requests the data from the SSAS server and this is the trade-off you get for
the really small Excel file but I was just wondering if I have overlooked
something...

are you using AS2005 or 2000?

have you calculated members?
what is the query response time on the server side? (usage analysis)

"Manos Kelaiditis" <mkelaiditis (AT) nospam (DOT) nospam> wrote

Have you checked where the bottleneck is? Is it on the server or on the client?

"Manos Kelaiditis" wrote: