I've got a relatively complex cube (26 dimensions, 33M rows). When I
process the cube, the CPUs get hammered when it comes to aggregation
calculation. The box is an 8-way with 700MHz CPUs and they all are at
90-100% throughout the time aggregations are being calculated.



Also, the Storage Design Wizard takes forever and doesn't come up with
much in terms of performance gain. It's running right now and has come
up with 1355 aggregations (36.3MB, 6% Performance) so far.



I imagine this has to do with aggregation and/or cube desing but I'm not
sure where to look next.



Any thoughts would be appreciated.



Randy Knight

Senior Database Engineer

match.com


--
Randy Knight
Senior Database Engineer
Match.com


Posted via http://dbforums.com

I'm thinking that 26 dimensions is causing to many
possible intersections. Are any of these dimensions
virtual? If not, converting some to virtual should help
processing the aggregations since virtual dimensions are
not pre-aggregated.

Also, I would seriously look at breaking the cube into
smaller functional cubes with fewer dimensions. Are there
really queries where a user will be using all 26
dimensions at once? Breaking them down into smaller cubes
will not only help processing performance, but also query
response.

Using dimension hierarchies where possible will also
help, since AS does not create aggregates between
hierarchies.(I think)
(ie. customer.byregion, customer.byincome, etc.)

HTH Lutz