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

Pedro Salgueiro pedro.salgueiro at inductiva.pt
Mon Jul 9 12:54:26 EDT 2012


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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120709/edcb1174/attachment.html 


More information about the Mondrian mailing list