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

Pedro Alves pmgalves at gmail.com
Mon Jul 9 12:56:58 EDT 2012


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


More information about the Mondrian mailing list