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

Slicer size is quadratically inflating the cell requests

    Details

    • Customer Case:
    • QA Validation Status:
      Not Yet Validated

      Description

      I have a simple test below that has a n day date range (in mondrian food mart only 5 days in this month have data, but theoretically, lets say this number is n)

      Then what I end up seeing when walking the code is that the CellReader gets issued O(n^2) requests.
      This seems to be happening via the call to RolapTupleCalculation which adds a calculated member to the RolapEvaluator.... We end up making n calls to this calculation, and each call results in n cell requests.

      I do not fully understand what this calculation is doing, and it is essentially introduced in the lines 365 to 391 in RolapResult

      public void testQueryEvaluation() {
              final TestContext testContext = getRestrictedTestContext();
              testContext.assertQueryReturns(
                      "select {[Measures].[Unit Sales]} on COLUMNS, \n"
                      + "[Store].[USA].[CA].Children on ROWS \n"
                      + "from [Sales] \n"
                      + "where ([Time.Weekly].[All Time.Weeklys].[1997].[2].[1] : [Time.Weekly].[All " +
                              "Time.Weeklys].[1997].[2].[21])" ,
                      "Axis #0:\n"
                              + "{[Time].[1997].[Q1].[1]}\n"
                              + "{[Time].[1997].[Q1].[2]}\n"
                              + "Axis #1:\n"
                              + "{[Measures].[Unit Sales]}\n"
                              + "Axis #2:\n"
                              + "{[Store].[USA].[CA].[Los Angeles]}\n"
                              + "{[Store].[USA].[CA].[San Francisco]}\n"
                              + "Row #0: 4,490\n"
                              + "Row #1: 289\n");
          }
       

        Activity

        Hide
        Julian Hyde added a comment -
        Here are some numbers that show the quadratic increase:

                // Range Weeks # evals
                // ======================== ===== =======
                // [1997].[2] : [1997].[6] 5 119
                // [1997].[2] : [1997].[11] 10 439
                // [1997].[2] : [1997].[21] 20 1679
                // [1997].[2] : [1997].[41] 40 6559

        Note that each time the number of weeks doubles, the number of evals (calls to RolapEvaluator.evaluateCurrent()) approximately multiplies by 4.
        Show
        Julian Hyde added a comment - Here are some numbers that show the quadratic increase:         // Range Weeks # evals         // ======================== ===== =======         // [1997].[2] : [1997].[6] 5 119         // [1997].[2] : [1997].[11] 10 439         // [1997].[2] : [1997].[21] 20 1679         // [1997].[2] : [1997].[41] 40 6559 Note that each time the number of weeks doubles, the number of evals (calls to RolapEvaluator.evaluateCurrent()) approximately multiplies by 4.
        Hide
        Julian Hyde added a comment -
        I have a tentative fix. In RolapResult.java at line 1038 (in the method evaluateStripe) just after the line

                        ci.value = o;

        add the lines

                        if (true) {
                            break;
                        }

        For me, it doesn't break anything, and the number of calls to eval is reduced. Let me know if you see the same or different.
        Show
        Julian Hyde added a comment - I have a tentative fix. In RolapResult.java at line 1038 (in the method evaluateStripe) just after the line                 ci.value = o; add the lines                 if (true) {                     break;                 } For me, it doesn't break anything, and the number of calls to eval is reduced. Let me know if you see the same or different.
        Show
        Julian Hyde added a comment - Fixed in https://github.com/pentaho/mondrian/commit/0b795c96961b96e4e4a4fde66733bee20a2235a5 .
        Hide
        Pedro Alves added a comment -
        Julian, I believe this is related with http://jira.pentaho.com/browse/MONDRIAN-1226
        Show
        Pedro Alves added a comment - Julian, I believe this is related with http://jira.pentaho.com/browse/MONDRIAN-1226
        Hide
        Brandon Bruce added a comment -
        the related unit test is passing so i will close as validated by unit test
        Show
        Brandon Bruce added a comment - the related unit test is passing so i will close as validated by unit test
        Hide
        Maxime Caron added a comment -
        this might be the same as MONDRIAN-991
        Show
        Maxime Caron added a comment - this might be the same as MONDRIAN-991
        Hide
        Daniël Knippers added a comment -
        I am surprised this is closed, considering a relatively simple MDX query I issued to Mondrian over 18 hours (!!!) ago is still running on my machine as we speak.

        I was investigating some of the (huge) performance issues I experienced with Mondrian myself, and guided by this thread I was going to count the number of evaluations to RolapEvaluator.evaluateCurrent() to see if it was still an issue. As a straightforward 'counting' method I simply added a System.out.println("1"); to this method and with this output being written to a file I would just count the number of lines in the resulting file.

        Lo and behold, this text file containing nothing more than a sequence of "1\n" strings is, at this point, 2.6GB in size. That's a whole lot of evaluateCurrent() calls. I just ran a script to count it: 896881960 calls to evaluateCurrent - that's almost 900 million, and it's still running.

        I am using Mondrian 3.4.17, as I saw Mondrian 3.4 is your current 'stable' version I would assume the 3.4.17 build from 31 October would include the fix to this issue which Julian mentioned on 14 September already. Either the fix is not included, or the fix does not work, it seems :-)

        Regarding my setup; it consists of a small fact table (~3.8M rows) and 7 dimension tables. The query I am executing is the following, accessing only 2 of the dimensions (dimension A and Organization):

        SELECT NonEmptyCrossJoin(
          [DIM A.W].Children, NonEmptyCrossJoin(
            [DIM A.X].Children, NonEmptyCrossjoin(
              [DIM A.Y].Children, [DIM A.Z].Children
        ))) ON 0
        FROM Cube
        WHERE ({ < LIST OF 987 Organisation IDs like [DIM Organization.Orgid].[SomeNumber] > })

        The 'A' Dimension has 22384 members. Attribute W has 8 unique values, X has 23 unique values, Y has 92 unique values, and Z has 808 unique values. The default measure of the cube is not a calculated measure, just a normal one (summing a numeric attribute in the fact table).

        I know, and understand to some extent, that you prefer to see bugs reproduced in your FoodMart cube but unfortunately I do not have the time (I'm on my employer's time, not mine) to try that. In my personal experience, doing a number of crossjoins seems to make the performance a LOT worse, so perhaps doing some crossjoins and a large number of WHERE clauses can reproduce it on FoodMart as well.

        I am making this post not to get my specific case fixed - I have already moved away from Mondrian for my current project sadly - but just to point out that the issue does not seem fixed. At least not in all cases.
        Show
        Daniël Knippers added a comment - I am surprised this is closed, considering a relatively simple MDX query I issued to Mondrian over 18 hours (!!!) ago is still running on my machine as we speak. I was investigating some of the (huge) performance issues I experienced with Mondrian myself, and guided by this thread I was going to count the number of evaluations to RolapEvaluator.evaluateCurrent() to see if it was still an issue. As a straightforward 'counting' method I simply added a System.out.println("1"); to this method and with this output being written to a file I would just count the number of lines in the resulting file. Lo and behold, this text file containing nothing more than a sequence of "1\n" strings is, at this point, 2.6GB in size. That's a whole lot of evaluateCurrent() calls. I just ran a script to count it: 896881960 calls to evaluateCurrent - that's almost 900 million, and it's still running. I am using Mondrian 3.4.17, as I saw Mondrian 3.4 is your current 'stable' version I would assume the 3.4.17 build from 31 October would include the fix to this issue which Julian mentioned on 14 September already. Either the fix is not included, or the fix does not work, it seems :-) Regarding my setup; it consists of a small fact table (~3.8M rows) and 7 dimension tables. The query I am executing is the following, accessing only 2 of the dimensions (dimension A and Organization): SELECT NonEmptyCrossJoin(   [DIM A.W].Children, NonEmptyCrossJoin(     [DIM A.X].Children, NonEmptyCrossjoin(       [DIM A.Y].Children, [DIM A.Z].Children ))) ON 0 FROM Cube WHERE ({ < LIST OF 987 Organisation IDs like [DIM Organization.Orgid].[SomeNumber] > }) The 'A' Dimension has 22384 members. Attribute W has 8 unique values, X has 23 unique values, Y has 92 unique values, and Z has 808 unique values. The default measure of the cube is not a calculated measure, just a normal one (summing a numeric attribute in the fact table). I know, and understand to some extent, that you prefer to see bugs reproduced in your FoodMart cube but unfortunately I do not have the time (I'm on my employer's time, not mine) to try that. In my personal experience, doing a number of crossjoins seems to make the performance a LOT worse, so perhaps doing some crossjoins and a large number of WHERE clauses can reproduce it on FoodMart as well. I am making this post not to get my specific case fixed - I have already moved away from Mondrian for my current project sadly - but just to point out that the issue does not seem fixed. At least not in all cases.
        Hide
        Daniël Knippers added a comment -
        For completeness; I'm currently running the same query under Mondrian 3.5.2 (downloaded the sources from here: http://repository.pentaho.org/artifactory/pentaho/pentaho/mondrian/ just like 3.4.17 yesterday). I saw the 'Fix Version' at the top of this thread saying 3.5.0 so I thought the fix may not have been integrated in 3.4.x.

        However, in a matter of a few minutes the amount of calls to evaluateCurrent is up at 15 million already :-(
        Show
        Daniël Knippers added a comment - For completeness; I'm currently running the same query under Mondrian 3.5.2 (downloaded the sources from here: http://repository.pentaho.org/artifactory/pentaho/pentaho/mondrian/ just like 3.4.17 yesterday). I saw the 'Fix Version' at the top of this thread saying 3.5.0 so I thought the fix may not have been integrated in 3.4.x. However, in a matter of a few minutes the amount of calls to evaluateCurrent is up at 15 million already :-(
        Hide
        Daniël Knippers added a comment -
        The 3.5.2 query unfortunately got interrupted last night due to an automatic reboot of Windows (workplace PC was not configured to NOT reboot automatically), but after ~14 hours of execution it also accumulated over 800 million rows. It might have actually gotten worse than in 3.4.17, but my data is not accurate enough to conclude that with much certainty.
        Show
        Daniël Knippers added a comment - The 3.5.2 query unfortunately got interrupted last night due to an automatic reboot of Windows (workplace PC was not configured to NOT reboot automatically), but after ~14 hours of execution it also accumulated over 800 million rows. It might have actually gotten worse than in 3.4.17, but my data is not accurate enough to conclude that with much certainty.

          People

          • Assignee:
            Brandon Bruce
            Reporter:
            Luc Boudreau
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: