Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: SQL queries Mondrian submits per one MDX statement

  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Cool SQL queries Mondrian submits per one MDX statement

    Good afternoon,

    Recently while trying to research a few performance issues I discovered an unexpected.
    I was trying this query:

    select
    {[Measures].[amount], [Measures].[count]} ON columns,
    {[Patient IO].members * [Patient Category].[Patient Category].members * [Financial Category Original].members} ON rows
    from [transactions]

    To my surprise server SQL log shows that to return the result mondrian submits 10 queries, 4 out of which are against the fact table .
    (see queries below, fact table is highlighted bold). We are not using aggregation tables having Infobright as our back-end database server.

    Scanning the fact table 4 times means that performance of such MDX query is 4 times poorer than it could be.
    In fact the last SQL query against the fact table is the one that retrieves all necessary information for the MDX. In my opinion the first 3 queries against the fact table are not necessary.

    It is possible I am missing something however at this point I am not sure where to look.
    Does mondrian have configuration parameters controlling this kind of behavior? I looked through
    SQL Generation and Caching and didn't find anything helpful.
    Is it possible to change this behavior in the cube schema?

    Any help would be greatly appreciated.

    Thank you,
    Yuri.

    ==================================================
    select `patient_type_io`.`id` as `c0`, `patient_type_io`.`description` as `c1`
    from `patient_type_io` as `patient_type_io`
    group by `patient_type_io`.`id`, `patient_type_io`.`description` order by ISNULL(`patient_type_io`.`id`), `patient_type_io`.`id` ASC

    select `patient_category`.`id` as `c0`, `patient_category`.`description` as `c1`
    from `patient_category` as `patient_category`
    group by `patient_category`.`id`, `patient_category`.`description` order by ISNULL(`patient_category`.`id`), `patient_category`.`id` ASC

    select `financial_class_category`.`id` as `c0`, `financial_class_category`.`description` as `c1`
    from `financial_class_category` as `financial_class_category`
    group by `financial_class_category`.`id`, `financial_class_category`.`description` order by ISNULL(`financial_class_category`.`id`), `financial_class_category`.`id` ASC

    select count(distinct `patient_category`.`id`) as `c0`
    from `patient_category` as `patient_category`

    select `patient_category`.`id` as `c0`, sum(`transaction`.`transaction_amount`) as `m0`, count(`transaction`.`id`) as `m1`
    from `patient_category` as `patient_category`, `transaction` as `transaction`
    where `transaction`.`patient_category_id` = `patient_category`.`id`
    group by `patient_category`.`id`

    select count(distinct `financial_class_category`.`id`) as `c0`
    from `financial_class_category` as `financial_class_category`

    select `financial_class_category`.`id` as `c0`, `patient_category`.`id` as `c1`, sum(`transaction`.`transaction_amount`) as `m0`, count(`transaction`.`id`) as `m1`
    from `financial_class_category` as `financial_class_category`, `transaction` as `transaction`, `patient_category` as `patient_category`
    where `transaction`.`financial_class_category_original_id` = `financial_class_category`.`id` and `transaction`.`patient_category_id` = `patient_category`.`id`
    group by `financial_class_category`.`id`, `patient_category`.`id`

    select count(distinct `patient_type_io`.`id`) as `c0`
    from `patient_type_io` as `patient_type_io`

    select `patient_type_io`.`id` as `c0`, `patient_category`.`id` as `c1`, sum(`transaction`.`transaction_amount`) as `m0`, count(`transaction`.`id`) as `m1`
    from `patient_type_io` as `patient_type_io`, `transaction` as `transaction`, `patient_category` as `patient_category`
    where `transaction`.`patient_type_io_id` = `patient_type_io`.`id` and `transaction`.`patient_category_id` = `patient_category`.`id`
    group by `patient_type_io`.`id`, `patient_category`.`id`

    select `financial_class_category`.`id` as `c0`, `patient_type_io`.`id` as `c1`, `patient_category`.`id` as `c2`, sum(`transaction`.`transaction_amount`) as `m0`, count(`transaction`.`id`) as `m1`
    from `financial_class_category` as `financial_class_category`, `transaction` as `transaction`, `patient_type_io` as `patient_type_io`, `patient_category` as `patient_category`
    where `transaction`.`financial_class_category_original_id` = `financial_class_category`.`id` and `transaction`.`patient_type_io_id` = `patient_type_io`.`id` and `transaction`.`patient_category_id` = `patient_category`.`id`
    group by `financial_class_category`.`id`, `patient_type_io`.`id`, `patient_category`.`id`


    Yuri.

  2. #2

    Default

    My guess is that though you have given one MDX query, Mondrian actually has to create multiple queries to get the desired result. Also the queries generated are cached and so are the results so that subsequent queries make use of this data.

    Cheers,
    Deepak

  3. #3
    Join Date
    Jan 2007
    Posts
    25

    Default nothing to do with this?

    Hi, few days ago I wrote a question about how long Mondrian lasts to respond in an app with an old mondrian version vs the version included in pentaho. (mondrian 3.1.4.)

    Looking at generated queries in postgres, the reason for the diference in the time response is this topic: SQL queries Mondrian submits per one MDX statement


    Actually, is there anything we can do to get the old mondrian version behavior working on Pentaho?, and if the answer is no, waht is the reason for that behavior, why multiple SQLs? is it using aggegate tables the only solution?.

    Thanks for any response.

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.