Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to Optimize mondrian when running mdx with multiple members(more than 20 )

  1. #1

    Default How to Optimize mondrian when running mdx with multiple members(more than 20 )

    Hi ,

    I have a problem while running a specific type of MDX queries. The issue is with the one's when i include a lot of members (above 20) per dimension.

    From the Mondrian sql logs i can see that all the member queries are queried and executed fast, but the one that takes time is while deriving the final query .That is mondrian takes time to arrive at the final sql query (excution is not taking much time as i have only 10000 records).
    Is there anything I can do about it ?
    Any thoughts will be much appreciated

  2. #2
    Join Date
    Mar 2007
    Posts
    142

    Default

    If by "final query" you mean the loading of the cells (in the logs, this shows up as Segment.load: [sql]") then I suggest you take that particular SQL query and run in your DB and profile it. You might realize that some indexes are missing, or that your DB iosn't powerful enough to do large GROUP BY operations. What DB are you using?
    Luc Boudreau
    aka. Luc le Magnifique
    aka. Monsieur Oui Oui

    Lead Engineer, Pentaho Corporation
    Web: http://devdonkey.blogspot.com
    Twitter: luclemagnifique
    IRC: Monsieur_Oui_Oui@freenode

  3. #3

    Default

    Thanks Luc ,

    Iam using Mysql 5.1.34-community edition. As I have mentioned the query execution is not taking much time ,i.e. if i run the segment load query in a sql editor it returns the result set fast . when I see the logs , i can see the three kind of queries being executed

    1. Query for measure
    2. Queries for dimension members
    3. Queries for cardinality of dimension
    4. Queries for segment load

    When I look into the logs the first query for each of this takes time to be derived from mondrian (eventhough the query execution time is less). So I doubt whether mondrian is taking time in deriving the optimum sql queries

    I was able to solve this issue last week by changing the mdx query structure from

    With member [PLAN].[AggregatePLANSelectedMembers] as 'Aggregate({([PLAN].[WarehouseAccountingPlan])})' member [QNAME].[AggregateQNAMESelectedMembers] as 'Aggregate({{([QNAME].[WarehouseAccountingPlan].[Warehouse-Non-PO-Invoice]),([QNAME].[WarehouseAccountingPlan].[Warehouse-Rush-Invoice]),([QNAME].[WarehouseAccountingPlan].[Warehouse-PO-Invoice]),([QNAME].[WarehouseAccountingPlan].[User-Approval])}})' member [TEAM].[AggregateTEAMSelectedMembers] as 'Aggregate({{([TEAM].[GeneralAccount-Indexing]),([TEAM].[GeneralAccount-PreIndexing]),([TEAM].[GeneralAccount-QC]),([TEAM].[PayRoll-Indexing-Team]),([TEAM].[PayRoll-PreIndexing-Team]),([TEAM].[Warehouse-Approver])}})' member [AGENT].[AggregateAGENTSelectedMembers] as 'Aggregate({{([AGENT].[aa]),([AGENT].[aaa]),([AGENT].[ab]),([AGENT].[abc]),([AGENT].[kishore]),([AGENT].[kp]),([AGENT].[kp2]),([AGENT].[kuruba]),([AGENT].[kuruba1]),([AGENT].[sheela]),([AGENT].[varadarajwh]),([AGENT].[varadaraj1]),([AGENT].[varadaraj]),([AGENT].[agents]),([AGENT].[ak])}})' member [STATUS].[AggregateSTATUSSelectedMembers] as 'Aggregate({[STATUS].[OPEN]})' member [TIME_A].[AggregateTIME_ASelectedMembers] as 'Aggregate({[TIME_A].[2012-12-02] : [TIME_A].[2013-01-02]})'member [INVOICE_GROUP].[AggregatedINVOICE_GROUPs] as 'Sum({([INVOICE_GROUP].[WarehouseAccountingPlan])})' member [SUPPLIER_NAME].[AggregatedSUPPLIER_NAMEs] as 'Sum({{([SUPPLIER_NAME].[WarehouseAccountingPlan].[685]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[702]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[703]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[704]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[705]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[706]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[707]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[711]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[712]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[724]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[723]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[730]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[737]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[738]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[739]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[744]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[745]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[746]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[747]),([SUPPLIER_NAME].[WarehouseAccountingPlan].[743])}})' select {([APPROVER].[kishore]),([APPROVER].[APS]),([APPROVER].[nirmal]),([APPROVER].[varadarajwh1]),([APPROVER].[varadarajwh]),([APPROVER].[sheela]),([APPROVER].[ap3]),([APPROVER].[apuser44]),([APPROVER].[swati123])} ON COLUMNS,{[APPROVER_AGING].[All APPROVER_AGINGs].Children} ON ROWS from [TRAIL] where Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({[APPROVAL_STATUS].[1]}, {[TIME_A].[AggregateTIME_ASelectedMembers]}) ,{[QNAME].[AggregateQNAMESelectedMembers]}),{[TEAM].[AggregateTEAMSelectedMembers]}),{[AGENT].[AggregateAGENTSelectedMembers]}),{[PLAN].[AggregatePLANSelectedMembers]}),{[STATUS].[AggregateSTATUSSelectedMembers]}),{[SUPPLIER_NAME].[AggregatedSUPPLIER_NAMEs]}),{[INVOICE_GROUP].[AggregatedINVOICE_GROUPs]})

    to


    with set [Base_Plans] as '{[PLAN].[WarehouseAccountingPlan]}'
    set [Base_QNAmes] as '{[QNAME].[WarehouseAccountingPlan].[Warehouse-Non-PO-Invoice], [QNAME].[WarehouseAccountingPlan].[Warehouse-Rush-Invoice], [QNAME].[WarehouseAccountingPlan].[Warehouse-PO-Invoice], [QNAME].[WarehouseAccountingPlan].[User-Approval]}'
    set [Base_Teams] as '{[TEAM].[GeneralAccount-Indexing], [TEAM].[GeneralAccount-PreIndexing], [TEAM].[GeneralAccount-QC], [TEAM].[PayRoll-Indexing-Team], [TEAM].[PayRoll-PreIndexing-Team], [TEAM].[Warehouse-Approver]}'
    set [Base_Agents] as '{[AGENT].[aa], [AGENT].[aaa], [AGENT].[ab], [AGENT].[abc], [AGENT].[kishore], [AGENT].[kp], [AGENT].[kp2], [AGENT].[kuruba], [AGENT].[kuruba1], [AGENT].[sheela], [AGENT].[varadarajwh], [AGENT].[varadaraj1], [AGENT].[varadaraj], [AGENT].[agents], [AGENT].[ak]}'
    set [Base_Approvers] as '{[APPROVER].[kishore], [APPROVER].[APS], [APPROVER].[nirmal], [APPROVER].[varadarajwh1], [APPROVER].[varadarajwh], [APPROVER].[sheela], [APPROVER].[ap3], [APPROVER].[apuser44], [APPROVER].[swati123]}'
    set [Base_Ageing] as '{[APPROVER_AGING].[All APPROVER_AGINGs].Children}'
    set [Base_time] as '{([TIME_A].[2012-12-02] : [TIME_A].[2013-01-02])}'
    set [Base_Invoice_group] as '{[INVOICE_GROUP].[WarehouseAccountingPlan]}'
    set [Base_Status] as '{[STATUS].[OPEN]}'
    set [Base_Suppliers] as '{[SUPPLIER_NAME].[WarehouseAccountingPlan].[685], [SUPPLIER_NAME].[WarehouseAccountingPlan].[702], [SUPPLIER_NAME].[WarehouseAccountingPlan].[703], [SUPPLIER_NAME].[WarehouseAccountingPlan].[704], [SUPPLIER_NAME].[WarehouseAccountingPlan].[705], [SUPPLIER_NAME].[WarehouseAccountingPlan].[706], [SUPPLIER_NAME].[WarehouseAccountingPlan].[707], [SUPPLIER_NAME].[WarehouseAccountingPlan].[711], [SUPPLIER_NAME].[WarehouseAccountingPlan].[712], [SUPPLIER_NAME].[WarehouseAccountingPlan].[724], [SUPPLIER_NAME].[WarehouseAccountingPlan].[723], [SUPPLIER_NAME].[WarehouseAccountingPlan].[730], [SUPPLIER_NAME].[WarehouseAccountingPlan].[737], [SUPPLIER_NAME].[WarehouseAccountingPlan].[738], [SUPPLIER_NAME].[WarehouseAccountingPlan].[739], [SUPPLIER_NAME].[WarehouseAccountingPlan].[744], [SUPPLIER_NAME].[WarehouseAccountingPlan].[745], [SUPPLIER_NAME].[WarehouseAccountingPlan].[746], [SUPPLIER_NAME].[WarehouseAccountingPlan].[747], [SUPPLIER_NAME].[WarehouseAccountingPlan].[743]}'
    set [Base_Approval_Status] as '{[APPROVAL_STATUS].[1]}'
    set [FULLSET] as 'NonEmptyCrossJoin([Base_Plans], NonEmptyCrossJoin([Base_Approvers], NonEmptyCrossJoin([Base_Ageing], NonEmptyCrossJoin([Base_QNAmes], NonEmptyCrossJoin([Base_Teams], NonEmptyCrossJoin([Base_Agents], NonEmptyCrossJoin([Base_time], NonEmptyCrossJoin([Base_Invoice_group], NonEmptyCrossJoin([Base_Suppliers], NonEmptyCrossJoin([Base_Status], [Base_Approval_Status]))))))))))'
    set [ROW_AXIS] as 'Generate([FULLSET], {[APPROVER].CurrentMember})'
    set [COLUMNS_AXIS] as 'Generate([FULLSET], {[APPROVER_AGING].CurrentMember})'
    set [NAT_Plans] as 'Generate([FULLSET],[PLAN].CurrentMember)'
    member [PLAN].[Slicer] as 'Aggregate([NAT_Plans] )'
    set [NAT_QNAme] as 'Generate([FULLSET],[QNAME].CurrentMember)'
    member [QNAME].[slicer] as 'Aggregate([NAT_QNAme])'
    set [NAT_Teams] as 'Generate([FULLSET],[TEAM].CurrentMember)'
    member [TEAM].[slicer] as 'Aggregate( [NAT_Teams] )'
    set [NAT_Agents] as 'Generate([FULLSET],[AGENT].CurrentMember)'
    member [AGENT].[slicer] as 'Aggregate( [NAT_Agents] )'

    set [NAT_time] as 'Generate([FULLSET],[TIME_A].CurrentMember)'
    member [TIME_A].[slicer] as 'Aggregate([NAT_time] )'

    set [NAT_Inv_gp] as 'Generate([FULLSET],[INVOICE_GROUP].CurrentMember)'
    member [INVOICE_GROUP].[slicer] as 'Aggregate([NAT_Inv_gp] )'

    set [NAT_supp] as 'Generate([FULLSET],[SUPPLIER_NAME].CurrentMember)'
    member [SUPPLIER_NAME].[slicer] as 'Aggregate([NAT_supp] )'

    set [NAT_stat] as 'Generate([FULLSET],[STATUS].CurrentMember)'
    member [STATUS].[slicer] as 'Aggregate([NAT_stat] )'

    set [NAT_APP_Stat] as 'Generate([FULLSET],[APPROVAL_STATUS].CurrentMember)'
    member [APPROVAL_STATUS].[slicer] as 'Aggregate([NAT_APP_Stat])'

    select [COLUMNS_AXIS] ON COLUMNS,
    [ROW_AXIS] ON ROWS
    from [TRAIL]
    where
    Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin([PLAN].[Slicer] ,[QNAME].[slicer] ),[TEAM].[slicer] ), [AGENT].[slicer] ), [TIME_A].[slicer] ), [INVOICE_GROUP].[slicer] ),[SUPPLIER_NAME].[slicer] ),[STATUS].[slicer]) ,[APPROVAL_STATUS].[slicer] )


    Iam not sure how this worked , as the sql queries generated by mondrian still remained same. Can you help me in understanding this?

    Thanks

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.