Hi,

Could you let me know why you use Aggregate function in this way? Aggregate
function returns a calculated value using the appropriate aggregate
function. Normal usage is as below:-

WITH MEMBER Geography.Total AS 'AGGREGATE({USA, France})'
SELECT {Measures.SumSales, Measures.MaxSales} ON COLUMNS,
{USA, France, Total} ON ROWS
FROM SalesCube
WHERE ([1998])

Using in WHERE clause is strange. Can you try not using AGGREGATE function
in WHERE clause? Does it work the same?


Bill Cheng
Microsoft Online Partner Support
Get Secure! – www.microsoft.com/security
This posting is provided “as is” with no warranties and confers no rights.
--------------------

Aggregate is a standard way to reference a set in the slicer/where
clause. But how many members are in:

descendants([Store].Standard.[Southwest],Store))

versus how many members are expected to be selected? If the latter
number is much smaller, than the performance may be much better than
what you are measuring - after all, Aggregate() has to apply the
appropriate function (Sum, Count, etc) across the set of members at
query time.

If the selected set is large, then the only optimization that comes to
mind is to design a hierarchy where substantial subsets are already
pre-aggregated.

If the only aggregate functions are Sum and Count, then a complement set
approach is possible, ie. if A = All - B, then Sum(A) = Sum(All) -
Sum(B) (B much smaller than A).


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Calculated members are always calculated at run-time as the cell values are
displayed or requested. The reason for the difference in perfomance is that
in the first query, the calculated member is referenced in the slicer.
Since the calculated member is referenced in the slicer, the value of the
calculated member is calculated for the each of the axes intersection. In
the second query, the calculated member is never referenced and therefore
not calculated.

One approach I've seen to this type of issue is to create a named set then
create the calculated member using the named set. The query would be a
little more involved, something like the following:

WITH set SLICER_SET_1 as 'descendants([Store].Standard.[Southwest],Store)'
MEMBER Store.Standard.AllowedStores AS ' AGGREGATE( SLICER_SET_1) ',
SOLVE_ORDER = 0
select
{[Measures].[Units], [Measures].[Net Sales]} on columns,
{ TopCount(crossjoin({[Product Style].members},{[Product
Division].[Drinks]}), 20,[Measures].[Units]),
TopCount(crossjoin({[Product Style].members},{[Product
Division].[Snacks]}), 20,[Measures].[Units]),
TopCount(crossjoin({[Product Style].members},{[Product
Division].[Food]}),
20,[Measures].[Units])
} on rows
from [Sale]
where ( [Store].[Standard].AllowedStores ,
[Time].[Marketing].[Week].&[05-Jul-04])

The big question is going to be the number of descendants that the
[Store].[Standard].[Southwest] member has at the [Store].[Standard].[Store]
level. If there are only one or two, there shouldn't be a major performance
difference. On the otherhand, if there are a few hundred descendants, the
calculated member would involve hundreds of core calls which would slow
performance.

Regards,

John Desch, MCDBA, MCSD
Microsoft SQL Analysis Services Support

This posting is provided “AS IS”, with no warranties, and confers no rights.