Hello,

From ProClarity, I obtain a strange result between two requests based on the
cube "Adventure Works":

1°) -----------------------
SELECT { [Measures].[Reseller Order Quantity] } ON COLUMNS ,
NON EMPTY { ORDER( { DESCENDANTS( [Promotion].[Promotions].[All Promotions],
[Promotion].[Promotions].[Promotion] ) }, ( [Measures].[Reseller Order
Quantity] ), BDESC ) } ON ROWS FROM [Adventure Works]
WHERE ( [Promotion].[Min Quantity].&[0] )

--> The result is correct and we obtain this:
Promotion Level (Promotions hierar.) Reseller Order Quantity
Touring-3000 Promotion 1,561
Touring-1000 Promotion 762
Sport Helmet Discount-2003 680
Sport Helmet Discount-2002 492
Mountain-100 Clearance Sale 456
Mountain-500 Silver Clearance Sale 382
Road-650 Overstock 304

-----------------------

Now, if I add a "TopCount" on the rows, the result is wrong:
2°) -----------------------
SELECT { [Measures].[Reseller Order Quantity] } ON COLUMNS ,
{ TOPCOUNT( { DESCENDANTS( [Promotion].[Promotions].[All Promotions],
[Promotion].[Promotions].[Promotion] ) }, 5, ( [Measures].[Reseller Order
Quantity] ) ) } ON ROWS
FROM [Adventure Works]
WHERE ( [Promotion].[Min Quantity].&[0] )

--> Wrong result:
Promotion Level (Promotions hierar.) Reseller Order Quantity
Touring-3000 Promotion 1,561
Touring-1000 Promotion 762


The "TopCount" is applied to all the members of the level "Promotion"
("Promotions" hierarchy), as if filter (in the clause WHERE) missed.
It would seem that the filter (WHERE clause) is applied after the
calculation of the "TopCount".

An explanation?

Thanks a lot.
Pascal

PS: To facilitate the comprehension of the problem, here is the same
request, without the WHERE ( [Promotion].[Min Quantity].&[0] ) slice and
without "TopCount":
Promotion Level (Promotions hierar.) Promotion Min Qty Reseller Order
Quantity (the Measure)
Volume Discount 11 to 14 11
16,063
Volume Discount 15 to 24 15
10,713
Volume Discount 25 to 40 25
2,321
Touring-3000 Promotion 0
1,561
Touring-1000 Promotion 0
762
Sport Helmet Discount-2003 0 680
Sport Helmet Discount-2002 0 492
Mountain-100 Clearance Sale 0 456
Mountain-500 Silver Clearance Sale 0 382
Road-650 Overstock 0
304
Volume Discount 41 to 60 41 85

Actually, the issue exists even with Order():

Head(ORDER( DESCENDANTS( [Promotion].[Promotions].[All Promotions],
[Promotion].[Promotions].[Promotion Name] ),
[Measures].[Reseller Order Quantity], BDESC ), 5) ON ROWS
FROM [Adventure Works]
WHERE ( [Promotion].[Min Quantity].&[0] )
---------------------------------------------------------
Reseller Order Quantity
Touring-3000 Promotion 1,561
Touring-1000 Promotion 762

Based on a related thread in the Analysis Services MSDN Forum, this
should be fixed in the forthcoming SP2:

http://forums.microsoft.com/MSDN/Sho...17552&SiteID=1
...
Had an update from Microsoft. The implementation of the fix will take
longer than expected (backporting from SP2 to SP1). Now due 1st week of
September.
...
btw, we have the fix on early release from Microsoft and are testing it
now. The results are promising.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***