[Mondrian] Unexpected behaviour while using a TopCount in a Named Set

[Julian Hyde]

I don't know whether it would make any difference with your particular data set, but for the record, the queries are not equivalent. According to the MDX spec, named sets are evaluated after the slicer axis but before other axes, therefore the second query doesn't inherit the 'NON EMPTY'.

It's worth trying the latest 3.4 code, as Pedro suggests.

Julian


On Jul 9, 2012, at 9:56 AM, Pedro Alves wrote:

> Did you try the latest code in 3.4 branch? We fixed some issues with 
> top count very recently
> 
> On Mon 09 Jul 2012 05:54:26 PM WEST, Pedro Salgueiro wrote:
>> Hi!
>> 
>> I am using a Named Set with a TopCount, which is then used in a Select
>> which has a set of time members in the WHERE clause.  For some reason,
>> I am getting an unexpected behaviour: The TopCount statement doesn't
>> seems to be affected by the WHERE clause. I tried to replicate the
>> situation while using the Foodmart and managed to get the same
>> behaviour, so I guess that we can exclude any problem with the cube we
>> are using.
>> 
>> To better explain the problem, this is the same query, but not using
>> the set:
>> 
>>    SELECT
>>    NON EMPTY TopCount([Customers].[Name].Members, 5, [Measures].[Unit Sales]) * [Measures].[Unit Sales] on 0
>>    FROM [Sales]
>>    WHERE [Time].[1997].[Q1].[1]:[Time].[1997].[Q4].[10]
>> 
>> This query produces the following results:
>> 
>>    USA
>>    WA
>>    Spokane
>>    Mary Francis Benigar James Horvat Matt Bellah Ida Rodriguez Kristin Miller
>>    Unit Sales           Unit Sales   Unit Sales  Unit Sales    Unit Sales
>>    ==================== ============ =========== ============= ==============
>>                      422          369         363           344            323
>> 
>> 
>> This query is equivalent to the following one, which moves the
>> TopCount to the set [TopCount]:
>> 
>>    WITH
>>    SET [Top Count]
>>    AS { TopCount([Customers].[Name].Members, 5, [Measures].[Unit Sales]) }
>> 
>>    SELECT
>>    NON EMPTY [Top Count] * [Measures].[Unit Sales] on 0
>>    FROM [Sales]
>>    WHERE [Time].[1997].[Q1].[1]:[Time].[1997].[Q4].[10]
>> 
>> 
>> Although this query is equivalent to the first one, it produces
>> different results:
>> 
>>    USA
>>    WA
>>    Olympia          Bremerton       Puyallup       Spokane
>>    Patricia Gervasi Richard Vansdal Tawnya Ancheta Edna Woodson Ida Rodriguez
>>    Unit Sales       Unit Sales      Unit Sales     Unit Sales   Unit Sales
>>    ================ =============== ============== ============ =============
>>                  226             156            220          243           344
>> 
>> The funny thing, is that this behaviour is only seen in some cases,
>> which is probably related to the data in the cube.
>> 
>> Is this the behaviour to be expected from the the last query? If so,
>> it doesn’t makes any sense, since the statement in the WHERE clause
>> should be propagated to the named sets, thus resulting in the top 5
>> customers for the given time interval.
>> 
>> Could this be a mondrian bug?
>> 
>> Has anyone encountered this problem before?
>> 
>> Best regards,
>> Pedro
>> 
>> 
>> 
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian