Four, five dimensions is normally no problem. Tell us more about the
hardware you are using on the client and server. Generally I recommend 256
MB RAM on the client if you use an O/S like Windows XP. It is harder to be
more specific about the server because it depends on the number of cubes,
the number of dimensions in each cube and the number of members in each
dimension. The number of concurrent users is also important. A small server
with a limited number of users and cubes can start with 2 CPU:s and 512 MB
RAM. A good disksystem is also<important.

HTH
Thomas Ivarsson

"Tom Walker" <twalker (AT) tom (DOT) net> wrote

Thomas,

The cube that I have is very small. The fact table has
just 790 rows. There are 8 shared dimensions in the cube
and have 5, 104, 68, 50, 12, 503, 6, 20 rows respectively.
As you can see the dimension that has maxmimum no of
members / rows has 503 rows.

All these tables are properly indexed and have foreign key
definitions declared. The machine that has analysis server
running has 512 MB RAM and 2 * 866 MHZ Intel processors.
The client that uses Excel XP to access the cube has 196
MB RAM and 1 * 400 Mhz processor.

I am pretty sure a cube just this small shouldnt be slow
to browse. If I include up to 3 dimensions, the
performance is fine but when I include the 4th and 5th
dimension the data retrieval slows down.

I used to have MOLAP storage for this cube and right now I
have ROLAP. ROLAP seems to be a better performer than
MOLAP. Nobody would believe me If I say that :-)

Any pointers, ideas ?

Thanks,
Tom

PS : I thought its just my cube is slow, but when I browsed
the sales cube in the server itself using the cube browser
that was also very slow. So I am not sure whether the
client is the problem.

Depends on the hardware I am using, but if the dimensions are not large and
I try this on a modern workstation that is normally not a problem. Normally
you use 4-5 dimensions beacuse the report gets to complicated if you exceed
that. Performance will also degrade when you add more dimensions.

A possible solution to your problem is to increase the fact table size
estimate in the cube editor. If you have 1000 records in the fact table
increase the estimated size with the factor ten or twenty to 10.000-20.000
estimated fact table records. After that build new aggregations and use
MOLAP for storage.

HTH
Thomas Ivarsson

"Tom Walker" <twalker (AT) tom (DOT) net> wrote

Let us see if we can bracket the problem to see where the real issue is.

1) Is the problem with performance in processing or querying? It sounds like
querying. But that is the first step in Analysis Services performance
tuning.

2) If in querying, is it just one tool or all tools? What is performance
like when you use the cube browser (right-click the cube and select "Browse
data...")? What is performance like when you use various front-end tools?
Consider either purchasing a copy or getting an evaluation copy of various
mainstream OLAP query tools. If you monitor this alias, pick one or two that
you hear a lot.

3) Try the query on a different higher-performance client machine. For
example, does performance improve significantly when using the server itself
for browsing. From your posting you said it was only a single 400MHz Office
XP machine. That is pretty low-end these days.

4) Does adjusting the aggregation design effect performance? How many
aggregates are currently being designed? See what happens if you go more or
less.

5) When you start getting poor performance, what is happening on the server?
Are you getting CPU spikes and a significant increase in I/O? If so, it
would seem to indicate that you may be hitting aggregates that need to be
calculated (which requires more CPU and I/O than if you were hitting
pre-calculated aggregates).

Hope that helps.

--

Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Thomas Ivarsson" <thomas.ivarsson (AT) exallon (DOT) sigma.se> wrote