Hello,

When designing aggregations for my cubes in AS2000, I usually just let AS
design "Until I click stop" and the let it go to 100% performance gain.
However, this can lead to some rather large storage designs, and since I
have some HUGE performance issues when querying the cubes (in particular
when calculated members are involved in the query), I was wondering if it
can give a better performance if I do not optimze to 100%?!

Also, since I have 18 (shared) dimensions for my cubes, I have used the
"Dependent dimension"-option when applicable to get better performance. Does
this sound reasonable.

--
Michael Barrett

100% performance on aggregations is very excessive. With 18 dimensions
involved, I'd expect that to take hours and maybe even days to figure out to
produce an aggregation set in the wizard, unless you are using parent-child
dimensions which will make the overall possible set of aggregations much
smaller.

For a cube with 18 dimensions, I'd probably recommend that you start with
somewhere between 5 and 10 percent for peformance gain, and then use usage
based optimization to produce the optimal aggregation design. I don't know
how many aggregations you have currently designed, but with 18 dimensions,
it's likely to be quite high, and it's very likely that a large majority of
them are probably never being used (but they still require processing time
and space). The goal should be to produce the smallest aggregation set
possible (lowest processing time and space) with having an optimal query
performance time.

It could be that by setting the dependent dimension attribute that that is
reducing the possible aggregation set and as such might explain why you're
able to select 100% aggregation through the wizard and not have it take days
to complete. However, the dependent dimension attribute is only used
during aggregation design, it doesn't mean that the server will actually use
an aggregate from a dependent dimension while users are querying the
cube...so you don't get better query performance by setting this attribute
directly, it just reduces the overall aggregation space during design.


--
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.



"Michael Barrett" <mbiwj001 (AT) sneakemail (DOT) com> wrote