While trying to tune our cube, we realized that we do not know what an
aggregation really is. What kind of data do they contain, how can this data
or metadata be retrieved, what measures and dimension are involved in a
certain aggregation?
Nor are we able to find out what aggregations are build dependent on storage
definition and performance gain. What aggregations are build when Usage
Based Optimization is involved? (We have a cube that was build with usage
based optimization. With 10% performance gain we had 3 aggregations with 99%
we had 20. The same cube without optimization with 75% had nearly 500
aggregations. Whatever the performance adjustment with UBO was, the result
was all the same bad - compared to standard aggregated cube - no significant
difference whatever the performance adjustment was.)

We are eager to find out, how we can control what aggregations are build,
how to find out what aggregations have been built (adomd does not realy
gives much information - each aggregation seems to be connected with all
dimensions and measures a cube has) and by the way: why does the process
of - just (!) - calculating aggregations, while doing storage definition,
does not come to an end when a cube reaches a certain size (i.e. dimensions,
measures, no of rows of the fact table) and performance gain should exceed
e.g. 15% when we give the correct number of rows of the fact table - while
calculation performes fine when we give a much smaller number of rows? What
really happens in the background of this process?

We use SQL-Server 2K Enterprise

Thank's a lot for any hint!
Thank's too for any hint that helps giving our cube a better performance!

Stephan

first, there is a tool available (microsoft ewb site) to see the
currentaggregations and manually create new one.
second...
when you create an aggregation, the system precalculate the measures for a
combination of levels from different dimensions.
for example, the system create an aggregate for "All time", "Country" etc...
so the measures are calculated for this combination and stored on the disk.
so now, when a user aski for this combination, AS will read the aggregated
value (1 cell) instead of read a large number of cells and aggregate them
on the fly.
imagine you have 365 days and 1000 customers. if you query your cube against
the full year and for all the customers in a particular country, without
aggregations, you have to sum 365 * 1000 cells (365 000). because you have
aggregated the All years + country, you reduce this to 1 cell only, the
preaggregate one!!!

you can see the difference with a cold cache, because AS keep in cache some
values after the first access, so if you have not aggregated the cube, the
first access is slow (365 000cells to read) , but following accesses are
cached.

1 aggregation allways include all dimensions and all measure of a cube
but aggregated at the top level (the "all member" ) is like the dimension is
not optimized, except at the all level.

I hope this will help you.

"Stephan Schulte" <office (AT) st-j-schulte (DOT) de> wrote