I have a cube with many dimensions (800). MDX queries are slow but not
disasterously so (6 seconds against 2000 row table).

The real problem is access to schema rowsets. The Profiler shows 20+ seconds
to return the MDSCHEMA_CUBES rowset.

I'm running SSAS 2005 Enterprise on Windows 2003 Server Enterprise X64 SP1
on a dual chip-dual core Opteron machine with 4 Gig of RAM.

Performance shows that no more than 30% of the CPU is ever used. Is there a
way to encourage SSAS to use more CPU and would that speed things up (a
Windows or SSAS setting)?

There does not seem to be a great deal of disk i/o going on.

Can anyone suggest how I can approach this problem (other than changing the
cube)? Is there a good intro to diagnosing and resolving performance problems
in Windows 2003 Server (x64) or SSAS 2005?

Thanks!

--
Bob Hodgman

I think the problem here is simply the number of dimensions... Each
dimension behind the scenes is also a cube and the schema rowset has to
iterate and analyze the dimension cubes before it decides to filter them out
of the result. I'm guessing that this is costing you a lot.

As per the other posting, if you can group the attributes together in larger
dimensions, you should see good improvements here.

Schema rowsets won't use more than one processor -- this is not an operation
that can be done in parallel.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Bob H." <BobH (AT) discussions (DOT) microsoft.com> wrote

Akshai - I see you and Jeje have answered my question in your responses to my
other post. Thanks!
--
Bob Hodgman


"Akshai Mirchandani [MS]" wrote:

Thanks, Akshai!

Is there a section in Books Online (or other documentation) that would
describe in more detail how to combine the dimensions? I'm unclear on how you
can combine dimensions and still maintain the right level of granularity.
Would a combined dimension have a dimension table that contained all possible
combinations of the values of the old, separate dimensions?

If a document that describes this doesn't come to mind right away, please
don't feel the need to research it. You've already been very helpful and I'll
be experimenting and reading today, anyway.

--
Bob Hodgman


"Akshai Mirchandani [MS]" wrote:

I don't have such a document. But to answer your question: yes, essentially
such a dimension table would be the crossjoin of all the values of the old,
separate dimensions.

If most of those attributes were yes/no type fields, then the number of
combinations for each natural entity will become large as you combine lots
of them together -- so you will have to balance the number of attributes in
the dimension with the number of dimensions. But even putting 16 attributes
in each dimension (2^16 = 65536 members) would reduce the number of
dimensions to 800/16 = 50.

If possible, I would also recommend creating an integer key for the
combinations in the dimension table rather than using a huge composite
key -- use some ETL process to put the integer key into the fact table
instead of all the attribute values...

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Bob H." <BobH (AT) discussions (DOT) microsoft.com> wrote

Thanks, Akshai!

I just discovered that the math is important when determining the number of
old dimension tables to combine in each new dimension. I was trying to create
a new dim table for 16 old, but each old dim table contained about 5 (not 2)
values. Heh-heh. Those numbers get big pretty quick. Even if I can't get down
to 50 dimensions, though, it looks like this might be the only way to cut the
time necessary for the queries.

I'll avoid composite keys in the fact table too.

Thanks for all your help.

--
Bob Hodgman


"Akshai Mirchandani [MS]" wrote: