US and Worldwide: +1 (866) 660-7555
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: too many sql queries with mondrian 3.4.1

  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Default too many sql queries with mondrian 3.4.1

    Hello,

    I'm having a problem regarding the performance when i do an analysis on pentaho 4.5 (using mondrian 3.4.1). It seems to generate a lot of sql queries (about 60 queries for a report i find quite simple with only 3 dimensions used). The majority of these queries are using the fact table which is a behavior i'd like to avoid.

    Here is the schema:

    Code:
    <Schema name="UIC cubes BI">
        <Cube name="PERFORMANCE" visible="true" cache="true" enabled="true">
            <Table name="bireport_cube_perfomance" />
            <Dimension name="SourceMedia" foreignKey="media_id">
            <Hierarchy hasAll="true" allMemberName="toutes Media" primaryKey="media_id" >
            <Table name="dim_medias_labels" >
            </Table>
                <Level name="Medias" visible="true" column="media_id" nameColumn="media_label" uniqueMembers="true" caption="Media" >
                </Level>
            </Hierarchy>
        </Dimension>
        <Dimension name="SourceQueue" foreignKey="tk_queue_id" >
            <Hierarchy hasAll="true" allMemberName="toutes Files" primaryKey = "tk_queue_id">
            <Table name="dim_queues">
            </Table>
                <Level name="Files" visible="true" column="tk_queue_id" nameColumn="queue_name" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="Files">
                </Level>
            </Hierarchy>
        </Dimension>
            <Dimension name="Time" type="TimeDimension" foreignKey="date_stat_day" >
        <Hierarchy hasAll="true" allMemberName="All Years" primaryKey="date_stat_day" >
          <Table name="dim_dates"/>
          <Level name="Year" column="date_year" type="Numeric" uniqueMembers="true"
              levelType="TimeYears"/>
          <Level name="Month" column="date_month" uniqueMembers="false" type="Numeric"
              levelType="TimeMonths"/>
            <Level name="Day" column="date_day" nameColum="date_stat_day" uniqueMembers="false" type="Numeric"
              levelType="TimeDays"/>
        </Hierarchy>
      </Dimension>
        <Measure name="Duree de traitement (secondes)" column="eventduration" datatype="Integer" aggregator="avg" caption="Moyenne tps de traitement (secondes)" visible="true">  
            </Measure>
            <Measure name="Nombre de dossiers" column="thread_id" datatype="Integer" aggregator="count" caption="Nombre de dossiers" visible="true">  
            </Measure>
        </Cube>
    </Schema>
    I didn't change the mondrian.properties.

    The MDX request is as follow:

    Code:
    select NON EMPTY {Hierarchize({{[Time].[Year].Members}, {[Time].[Month].Members}, {[Time].[Day].Members}})} ON COLUMNS,
      NON EMPTY Crossjoin([SourceMedia].[Medias].Members, [SourceQueue].[Files].Members) ON ROWS
    from [PERFORMANCE]
    I did a first test using the original jar : mondrian-3.4.1.jar

    The number of sql queries generated is more than sixty queries, which is way too many queries for this simple report. (see the mondrian sql log attached) and the report is displayed in about 20 sedonds.

    Then, i downgraded the mondrian jar to an older mondrian-3.3.0.14703
    This time, the number of queries is only 12. (see the mondrian sql log attached) and the report is displayed in about 5 seconds.

    So, on this particular example, the behavior of the last mondrian engine (3.3.0) is more effective than the new one (3.4.1).
    Is there a specific modification i need to make on the schema xml or the mondrian.properties with this version of mondrian?

    Thanks in advance for any suggestions.
    Attached Files Attached Files

  2. #2

    Default

    I am also experiencing something like this where Mondrian creates way too many SQL queries, resulting in very poor performance. I have no solution for you, but I will also try out 3.3 again to see if it makes a difference.

  3. #3
    Join Date
    Feb 2007
    Posts
    196

    Default

    I had the same problem, especially with one mdx query that involves many crossjoins. The query took about 5 minutes. After downgrading mondrian to 3.3.0.14703, query takes about 15 seconds. Is it a known bug?

    regards, dejan

  4. #4
    Join Date
    Oct 2012
    Posts
    18

    Default

    Hi,
    Did you ever solve this problem?

    I'm seeing the same thing with the most updated mondrian version 3.5

    Thanks,
    Efi

  5. #5
    Join Date
    Feb 2007
    Posts
    196

    Default

    Nope. Using 3.3 :-(

  6. #6
    Join Date
    Oct 2012
    Posts
    18

    Default

    Amazing...

    I tried downgrading to 3.3 but now I'm getting "java.sql.SQLException: org.apache.commons.dbcp.DelegatingStatement is closed." exception which is caused by a bug fixed in 3.4.1

    Any idea what was changed between 3.3 and 3.4?

    Thanks,
    Efi

  7. #7
    Join Date
    Dec 2011
    Posts
    11

    Default

    Hello,
    I have the same issue, while upgrading Mondrian 3.3.0.14703 to 3.5.0. I tried to use 3.6.1 too, but the behavior is the same.
    Too many SQL statements are run. Shall I wait for Mondrian 4 ? or is there something to fix in the cube or mondrian.properties ?
    I opened http://jira.pentaho.com/browse/MONDRIAN-1831

  8. #8
    Join Date
    Feb 2007
    Posts
    196

    Default

    I only remember I had to play with some native parameters in mondrian.properties

  9. #9
    Join Date
    Dec 2011
    Posts
    11

    Default

    My current mondrian.properties is the following


    Code:
    # Allow the use of aggregates
    mondrian.rolap.aggregates.Use=true
    mondrian.rolap.aggregates.Read=true
    mondrian.native.topcount.enable=true
    mondrian.native.filter.enable=true
    
    mondrian.rolap.ignoreInvalidMembers=true
    mondrian.rolap.ignoreInvalidMembersDuringQuery=true
    
    mondrian.rolap.EnableRolapCubeMemberCache=true
    
    mondrian.result.limit=50000
    
    #i18n
    mondrian.rolap.localePropFile=locale.properties
    
    mondrian.jdbcDrivers=com.mysql.jdbc.Driver
    
    #hierarchy naming
    mondrian.olap.SsasCompatibleNaming=true
    
    #SQL formatting
    mondrian.rolap.generate.formatted.sql=true
    
    mondrian.native.nonempty.enable=true
    I will have a look to the native parameters.
    Thanks for the answer, it means that I'm not supposed to stay in 3.3.0 forever.
    Last edited by kanai; 12-11-2013 at 04:50 AM.

  10. #10
    Join Date
    Jan 2013
    Posts
    512

    Default

    Short answer, try setting mondrian.rolap.cellBatchSize to a very large number as a test and see whether that impacts the number of queries running. You'd still want to bring the batch size down to something reasonable for your workload, but that could at least indicate what's going on. Take a look at this post for a little more info on the property http://forums.pentaho.com/showthread...=cellBatchSize

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •