Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Performance issues with calculated measures

  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Question Performance issues with calculated measures

    I see that with calculated measures Mondrian slows down a lot.

    I am requesting data for 10 days at a certain level. The query returning 250 rows takes more than 4 minutes against Mondrian Cache. Against the database this is fast (few milli seconds) but from Mondrian its slow ! In real time the result would have data in the order of thousands of rows slowing things further. However, this gets faster with lower number of calculated measures; without calculated measures this takes just a few seconds.


    If we were to compute the calculated values in DB via functions, is there a way to direct the cube to get these values from these functions for all the rows in the fact table than having Mondrian do the calculations? Does Mondrian calculate these values every time a calculated measure is requested or is it calculated at the first request and stored in cache? If it’s the later one, I don’t see why should it perform as slow as it does? Am I missing something?

    Another issue am having is irrespective of calculated measures.

    When the result set of the previous query is filtered for one particular date (at the same level), it goes to DB than Mondrian cache for the first request. In case of data filtering from previous queries, should it not get the filtered results from the Mondrian cache?

    Another issue that I see is, from JPivot, MondrianQueryAdapter takes 10 milliseconds for the monQuery.toString() operation but it takes 2 minutes before the SQL is sent to the database; Execution of SQL in the DB is fast, takes around 200 ms.

    All these together are slowing down the performance of my system, with every query taking more than 5 minutes to return results. Is there any special performance tuning or constraint on calculated members in the cube?

    Database is tuned according to the performance guide; Fact and dimension tables are indexed on measures and dimensions. As such, from the SQL trace it looks like DB is performing way faster than Mondrian. When the query is executed against the DB on the first request, results are returned in seconds, but Mondrian takes a while before the results are made available. We have allocated 2GB RAM and the result sets returned (as of now) are in the order of a few MB.

    Any suggestions/help on improving performance here would be greatly appreciated. We have been playing with Mondrian for more than a month now to analyze how it suits our business needs, and as we deal with a lot of calculated measures it is really necessary to know if this problem has a solution.

    Thanks,
    Deep

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Based on the performance you are seeing, it sounds like you are doing a huge number of calculations. Your suspicions about caching are correct: mondrian caches cells fetched from the database but does not, by default, cache the results of in-memory calculations.

    For a few functions calculations, mondrian uses a cache of calculation results. This cache is only for the duration of a statement; it is not shared between statements. One case where mondrian puts this caching in automatically is in the evaluation of the Rank(<set>, <measure>) function, to avoid evaluating and sorting the set many times.

    There's a function we use internally to enable this caching explicitly, by wrapping an expression in the $Cache(<expr>) function. The result will be the same as <expr>, but second and subsequent evaluations will be cached. Unfortunately I don't think you can call it directly from an MDX statement, because the parser can't handle the $ in the name. I'll look into making this public in the next release.

    Julian

  3. #3
    Join Date
    Aug 2007
    Posts
    10

    Default

    Thanks for your help. Yes, we are doing a huge number of calculations. Could you please tell us how can we make this caching enabled internally for all calculated members by default; may be that would help us improve the performance.

    One thing that we still see is :
    When the result set of the previous query is filtered for one particular date (at the same level), it goes to DB than Mondrian cache for the first request. In case of data filtering from previous queries, should it not get the filtered results from the Mondrian cache or is this the right behavior?


    Deep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.