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

Pedro Salgueiro pedro.salgueiro at inductiva.pt
Wed Jul 11 07:32:56 EDT 2012


First, thank you all for the help on this problem.

Ok, some problems have been solved, but others came out.

I am using Maven to get mondrian together with all its dependencies. 
Tried several versions from 
http://repository.pentaho.org/artifactory/pentaho/pentaho/mondrian/, 
including 3.3.0.14701, 3.4.0-SNAPSHOT and 3.4.5 with no luck.

Today I pulled the master branch from git hub, and the problem was 
solved these queries. Soon I realized that I should be using the version 
TRUNK-SNAPSHOT from Pentahoo Maven repository, which also solved the problem

Then we started testing the query against our cube (which is slightly 
different from the one we posted here), and found out that it is still 
producing bad results.  The major difference between the queries, is 
that we use a DISTINCT() inside the TOPCOUNT. We tried to replicate the 
problem in Foodmart by inserting the DISTINCT in the TOPCOUNT, and the 
results are consistent with our cube:

     WITH
     SET [Top Count] AS
     {
         TopCount(
             DISTINCT([Customers].[Name].Members),
             5,
             [Measures].[Unit Sales]
         )
     }

     SELECT
     [Top Count] * [Measures].[Unit Sales] on 0
     FROM [Sales]
     WHERE [Time].[1997].[Q1].[1]:[Time].[1997].[Q3].[8]


This query produces the following results:

    USA
    WA
    Spokane                                      Port Orchard  Spokane
    James Short Frank Darrell Christine Brubaker Albert Wilcox Curtis Pollard
    Unit Sales  Unit Sales    Unit Sales         Unit Sales    Unit Sales
    =========== ============= ================== ============= ==============
             244           267                187           174            234

While using the same query, but without using the set, the results are 
different:

    SELECT
    TopCount(DISTINCT([Customers].[Name].Members), 5, [Measures].[Unit Sales]) * [Measures].[Unit Sales] on 0
    FROM [Sales]
    WHERE [Time].[1997].[Q1].[1]:[Time].[1997].[Q3].[8]

Producing these results:

    USA
    WA
    Spokane
    James Horvat Mary Francis Benigar Matt Bellah Linda Combs Emily Barela
    Unit Sales   Unit Sales           Unit Sales  Unit Sales  Unit Sales
    ============ ==================== =========== =========== ============
              335                  325         292         291          286

We made some more experiments, and we reached a "conclusion" that when 
we are using something which outputs a "calculated" set, inside a 
TOPCOUNT, which on its turn is inside a SET;  the TOPCOUNT produces bad 
results. As an example, we inserted an EXCEPT() in the TOPCOUNT, and the 
behaviour was the same, producing bad results:

    WITH
    SET [Top Count] AS
    TopCount(
              Except([Customers].[Name].Members, { [Customers].[USA].[WA].[Spokane].[James Horvat] }),
              5,
              [Measures].[Unit Sales]
             )
        
      SELECT
      [Top Count] * [Measures].[Unit Sales] on 0
      FROM [Sales]
      WHERE [Time].[1997].[Q1].[1]:[Time].[1997].[Q4].[10]

producing the following 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

While using the query without the set:

    SELECT
         TopCount(
                  Except([Customers].[Name].Members, { [Customers].[USA].[WA].[Spokane].[James Horvat] }),
                  5,
                  [Measures].[Unit Sales]
          ) * [Measures].[Unit Sales] on 0
    FROM [Sales]
    WHERE [Time].[1997].[Q1].[1]:[Time].[1997].[Q4].[10]

produces the following results:

    USA
    WA
    Spokane
    Mary Francis Benigar Matt Bellah Ida Rodriguez Kristin Miller Emily Barela
    Unit Sales           Unit Sales  Unit Sales    Unit Sales     Unit Sales
    ==================== =========== ============= ============== ============
                      422         363           344            323          31

By the way things look, it seems that a TOPCOUNT inside a SET doesn't 
like to receive "calculated" SET. Either that, or I am doing something 
very wrong and can't see it.

About the property settings, we are using the default 
mondrian.properties file, and not passing extra settings while 
establishing the connection to mondrian.

Cheers,
Pedro




On 07/10/2012 07:23 PM, Julian Hyde wrote:
> What property settings are you using? I've just tried this on the master branch (3.4) and all 3 queries give the same result. The SQL is querying all 10 months, as it should.
>
> Julian
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120711/86042d99/attachment-0001.html 


More information about the Mondrian mailing list