hi All,

AS2000

I'm having a strange issue with aggregates designed by AS.

Currently I have 2 copies of my DEV database (i simply made a copy - no
chagnes after that). The connection to source is the same.
When I design MOLAP storage on DB 1 for one of the cubes, I get
648 aggregates, 250MB, 99% performance

when i do it on it's identical copy i get
711 aggregates, ~14GB!!, 80% performance

It looks like aggregate designer goes completely different path. The
underlyuing source data is the same.
Any hints what is going wrong? anything I can do to influence that behaviour?

Many thanks,
Robert

Are "Fact Table Size" of the cube and each "Member Count" of every dimension
levels the same?
They affect on the aggregation design.

And... usaully 10 to 30% aggregation is enough.

Ohjoo


"Robert Neptune" <RobertNeptune (AT) discussions (DOT) microsoft.com> wrote

Thank you for sugestion.

I tried changing row count but it had no affect.

I do take your comment about %aggretates. In my case, I was quite ok though
to have only 250MB with 100%.

The point was I woudl like to understand better why i get difference below.
I also think that aggregates generated in these 2 cases are very different.
Calculating aggs in frist case took ~11 minutes. In second case I stopped it
after 30 minutes @ 20% progress.


"Ohjoo Kwon" wrote:

Well... I think the result should be (almost) the same logically if
everything is the same.

Anyway, you may learn more about the aggregation design from "Microsoft SQL
Server 2000 Analysis Services Performance Guide" at
http://www.microsoft.com/technet/pro.../ansvcspg.mspx.

And... if you want to find out what differences are after aggregation
design, Partition Aggregation Utility will be helpful
http://download.microsoft.com/downlo...aggutility.exe.

You can verify and do much more things related to aggregations by partition
with it.

Ohjoo

"Robert Neptune" <RobertNeptune (AT) discussions (DOT) microsoft.com> wrote