<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:x="urn:schemas-microsoft-com:office:excel" 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=utf-8">
<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;}
/* 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;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.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 been experimenting with 2 flavors of “filter” MDX, and also turning native eval off with
<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">mondrian.native.crossjoin.enable=false<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">mondrian.native.nonempty.enable=false<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">For the queries I’m looking at, it looks to me like writing filter MDX with NonEmptyCrossJoin() in the WHERE improves some queries, and turning off native eval avoids the NonEmptyCrossJoin() resulting in an expensive
UNION sql from SqlTupleReader. So the UNION gets avoided by deferring constraints to the segment load.<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">Setting mondrian.rolap.aggregates.optimizePredicates=false also gives me results I’m happier with. With this property set to true, a lot of filters are more or less ignored.<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">I feel like I’m doing something wrong to turn off native and optimizePredicates…<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, August 19, 2016 11:38 AM<br>
<b>To:</b> Mondrian developer mailing list <mondrian@pentaho.org><br>
<b>Subject:</b> Re: [Mondrian] virtual cubes, non-joining dims, performance<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Hey Jeff-<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">The purpose of the UNION query with native evaluation of virtual cubes is to make sure that all of the tuples with NON EMPTY data from any of the relevant base cubes are included. So if a query
crossjoins Products and Stores from the [Warehouse and Sales] cube, native eval needs to query both the sales and warehouse fact tables in order to get the full set of NON EMPTY tuples. It does that with queries to each fact table UNION’d together (that logic
has been in place for years).<o:p></o:p></span></p>
</div>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Prior to the changes for 1599/2280, SqlTupleReader would retrieve those base cubes relevant to the measures in the query, and then pare the list of cubes down to only those that join to all the
dimensions in the crossjoin. If any measures in the query might change context, however, that approach could give incorrect results. There are some examples in the 2 jira cases.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">The changes for 1599/2280 introduced grouping of native tuple queries, so we now retrieve tuples grouped by common joining base cubes. STR only does this if one or more measures are determined
to shift context (with ValidMeasure or otherwise). But this does mean that we’re now more likely to execute UNION queries, since we’re evaluating in groups that share common cubes.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">I’m definitely interested in hearing thoughts on improving performance in this scenario. The trick is assuring correctness, which requires knowing which tuples have non-empty data across all relevant
fact tables, without executing more expensive SQL than necessary. <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">-matt<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="color:black">From: </span></b><span style="color:black"><<a href="mailto:mondrian-bounces@pentaho.org">mondrian-bounces@pentaho.org</a>> on behalf of "<a href="mailto:jeff.s.wright@truvenhealth.com">jeff.s.wright@truvenhealth.com</a>"
<<a href="mailto:jeff.s.wright@truvenhealth.com">jeff.s.wright@truvenhealth.com</a>><br>
<b>Reply-To: </b>Mondrian list <<a href="mailto:mondrian@pentaho.org">mondrian@pentaho.org</a>><br>
<b>Date: </b>Friday, August 19, 2016 at 10:26 AM<br>
<b>To: </b>Mondrian list <<a href="mailto:mondrian@pentaho.org">mondrian@pentaho.org</a>><br>
<b>Subject: </b>[Mondrian] virtual cubes, non-joining dims, performance</span><span style="font-size:12.0pt;color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<div>
<p class="MsoNormal"><span style="color:black">I think this is primarily to Matt, but sending to mailing list in case there’s other interest or input…<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"> <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">We’ve been testing some new-ish code in an effort to get correct behavior of non-joining dimensions, and there seems to be a somewhat steep performance cost to the fix. What we’re seeing is that recent versions
of SqlTupleReader generate an expensive UNION query across the fact tables in a virtual cube.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"> <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">I haven’t carefully sorted through the code our team has tested, but looking at the revision history on github, I suspect we’re testing with the checkin for issue #1, but not issue #2:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"> <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">#1 - <a href="http://jira.pentaho.com/browse/MONDRIAN-1599">
http://jira.pentaho.com/browse/MONDRIAN-1599</a> - Some queries against Virtual cubes can give wrong results when dimensions don't fully join<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">#2 - <a href="http://jira.pentaho.com/browse/MONDRIAN-2280">
http://jira.pentaho.com/browse/MONDRIAN-2280</a> - ValidMeasure not taken into account when Analyzer uses NonEmptyCrossjoin as it returns no data<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"> <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">Is the expensive UNION query something you’ve seen as well?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"> <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">--jeff<o:p></o:p></span></p>
</div>
</div>
</div>
</body>
</html>