<div dir="ltr">So, i think this reproduces the problem. The query is as follows:<div><br></div><div><div>with</div><div>member [Measures].[Total] as </div><div>aggregate(</div><div>    CrossJoin(</div><div>        [product].currentmember,</div><div>        Crossjoin(</div><div>            [gender].[all gender],</div><div>            Crossjoin(</div><div>                [filter],</div><div>                [Store Type].[all store types]</div><div>            )</div><div>        )</div><div>    ),</div><div>    measures.[unit sales]</div><div>)</div><div>set [filter] as</div><div>cache(except(</div><div>    [Store].[Store Name].Members, </div><div>    {</div><div>        [Store].[Canada].[BC].[Vancouver].[Store 19], </div><div>        [Store].[Canada].[BC].[Victoria].[Store 20],</div><div>        [Store].[Mexico].[DF].[Mexico City].[Store 9],</div><div>        [Store].[Mexico].[DF].[San Andres].[Store 21],</div><div>        [Store].[Mexico].[Guerrero].[Acapulco].[Store 1]</div><div>    }</div><div>))</div><div>select </div><div>NON EMPTY Crossjoin(</div><div>    [product].[product name].members,</div><div>    Crossjoin(</div><div>        [Gender].[Gender].members,</div><div>        Crossjoin(</div><div>            [filter],</div><div>            [Store Type].[Store Type].members</div><div>        )</div><div>    )</div><div>) on 1, </div><div>{ measures.total } on 0 </div><div>from [Sales]</div></div><div><br></div><div><br></div><div>This query fails to return any result and we did wait for quite a few minutes.<br></div><div><br></div><div>Then when we changed the [filter] set to:</div><div><br></div><div><div>set [filter] as</div><div>cache({</div><div>    [Store].[Mexico].[Jalisco].[Guadalajara].[Store 5],</div><div>    [Store].[Mexico].[Veracruz].[Orizaba].[Store 10],</div><div>    [Store].[Mexico].[Yucatan].[Merida].[Store 8],</div><div>    [Store].[Mexico].[Zacatecas].[Camacho].[Store 4],</div><div>    [Store].[Mexico].[Zacatecas].[Hidalgo].[Store 12],</div><div>    [Store].[Mexico].[Zacatecas].[Hidalgo].[Store 18],</div><div>    [Store].[USA].[CA].[Alameda].[HQ],</div><div>    [Store].[USA].[CA].[Beverly Hills].[Store 6],</div><div>    [Store].[USA].[CA].[Los Angeles].[Store 7],</div><div>    [Store].[USA].[CA].[San Diego].[Store 24],</div><div>    [Store].[USA].[CA].[San Francisco].[Store 14],</div><div>    [Store].[USA].[OR].[Portland].[Store 11],</div><div>    [Store].[USA].[OR].[Salem].[Store 13],</div><div>    [Store].[USA].[WA].[Bellingham].[Store 2],</div><div>    [Store].[USA].[WA].[Bremerton].[Store 3],</div><div>    [Store].[USA].[WA].[Seattle].[Store 15],</div><div>    [Store].[USA].[WA].[Spokane].[Store 16],</div><div>    [Store].[USA].[WA].[Tacoma].[Store 17],</div><div>    [Store].[USA].[WA].[Walla Walla].[Store 22],</div><div>    [Store].[USA].[WA].[Yakima].[Store 23]</div><div>})</div></div><div><br></div><div>this still didn&#39;t return anything. So we went with:</div><div><br></div><div><div style="font-size:12.8000001907349px">set [filter] as</div><div style="font-size:12.8000001907349px"><span style="white-space:pre-wrap">        </span>[Store].[store name].members</div></div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">And this returned a result after a while, and since the members were cached, the previous versions of the query returned also if executed again, but still taking a lot of time to finish.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px"><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jun 17, 2015 at 3:35 PM, Julian Hyde <span dir="ltr">&lt;<a href="mailto:julianhyde@gmail.com" target="_blank">julianhyde@gmail.com</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word">Hilario,<div><br></div><div>Can you generate a few stack traces (by sending &quot;kill -QUIT &lt;process id&gt;”) while it is running, then send the log? If we are hitting a performance problem like a quadratic for-loop or a bad hash key, then it should show up on the traces.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Julian</div></font></span><div><div class="h5"><div><br><div><blockquote type="cite"><div>On Jun 17, 2015, at 5:27 AM, Matt Campbell &lt;<a href="mailto:mcampbell@pentaho.com" target="_blank">mcampbell@pentaho.com</a>&gt; wrote:</div><br><div><div style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">The Cache function includes evaluation context in its key, and looking back at the query I was thinking the Except() might be evaluated at each projected intersection.  But actually- since it’s in a named set it should be evaluated once in the context of the slicer.<u></u><u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Hilario- if you could reproduce the poor performance with Foodmart that could help us diagnose.<u></u><u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"><span> </span><a href="mailto:mondrian-bounces@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">mondrian-bounces@pentaho.org</a><span> </span>[<a href="mailto:mondrian-bounces@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">mailto:mondrian-bounces@pentaho.org</a>]<b>On Behalf Of<span> </span></b>Luc Boudreau<br><b>Sent:</b><span> </span>Tuesday, June 16, 2015 4:30 PM<br><b>To:</b><span> </span>Mondrian developer mailing list<br><b>Subject:</b><span> </span>Re: [Mondrian] Except function performance<u></u><u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">The full docs about Cache() is here.<u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><a href="http://mondrian.pentaho.com/documentation/performance.php#Optimizing_Calculations_with_the_Expression_Cache" style="color:purple;text-decoration:underline" target="_blank">http://mondrian.pentaho.com/documentation/performance.php#Optimizing_Calculations_with_the_Expression_Cache</a><u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Not sure why it wouldn&#39;t work. It&#39;s a trivial function implementation.<u></u><u></u></div></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">On Tue, Jun 16, 2015 at 4:08 PM, Hilario Fernandes &lt;<a href="mailto:hilario.fernandes@cortex-intelligence.com" style="color:purple;text-decoration:underline" target="_blank">hilario.fernandes@cortex-intelligence.com</a>&gt; wrote:<u></u><u></u></div><blockquote style="border-style:none none none solid;border-left-color:rgb(204,204,204);border-left-width:1pt;padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Unfortunately using the Cache() around the except in the <span style="font-size:9.5pt">FILTERED_D set seems no make no difference... Any reason why it shouldn&#39;t?</span><u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div></div><div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">On Tue, Jun 16, 2015 at 1:47 PM, Brandon Jackson &lt;<a href="mailto:usbrandon@gmail.com" style="color:purple;text-decoration:underline" target="_blank">usbrandon@gmail.com</a>&gt; wrote:<u></u><u></u></div><blockquote style="border-style:none none none solid;border-left-color:rgb(204,204,204);border-left-width:1pt;padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Luc!  Thanks for that golden nugget.<br><br>Sent from my iPhone<u></u><u></u></div></div><div><div><div><p class="MsoNormal" style="margin:0in 0in 12pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br>On Jun 16, 2015, at 10:00 AM, Luc Boudreau &lt;<a href="mailto:lucboudreau@gmail.com" style="color:purple;text-decoration:underline" target="_blank">lucboudreau@gmail.com</a>&gt; wrote:<u></u><u></u></p></div><blockquote style="margin-top:5pt;margin-bottom:5pt"><div><p style="margin-right:0in;margin-left:0in;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">If you know that a given mdx expression will always return the same value, you can wrap it in a Cache() function.<u></u><u></u></p><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">On Jun 16, 2015 10:55, &quot;Hilario Fernandes&quot; &lt;<a href="mailto:hilario.fernandes@cortex-intelligence.com" style="color:purple;text-decoration:underline" target="_blank">hilario.fernandes@cortex-intelligence.com</a>&gt; wrote:<u></u><u></u></div><blockquote style="border-style:none none none solid;border-left-color:rgb(204,204,204);border-left-width:1pt;padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">The set returned by the Except() is small, 3 members. If we replace the except with those 3 members, the performance is closer to expected.<u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Is there some way we can avoid the except from being recalculated on each iteration?<u></u><u></u></div></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">On Mon, Jun 15, 2015 at 2:44 PM, Matt Campbell &lt;<a href="mailto:mcampbell@pentaho.com" style="color:purple;text-decoration:underline" target="_blank">mcampbell@pentaho.com</a>&gt; wrote:<u></u><u></u></div><blockquote style="border-style:none none none solid;border-left-color:rgb(204,204,204);border-left-width:1pt;padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">NonEmptyCrossJoin and Crossjoin on a NON EMPTY axis are mapped to the same native evaluator, so there should be no difference.</span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">How big is the set returned by Except?  That Aggregate() needs to be evaluated for each tuple on the rows, which can be time consuming for larger sets.</span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Just out of curiosity, if you replace the named set containing the Except with the same enumerated members the except() would return, do you see much difference?</span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> </span><a href="mailto:mondrian-bounces@pentaho.org" style="color:purple;text-decoration:underline" target="_blank"><span style="font-size:11pt;font-family:Calibri,sans-serif">mondrian-bounces@pentaho.org</span></a><span style="font-size:11pt;font-family:Calibri,sans-serif"><span> </span>[mailto:</span><a href="mailto:mondrian-bounces@pentaho.org" style="color:purple;text-decoration:underline" target="_blank"><span style="font-size:11pt;font-family:Calibri,sans-serif">mondrian-bounces@pentaho.org</span></a><span style="font-size:11pt;font-family:Calibri,sans-serif">]<span> </span><b>On Behalf Of<span> </span></b>Hilario Fernandes<br><b>Sent:</b><span> </span>Monday, June 15, 2015 1:36 PM<br><b>To:</b><span> </span>Mondrian developer mailing list</span><u></u><u></u></div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br><b>Subject:</b><span> </span>Re: [Mondrian] Except function performance<u></u><u></u></div></div></div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Thank you for your reply Matt! <u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">We are using the CrossJoin() function in our mdx, i just used the &#39;*&#39; operator for readability. Will NonEmptyCrossjoin() be more efficient than CrossJoin() considering that we are using NonEmpty on the axis?<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Unfortunately we already have the ExpandNonNative property set to true.<u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">On Mon, Jun 15, 2015 at 2:26 PM, Matt Campbell &lt;<a href="mailto:mcampbell@pentaho.com" style="color:purple;text-decoration:underline" target="_blank">mcampbell@pentaho.com</a>&gt; wrote:<u></u><u></u></div><blockquote style="border-style:none none none solid;border-left-color:rgb(204,204,204);border-left-width:1pt;padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Except() is not handled natively.  You can try setting “mondrian.native.ExpandNonNative=true”, which will evaluate the except non-natively and attempt to include the resulting tuples in a native context.  That property is enabled by default in Pentaho biserver, but false by default in Mondrian.</span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Another thing that caught my eye- the ‘*’ crossjoin operator in your query is not mapped to native crossjoin (MONDRIAN-2284).  So that could also be a factor.  Try replacing the crossjoins with nested NonEmptyCrossJoin().</span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span><u></u><u></u></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> </span><a href="mailto:mondrian-bounces@pentaho.org" style="color:purple;text-decoration:underline" target="_blank"><span style="font-size:11pt;font-family:Calibri,sans-serif">mondrian-bounces@pentaho.org</span></a><span style="font-size:11pt;font-family:Calibri,sans-serif"><span> </span>[mailto:</span><a href="mailto:mondrian-bounces@pentaho.org" style="color:purple;text-decoration:underline" target="_blank"><span style="font-size:11pt;font-family:Calibri,sans-serif">mondrian-bounces@pentaho.org</span></a><span style="font-size:11pt;font-family:Calibri,sans-serif">]<span> </span><b>On Behalf Of<span> </span></b>Hilario Fernandes<br><b>Sent:</b><span> </span>Monday, June 15, 2015 12:41 PM<br><b>To:</b><span> </span>Mondrian mailing list<br><b>Subject:</b><span> </span>Re: [Mondrian] Except function performance</span><u></u><u></u></div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Hello!<u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">I&#39;ve sent this question a while ago without any replay, bringing it up again.<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">As I could see, using something like except in that crossjoin, it cannot use a native evaluation and that slows things really a lot. This is as far as i&#39;ve gotten, maybe someone that knows how things work more in dept can throw some comments in?<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Thanks<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">On Wed, Mar 4, 2015 at 2:15 PM, Hilario Fernandes &lt;<a href="mailto:hilario.fernandes@cortex-intelligence.com" style="color:purple;text-decoration:underline" target="_blank">hilario.fernandes@cortex-intelligence.com</a>&gt; wrote:<u></u><u></u></div><blockquote style="border-style:none none none solid;border-left-color:rgb(204,204,204);border-left-width:1pt;padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Hi!<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">I&#39;m having somewhat of a performance problem when trying execute the following query:<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">WITH<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">MEMBER [Measures].[TotalA] AS<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">IIF((NOT IsEmpty([Measures].[M1])), Aggregate({[A].CurrentMember} * {[B].[Total B]} * {[C].[Total C]} * {[FILTERED_D]} * {[E].[Total E]}, [Measures].[M1]), NULL)<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">SET [FILTERED_D] AS<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Except({[D].[D].Members},{[D].[D].[member1], [D].[D].[member2]})})<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">SELECT<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">NON EMPTY ({[Measures].[M1], [Measures].[TotalA]}) ON COLUMNS,<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">NON EMPTY [A].[A].Members * [B].[B].Members * [C].[C].Members * [FILTERED_D] * [E].[E].Members ON ROWS<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">FROM [C]<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">The point of the TotalA measure is to return the total of the measure for every A member, repeating it for the rest of the other dimension members on the axis. The problem is this measure must respect the filters in that axis, so the except is placed both on the rows and used on the aggregate.<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Thing is, when the except is used in the TotalA calculation its makes the evaluation a lot slower. This only happens with except, if something like Filter(members, measure &gt;100) is used then there is no major difference.<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Can anyone shed some light on why this happens? And maybe some ideas to work around it with some other way to achieve the same result.<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Thanks<u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="color:rgb(136,136,136)"> </span><u></u><u></u></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="color:rgb(136,136,136)"> </span><u></u><u></u></div></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="color:rgb(136,136,136)">--</span><u></u><u></u></div><div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><span style="color:rgb(136,136,136)">Hilario Fernandes</span><u></u><u></u></div></div></div></div></div></blockquote></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br><br clear="all"><u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">--<u></u><u></u></div><div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Hilario Fernandes<u></u><u></u></div></div></div></div></div></div></div></div></div><p class="MsoNormal" style="margin:0in 0in 12pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br>_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color:purple;text-decoration:underline" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><u></u><u></u></p></blockquote></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br><br clear="all"><u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"> <u></u><u></u></div></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">--<u></u><u></u></div><div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Hilario Fernandes<u></u><u></u></div></div></div></div></div></div></div></div></div><p class="MsoNormal" style="margin:0in 0in 12pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br>_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color:purple;text-decoration:underline" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><u></u><u></u></p></blockquote></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br><br clear="all"><u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">--<span> </span><u></u><u></u></div><div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Hilario Fernandes<u></u><u></u></div></div></div></div></div><p class="MsoNormal" style="margin:0in 0in 12pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br>_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color:purple;text-decoration:underline" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><u></u><u></u></p></blockquote></div></div></blockquote><blockquote style="margin-top:5pt;margin-bottom:5pt"><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color:purple;text-decoration:underline" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><u></u><u></u></div></div></blockquote></div></div></div><p class="MsoNormal" style="margin:0in 0in 12pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br>_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color:purple;text-decoration:underline" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><u></u><u></u></p></blockquote></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br><br clear="all"><u></u><u></u></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">--<span> </span><u></u><u></u></div><div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif">Hilario Fernandes<u></u><u></u></div></div></div></div></div></div></div><p class="MsoNormal" style="margin:0in 0in 12pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><br>_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org" style="color:purple;text-decoration:underline" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color:purple;text-decoration:underline" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><u></u><u></u></p></blockquote></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:&#39;Times New Roman&#39;,serif"><u></u> <u></u></div></div></div><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;float:none;display:inline!important">_______________________________________________</span><br style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;float:none;display:inline!important">Mondrian mailing list</span><br style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><a href="mailto:Mondrian@pentaho.org" style="color:purple;text-decoration:underline;font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px" target="_blank">Mondrian@pentaho.org</a><br style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" style="color:purple;text-decoration:underline;font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a></div></blockquote></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" rel="noreferrer" 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"><div dir="ltr"><div>Hilario Fernandes</div></div></div>
</div>