sql server data warehouse: I have a cube with 29 shared and private dimensions.
Several of the dimensions have multiple hierarchies.

When I run the storage design wizard, I chose MOLAP as my
storage option, and I move to the aggregation wizard.

I select stop when the performance improvement reaches
50%, and hit start. The graph jumps to around 4% and the
status indicates that 129 aggregations have been created.
At this point, the Analysis Services Manager stops
responding, and the creation of additional aggregations
grinds to a near halt. After leaving the process running
on the server for nearly 12 hours, I came back to find
that only 1200 aggregations had been calculated for a 7%
performance improvement and 2MB of storage.

I would like to know what the considerations are for
creating aggregations and how to speed this process up.
What does the storage wizard look at to determine which
aggregations to create, and why does it stall? Are my
dimensions malformed? Do I have too many dimensions? Are
there server settings that need to be adjusted?


Please help.

Thanks,

The storage design wizard primarily looks at the count of members in your
dimension levels as the basis for creating aggregations. 29 dimensions is a
lot of dimensions for a particular cube and is definitely on the complex
side. I'd recommend that you don't run the design storage wizard for more
than half an hour at most. In your case I'd actually specify zero
aggregates instead since your dimensional space is so large. There are so
many possible aggregates that the likelihood that any of them will benefit
your users is really quite small.

Instead, I'd recommend that you try 0% aggregation and then turn on the
query log and then do usage based optimization. If you know what some of
the queries are that your users will be interested in advance, you can run
those queries in a test environment and then run the Useage Based
Optimization wizard on that set. That will design aggregates that
specifically target those queries.

If you have no idea what types of queries the users will perform, try to
help out the wizard as much as possible, by going into the cube editor and
disabling some of the levels in the cube for consideration for aggregations.
This will reduce the complexity of the cube for the storage design wizard
and give you a set of aggregates that is more likely to benefit your users.

Also make sure that the dimension counts are reflective of the values
expected for a single partition. So if you are partitioning on month, make
sure that the level counts for that dimension are reflective of that. For
example, 30 days, 1 month, 1 year should be the level counts if you're
partitioning by month.

A lot of this information is availalbe in the Analysis Services Peformance
Guide which you can get at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ANSvcsPG.asp


Sean


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.



[quoted text, click to view]