[Mondrian] Performance issue with large number of measures in Mondrian 3.0.4

Huei-Ju Chen hchen at prospricing.com
Mon Aug 24 12:08:23 EDT 2009


Correction of the query in Step 3:


WITH
SET [#DataSet#] as 'NonEmptyCrossjoin(
{ [Product].[Food]},
{Descendants([Store].[USA], 1)}
)'

SELECT {[Measures].[M2]} on columns, NON EMPTY Hierarchize({[#DataSet#]}) on rows FROM [Sales]

________________________________
From: Huei-Ju Chen
Sent: Monday, August 24, 2009 11:02 AM
To: 'Mondrian developer mailing list'
Subject: RE: [Mondrian] Performance issue with large number of measures in Mondrian 3.0.4

Yes, I could reproduce this issue in 3.1.2.

Thanks,
Daphne

________________________________
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Peter Tran
Sent: Monday, August 24, 2009 10:55 AM
To: Mondrian developer mailing list
Subject: FW: [Mondrian] Performance issue with large number of measures in Mondrian 3.0.4

Forwarding to the Mondrian Developer Mailing List.

Thanks Daphne!!!  I believe we've also saw that this was still an issue in 3.1.2 right?

Thanks,
-Peter

From: Huei-Ju Chen
Sent: Monday, August 24, 2009 10:48 AM
To: jhyde at pentaho.com
Cc: Peter Tran
Subject: RE: [Mondrian] Performance issue with large number of measures inMondrian 3.0.4

Hi Julian,

Here is how I reproduced this issue in foodmart:

 1.  add the following calculated measures in the Sales cube. M0 is the sum of 7 base measures. M1 is the sum of M0 and 17 base measures. M2 is the sum of M1 and 17 base measures.

  <CalculatedMember
      name="M0"
        dimension="Measures"
        formula="[Measures].[Unit Sales] + [Measures].[Store Cost] + [Measures].[Store Sales] + [Measures].[Customer Count] + [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]" />
    <CalculatedMember
      name="M1"
        dimension="Measures"
        formula="[Measures].[M0] + [Measures].[Unit Sales] + [Measures].[Store Cost] + [Measures].[Store Sales] + [Measures].[Customer Count] + [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]" />
      <CalculatedMember
      name="M2"
        dimension="Measures"
        formula="[Measures].[M1] + [Measures].[Unit Sales] + [Measures].[Store Cost] + [Measures].[Store Sales] + [Measures].[Customer Count] + [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]+ [Measures].[Sales Count]" />


 1.  Select M1 in the query. It took 3.5 seconds to return.
      WITH
SET [#DataSet#] as 'NonEmptyCrossjoin(
{ [Product].[Food]},
{Descendants([Store].[USA], 1)}
)'

SELECT {[Measures].[M1]} on columns, NON EMPTY Hierarchize({[#DataSet#]}) on rows FROM [Sales]


 1.  Select M2. It has been running for 5 minutes and hasn't returned.
     WITH
SET [#DataSet#] as 'NonEmptyCrossjoin(
{ [Product].[Food]},
{Descendants([Store].[USA], 1)}
)'

SELECT {[Measures].[M1]} on columns, NON EMPTY Hierarchize({[#DataSet#]}) on rows FROM [Sales]
Thank you,
Daphne


________________________________
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Julian Hyde
Sent: Friday, August 21, 2009 3:52 AM
To: 'Mondrian developer mailing list'
Subject: RE: [Mondrian] Performance issue with large number of measures inMondrian 3.0.4

I'd not noticed it. It looks exponential -- it doubles each time you add a measure -- so it's definitely a concern. Can you construct a test case on foodmart with enough measures to have truly abyssmal performance, then log a bug. Looks like it's in some of the special logic in crossjoin to 'optimize' the non-empty case.

________________________________
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Peter Tran
Sent: Thursday, August 20, 2009 10:34 AM
To: Mondrian developer mailing list
Subject: [Mondrian] Performance issue with large number of measures inMondrian 3.0.4
Hi,

Has anyone noticed a performance issue with Mondrian 3.0.4 with regards to large number of measures in a calculated measure?

We ran some test and got the following results.  We added one base measure at a time to the calculated measure for the test.

# measures     elapsed time
    9             19.078
   10             34.307
   11             79.096
   12            146.222
   13            304.365

[cid:image001.gif at 01CA24AB.327D7210]


It looks like it's working inside some recursive code.  Any help or recommendation would be greatly appreciated.

Stack trace:
java.util.HashMap.put(HashMap.java:372)
java.util.HashSet.add(HashSet.java:200)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.process(CrossJoinFunDef.java:2089)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2070)
mondrian.mdx.MemberExpr.accept(MemberExpr.java:73)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.process(CrossJoinFunDef.java:2082)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2070)
mondrian.mdx.MemberExpr.accept(MemberExpr.java:73)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.process(CrossJoinFunDef.java:2082)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2070)
mondrian.mdx.MemberExpr.accept(MemberExpr.java:73)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.process(CrossJoinFunDef.java:2082)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2070)
mondrian.mdx.MemberExpr.accept(MemberExpr.java:73)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.process(CrossJoinFunDef.java:2082)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2070)
mondrian.mdx.MemberExpr.accept(MemberExpr.java:73)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:157)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef$MeasureVisitor.visit(CrossJoinFunDef.java:2048)
mondrian.mdx.ResolvedFunCall.accept(ResolvedFunCall.java:154)
mondrian.olap.fun.CrossJoinFunDef.nonEmptyList(CrossJoinFunDef.java:2196)
mondrian.olap.fun.NonEmptyCrossJoinFunDef$1.evaluateList(NonEmptyCrossJoinFunDef.java:68)
mondrian.calc.impl.AbstractListCalc.evaluate(AbstractListCalc.java:67)
mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:794)
mondrian.rolap.RolapResult.access$100(RolapResult.java:46)
mondrian.rolap.RolapResult$RolapResultEvaluatorRoot.evaluateNamedSet(RolapResult.java:1194)
mondrian.rolap.RolapEvaluator.evaluateNamedSet(RolapEvaluator.java:884)
mondrian.mdx.NamedSetExpr$1.evaluateList(NamedSetExpr.java:78)
mondrian.olap.fun.SetFunDef$ListSetCalc$2.evaluateVoid(SetFunDef.java:149)
mondrian.olap.fun.SetFunDef$ListSetCalc.evaluateList(SetFunDef.java:204)
mondrian.olap.fun.HierarchizeFunDef$1.evaluateList(HierarchizeFunDef.java:48)
mondrian.calc.impl.AbstractListCalc.evaluate(AbstractListCalc.java:67)
mondrian.rolap.RolapResult.executeAxis(RolapResult.java:694)
mondrian.rolap.RolapResult.evalLoad(RolapResult.java:557)
mondrian.rolap.RolapResult.loadMembers(RolapResult.java:532)
mondrian.rolap.RolapResult.<init>(RolapResult.java:254)
mondrian.rolap.RolapConnection.execute(RolapConnection.java:597)
mondrian.tui.CmdRunner.runQuery(CmdRunner.java:566)
mondrian.tui.CmdRunner.execute(CmdRunner.java:543)
mondrian.tui.CmdRunner.executeMdxCmd(CmdRunner.java:2243)
mondrian.tui.CmdRunner.commandLoop(CmdRunner.java:887)
mondrian.tui.CmdRunner.commandLoop(CmdRunner.java:750)
mondrian.tui.CmdRunner.main(CmdRunner.java:2397)

Thanks!
-Peter

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090824/8036cbd7/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.gif
Type: image/gif
Size: 7160 bytes
Desc: image001.gif
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20090824/8036cbd7/attachment.gif 


More information about the Mondrian mailing list