<div dir="ltr">Just curious...can you share your MySQL schema + data? I'm intrigued by your query and would like to run it myself. Perhaps some optimization on the MySQL end of things is possible as well...</div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, May 5, 2015 at 10:26 PM, Joshua Diamond <span dir="ltr"><<a href="mailto:diamond.joshh@gmail.com" target="_blank">diamond.joshh@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_quote"><br><div dir="ltr">Hi,<div><br></div><div>Thanks for letting me join this list. I joined because I'm having a problem that no amount of research has been able to solve.</div><div>Here's my specs:</div><div>Analyzer: Saiku 2.6 CE</div><div>mondrian-3.8.0.0-209.jar</div><div>database: MySql</div><div>Attached is my schema.</div><div>Attached are my mondrian properties.</div><div><br></div><div>my fact table is roughly 500,000 rows.</div><div>my largest dimension table is my CandidatesDim dimension is 50,000 rows.</div><div><br></div><div>When ever I try to run a query like the following, mondrian generates hundreds of sql querries and even if I set my query timeout property to something ridiculous, it never finishes.</div><div><div>SELECT</div><div>NON EMPTY Hierarchize(Union(CrossJoin({[OfficeDim.OfficeHier].[U.S. President]}, CrossJoin([PartyDim.PartyHier].[Party].Members, {[Measures].[VotesDisplay]})), CrossJoin({[OfficeDim.OfficeHier].[U.S. President]}, CrossJoin([PartyDim.PartyHier].[Party].Members, {[Measures].[Percentage]})))) ON COLUMNS,</div><div>NON EMPTY CrossJoin([GeoDim.GeoHier].[State].Members, [CandidatesDim.CandidatesHier].[Candidate].Members) ON ROWS</div><div>FROM [ElectionResults]</div><div>WHERE {[CycleDim.CycleHier].[General].[2012 General]}</div></div><div><br></div><div>The problem seems to happen whenever I have more than one dimension on an axis and more than one measure. </div><div><br></div><div>If I run this query it finishes in 3 seconds:</div><div><div>SELECT</div><div>NON EMPTY CrossJoin({[OfficeDim.OfficeHier].[U.S. President]}, CrossJoin([PartyDim.PartyHier].[Party].Members, {[Measures].[Percentage]})) ON COLUMNS,</div><div>NON EMPTY CrossJoin([GeoDim.GeoHier].[State].Members, [CandidatesDim.CandidatesHier].[Candidate].Members) ON ROWS</div><div>FROM [ElectionResults]</div><div>WHERE {[CycleDim.CycleHier].[General].[2012 General]}</div></div><div><br></div><div>I've tried almost every combination of mondrian properties. I've tried setting saiku.olap.convert.query = true. I've created aggregate tables that never got used. </div><div><br></div><div>What am I doing wrong? Is this just a mondrian 3.x issue that I'm forced to live with? I'm open to any thoughts you all might have. </div><div><br></div><div>Thank you so much for your help.</div><div><br></div><div>Josh</div><span class="HOEnZb"><font color="#888888"><span><font color="#888888"><div><br></div><div>-- <br><div><div dir="ltr"><div>Joshua Diamond<br>e. <a href="mailto:Diamond.JoshH@gmail.com" target="_blank">Diamond.JoshH@gmail.com</a><br></div>c.: <a href="tel:%28561%29676-3275" value="+15616763275" target="_blank">(561)676-3275</a><br><div><br></div></div></div></div></font></span></font></span></div></div><div><div dir="ltr"><div><br></div></div></div>
</div>
<br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature">Roland Bouman<br>blog: <a href="http://rpbouman.blogspot.com/" target="_blank">http://rpbouman.blogspot.com/</a><br>twitter: @rolandbouman<br>linkedin: <a href="http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro" target="_blank">http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro</a><br><br>Author of "Pentaho Solutions" (Wiley, ISBN: 978-0-470-48432-6 <a href="http://tinyurl.com/lvxa88" target="_blank">http://tinyurl.com/lvxa88</a>) and "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9 <a href="http://tinyurl.com/33r7a8m" target="_blank">http://tinyurl.com/33r7a8m</a>)</div>
</div>