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

Julian Hyde jhyde at pentaho.com
Mon Aug 24 13:29:38 EDT 2009


Looks like a good test case. Please log a bug.
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Peter Tran
Sent: Monday, August 24, 2009 8:55 AM
To: Mondrian developer mailing list
Subject: FW: [Mondrian] Performance issue with large number of measures
inMondrian 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]" />

 

2.	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]

 

3.	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     

        



 

 

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.jav
a: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.jav
a: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.jav
a: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.jav
a: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.jav
a: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(NonEmptyCrossJoinFu
nDef.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(RolapRe
sult.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/217ee33b/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/gif
Size: 7160 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20090824/217ee33b/attachment.gif 


More information about the Mondrian mailing list