Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-295

Query generated by Excel 2007 gives incorrect results

    Details

    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      Anton Nikitin wrote:

      > Today I was experimenting with Excel 2007 pivot
      > tables and found that it generates a bit crazy MDX
      > statements.
      >
      > For example, very simple GUI navigation steps cause
      > the following MDX:
      >
      > WITH
      > SET [XL_Row_Dim_0] AS 'VisualTotals(Distinct(Hierarchize(

      {Ascendants([Customers].[All Customers].[USA]), Descendants([Customers].[All Customers].[USA])}

      )))'
      > SELECT
      > NON EMPTY Hierarchize(

      {[Time].[Year].members}

      ) ON COLUMNS ,
      > NON EMPTY Hierarchize(Intersect({DrilldownLevel(

      {[Customers].[All Customers]}

      )}, [XL_Row_Dim_0])) ON ROWS
      > FROM [Sales]
      > WHERE ([Measures].[Store Sales])
      >
      > Mondrian doesn’t correctly processes this one (results
      > differ from MSAS 2000).

      Assuming this query is typical of Excel 2007, let's make sure it works. Will, assigning to you.

      Julian

        Activity

        Hide
        PSS Viewer added a comment -

        I m running mondrian 3.0.4

        if you replace XL_Row_Dim_0 by it value VisualTotals(Distinct(Hierarchize(

        {Ascendants([Customers].[All Customers].[USA]), Descendants([Customers].[All Customers].[USA])}

        ))) it works

        the issue is because of a wrong interpretation of the WITH SET result I think

        Show
        PSS Viewer added a comment - I m running mondrian 3.0.4 if you replace XL_Row_Dim_0 by it value VisualTotals(Distinct(Hierarchize( {Ascendants([Customers].[All Customers].[USA]), Descendants([Customers].[All Customers].[USA])} ))) it works the issue is because of a wrong interpretation of the WITH SET result I think
        Hide
        Yap Sok Ann added a comment -

        PSS Viewer,

        I just tried that with mondrian 3.0.4, and it doesn't make any difference. You may want to check if anything was accidentally left out in the copy-paste process.

        I am pretty sure this issue is due to problem comparing a RolapCubeMember with a VisualTotalMember

        Show
        Yap Sok Ann added a comment - PSS Viewer, I just tried that with mondrian 3.0.4, and it doesn't make any difference. You may want to check if anything was accidentally left out in the copy-paste process. I am pretty sure this issue is due to problem comparing a RolapCubeMember with a VisualTotalMember
        Hide
        Calum Miller added a comment -

        As a temporary work-a-round, tick this option in the Pivot Table options "totals/filters->include filtered items in totals"
        Excel will then stop sending VisualTotals in the MDX

        Show
        Calum Miller added a comment - As a temporary work-a-round, tick this option in the Pivot Table options "totals/filters->include filtered items in totals" Excel will then stop sending VisualTotals in the MDX
        Hide
        Calum Miller added a comment -

        There is a bug in the IntersectFunDef function when the "contains" method is called on hashset, see this Java Bug for details:
        http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6579200

        Currently, the AbstractListCalc call does:
        if (right.contains(leftObject)) {
        if (all || !result.contains(leftObject))

        { result.add(resultObject); }

        Which can and does return the wrong result. Try replacing with,
        if (right.contains(leftObject)) {
        if (all || !result.contains(leftObject)) { result.add(resultObject); }

        }else{
        if(slowContains(right,leftObject)){
        if (all || !slowContains(result,leftObject))

        { result.add(resultObject); }


        }
        }

        Here is the code for slowContains, which could probably be written more efficiently
        private static boolean slowContains(Collection right, Object leftObject){
        boolean result=false;

        Iterator rightObjectIterator=right.iterator();
        String rightString;
        while(rightObjectIterator.hasNext()){
        rightString=rightObjectIterator.next().toString();
        if(rightString.compareTo(leftObject.toString())==0)

        { LOGGER.info("millersoft: found via slowContains:"+leftObject); result=true; break; }

        //if
        }//while

        return result;
        }

        This resolves the VisualTotals and the intersection issue....enjoy!

        Show
        Calum Miller added a comment - There is a bug in the IntersectFunDef function when the "contains" method is called on hashset, see this Java Bug for details: http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6579200 Currently, the AbstractListCalc call does: if (right.contains(leftObject)) { if (all || !result.contains(leftObject)) { result.add(resultObject); } Which can and does return the wrong result. Try replacing with, if (right.contains(leftObject)) { if (all || !result.contains(leftObject)) { result.add(resultObject); } }else{ if(slowContains(right,leftObject)){ if (all || !slowContains(result,leftObject)) { result.add(resultObject); } } } Here is the code for slowContains, which could probably be written more efficiently private static boolean slowContains(Collection right, Object leftObject){ boolean result=false; Iterator rightObjectIterator=right.iterator(); String rightString; while(rightObjectIterator.hasNext()){ rightString=rightObjectIterator.next().toString(); if(rightString.compareTo(leftObject.toString())==0) { LOGGER.info("millersoft: found via slowContains:"+leftObject); result=true; break; } //if }//while return result; } This resolves the VisualTotals and the intersection issue....enjoy!
        Hide
        Julian Hyde added a comment -

        Problem is not http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6579200 as Calum suggests. (Adding an object to a set then changing its hashcode is a bad programming practice, and if you do it, you deserve to get stung. The 'workaround' of scanning the entire set each time is unacceptably slow. I don't believe that we do it anywhere in mondrian code.)

        The issue was that the member created by the VisualTotals was not comparing equal to a RolapCubeMember (and vice versa). Fixed the equals and hashCode methods for these classes to solve the problem.

        Also in this change, I made the Intersect function more efficient by treating members and tuples as separate cases, and by using a set to detect whether duplicates have been added to the result.

        Fixed in change 13238 on main branch.

        Show
        Julian Hyde added a comment - Problem is not http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6579200 as Calum suggests. (Adding an object to a set then changing its hashcode is a bad programming practice, and if you do it, you deserve to get stung. The 'workaround' of scanning the entire set each time is unacceptably slow. I don't believe that we do it anywhere in mondrian code.) The issue was that the member created by the VisualTotals was not comparing equal to a RolapCubeMember (and vice versa). Fixed the equals and hashCode methods for these classes to solve the problem. Also in this change, I made the Intersect function more efficient by treating members and tuples as separate cases, and by using a set to detect whether duplicates have been added to the result. Fixed in change 13238 on main branch.

          People

          • Assignee:
            Julian Hyde
            Reporter:
            Julian Hyde
          • Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: