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

Query generated by Excel 2007 gives incorrect results

    Details

      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: