When I select a distinct view of the query log that captures OLAP
queries, I get 9 distinct aggregations. I sample each query (1).

When I do the usage-based optimizer in AS, it finds only 5. When I
inspect these 5 with the Partition Explorer tool, it is missing the
single aggregation that would make my MDX query run the fastest.

Why does the optimizer not include some aggregation designs? Is there
a setting I need to make to get it to include all designs and not
"pick and choose?"

I realize that it may be able to use other aggregations to provide the
missing data, but in my case the 1 level I needed aggregated was
missing from all 5 aggregations the optimizer chose. However, if it
had used all nine aggregations, there were two of the nine that had
the needed level aggregation.

Any expertise out there with this type of issue?

Thanks

Kory

When using the UI to create usage-based aggs, at the last step it displays
the form with the chart on it. This UI provides one last (confusing)
opportunity to limit the # of aggs that will be built. Make sure the
defaults aren't limiting the aggs.

tom @ the domain below
www.tomchester.net


"Kory" <kory.skistad (AT) gmacrfc (DOT) com> wrote

In the event that you find that after you try Tom's suggestion below that
the aggregates are still not added, you could still go in an add them by
hand using the Partition Explorer from the SQL Server 2k resource kit.

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.

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

I did select the option to run until I click stop, but it quit after
only 5 aggregations. The other two options were by size or
performance, which the size was 60M and Performance went to 100%. In
my experience, the performance always goes to 100% if you use
Usage-based optimization and let it find all the aggregations.

In my case, it only found 5 out of the 9 in the query database, no
matter how many times I ran it.

BTW- I found your utilities on your web site extremely useful. To
round out your toolbox, a tool to design and save aggregations would
be perfect I've used the Partition Explorer tool but it is buggy
and I think Excel would be a better environment to host this type of
utility. Just a thought...


Thanks

Kory

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

Thanks Kory. You're right, unlikely I'll be rev'ing the 2000-based stuff
though. Looking to the future.

tom @ the domain below
www.tomchester.net


"Kory Skistad" <kskistad (AT) hotmail (DOT) com> wrote

Good point Tom. Hopefully "Yukon" Analysis Services will have a lot
of management tools included without users having to build their own.

Kory

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

I've tried that, but the Partition Explorer seems a bit buggy to me
Sean. When I create the aggregation, it doesn't show up as being
created. And then when I go into the Partition Analyzer GUI to design
aggregations, it states I have 5 aggregations, not 6, after I've added
one with the Partition Explorer tool.

Speaking of Partition Explorer... when you use the Partition Usage
button, and it gives you the option of choosing all levels, top level
only, bottom level only, etc. What is that for, and how does that
help when designing aggregations?

Thanks

Kory

"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote

I just found this explanation in a microsoft article:
http://www.microsoft.com/technet/tre...e/AnSvcsPG.asp

"The Usage-Based Optimization Wizard honors the settings for each
dimension's Aggregation Usage property. If you have previously
excluded certain dimensions and levels from consideration, you should
use the Cube Editor to enable these dimensions and levels before
running the Usage-Based Optimization Wizard, to ensure that all
dimensions and levels are considered based on actual query patterns.
For example, if you have marked the quarter level of the Time
dimension as disabled for aggregation and users are actually querying
at the quarter level, the Usage-Based Optimization Wizard will not
design aggregations at the quarter level. "


kskistad (AT) hotmail (DOT) com (Kory Skistad) wrote in message news:<c27ce0e1.0310020557.73efc9fe (AT) posting (DOT) google.com>...