<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">I’ve read this several times and I’m just not following. I think you’re asking for feedback on a property setting that will control whether Mondrian uses the crossjoin optimizer or does something else. And it
sounds like you’re looking for a way to make sure the slicer gets used in the cell loading sql.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Could you maybe make a stab at describing the two query execution plans, and how this optimizer threshold comes into play?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">--jeff<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> mondrian-bounces@pentaho.org [mailto:mondrian-bounces@pentaho.org]
<b>On Behalf Of </b>Matt Campbell<br>
<b>Sent:</b> Friday, November 06, 2015 9:19 AM<br>
<b>To:</b> Mondrian developer mailing list <mondrian@pentaho.org><br>
<b>Subject:</b> [Mondrian] The perils of the crossjoin optimizer<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Mondrian’s crossjoin optimizer acts as a fall back to native crossjoin, applying an alternative optimization strategy in cases where native evaluation was not possible or disabled. It works by loading cell data for crossjoined tuples to
eliminate empty intersections. Loading these cells can be an added cost, but often that’s okay since the cells may have been needed anyway, if not by this query then potentially by similar queries.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">There are scenarios where the cost can be excessive, however. The MDX below loads detail rows with a grand total. In this case, the second crossjoin (of two “Total” calculated members) cannot be natively evaluated, so the crossjoin optimizer
gets a shot. Since the WHERE slicer is also a calculated member, however, this results in an unconstrained SQL query against the fact table. That a big expense to reduce a tuple set that’s already of size 1.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The default crossjoin optimizer threshold has a value of 0 tuples, meaning it always kicks in. I’m not sure what the ideal default is, but it seems to me that in most cases with tiny sets the risk of expensive SQL outweighs the benefit.
A setting somewhere in the 10-100 region would eliminate many SQL queries with total/subtotal MDX like the one below.<i><o:p></o:p></i></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thoughts?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">WITH</span><span style="font-size:9.5pt;font-family:Consolas"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">member</span><span style="font-size:9.5pt;font-family:Consolas"> Store.[Store Total]
<span style="color:blue">as</span> <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> <span style="color:#A31515">'Aggregate([Store].[Store State].[WA].children)'</span><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">member</span><span style="font-size:9.5pt;font-family:Consolas"> Product.[Product Total]
<span style="color:blue">as</span> <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> <span style="color:#A31515">'Aggregate({[Product].[All Products].[Drink], [Product].[All Products].[Food]})'</span><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">member</span><span style="font-size:9.5pt;font-family:Consolas"> [Education Level].[Education Filter]
<span style="color:blue">as</span> <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> <span style="color:#A31515">'Aggregate({[Education Level].[All Education Level].[Bachelors Degree],</span><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:#A31515"> [Education Level].[All Education Level].[Graduate Degree]})'</span><span style="font-size:9.5pt;font-family:Consolas"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">SELECT</span><span style="font-size:9.5pt;font-family:Consolas"> Measures.[Unit Sales]
<span style="color:blue">on</span> 0,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas">
<span style="color:blue">NON</span> <span style="color:blue">EMPTY</span> <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> <span style="color:blue">UNION</span>(<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas">
<span style="color:maroon">CrossJoin</span>([Store].[Store State].[WA].<span style="color:maroon">children</span>,
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> {[Product].[All Products].[Drink], [Product].[All Products].[Food]}),
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> <span style="color:maroon">CrossJoin</span>(Store.[Store Total], Product.[Product Total]))
<span style="color:blue">on</span> 1<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">FROM</span><span style="font-size:9.5pt;font-family:Consolas"> Sales<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">WHERE</span><span style="font-size:9.5pt;font-family:Consolas"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> [Education Level].[Education Filter]<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>