<div>Robin,</div><div><br></div>I&#39;m surprised the constraint&nbsp;<div>&nbsp;&nbsp; `product_class`.`product_family` in (&#39;Food&#39;, &#39;Non-Consumable&#39;)</div><div><br></div><div>is missing from the second query. &nbsp;The RolapNativeFilter evaluator reduced the set of tuples down to&nbsp;</div>
<div><br></div><div><div><div>Food<span class="Apple-tab-span" style="white-space:pre">        </span>USA<span class="Apple-tab-span" style="white-space:pre">        </span>CA</div><div>Food<span class="Apple-tab-span" style="white-space:pre">        </span>USA<span class="Apple-tab-span" style="white-space:pre">        </span>OR</div>
<div>Food<span class="Apple-tab-span" style="white-space:pre">        </span>USA<span class="Apple-tab-span" style="white-space:pre">        </span>WA</div><div>Non-Consumable<span class="Apple-tab-span" style="white-space:pre">        </span>USA<span class="Apple-tab-span" style="white-space:pre">        </span>WA</div>
</div><div><br></div><div>Which should have forced a product_family constraint into the Segment load. &nbsp;(I do see that constraint when I run the query...)</div><div><br></div><div>This would bring the number of rows returned in this particular case down to 6. &nbsp;You&#39;re right, though, it would definitely be ideal to add the HAVING clause to reduce it to just the relevant rows.</div>
<br></div><div><br><div class="gmail_quote">On Mon, Feb 9, 2009 at 8:52 PM, Robin Tharappel <span dir="ltr">&lt;<a href="mailto:rtharappel@gmail.com">rtharappel@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;">
Hello,<br>
<br>
I am coming across a performance issue with an MDX that has a measure<br>
filter. In my MDX the filter is evaluated natively and the<br>
SqlTupleReader generates a SQL that returns 10,000 rows. However the<br>
query for loading the Segment returns over 1 million rows. The main<br>
reason for the difference between the number of rows returned between<br>
SqlTupleReader and Segment is that the query generated for<br>
Segment.load does not have the measure filter. The following MDX using<br>
the FoodMart data set can illustrate the problem<br>
<br>
select {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,<br>
Filter(NonEmptyCrossJoin([Product].[All Products].Children,<br>
[Store].[All Stores].[USA].Children),<br>
[Measures].[Unit Sales] &gt; 20000) ON ROWS<br>
from [Sales]<br>
<br>
Below is the SQL generated by SqlTupleReader with the having clause<br>
for the measure filter (returns 4 rows)<br>
<br>
<br>
select<br>
&quot;product_class&quot;.&quot;product_family&quot; as &quot;c0&quot;,<br>
&quot;store&quot;.&quot;store_country&quot; as &quot;c1&quot;,<br>
&quot;store&quot;.&quot;store_state&quot; as &quot;c2&quot;<br>
from<br>
&quot;product&quot; &quot;product&quot;,<br>
&quot;product_class&quot; &quot;product_class&quot;,<br>
&quot;agg_c_14_sales_fact_1997&quot; &quot;agg_c_14_sales_fact_1997&quot;,<br>
&quot;store&quot; &quot;store&quot;<br>
where<br>
&quot;product&quot;.&quot;product_class_id&quot; = &quot;product_class&quot;.&quot;product_class_id&quot;<br>
and &quot;agg_c_14_sales_fact_1997&quot;.&quot;product_id&quot; = &quot;product&quot;.&quot;product_id&quot;<br>
and &quot;agg_c_14_sales_fact_1997&quot;.&quot;store_id&quot; = &quot;store&quot;.&quot;store_id&quot;<br>
and &quot;agg_c_14_sales_fact_1997&quot;.&quot;the_year&quot; = 1997<br>
and (&quot;store&quot;.&quot;store_country&quot; = &#39;USA&#39;)<br>
group by &quot;product_class&quot;.&quot;product_family&quot;,&quot;store&quot;.&quot;store_country&quot;,&quot;store&quot;.&quot;store_state&quot;<br>
having (sum(&quot;agg_c_14_sales_fact_1997&quot;.&quot;unit_sales&quot;) &gt; 20000.0)<br>
order by &quot;product_class&quot;.&quot;product_family&quot; ASC,<br>
&quot;store&quot;.&quot;store_country&quot; ASC,<br>
&quot;store&quot;.&quot;store_state&quot; ASC<br>
<br>
Below is the SQL generated by Segment without the measure filter<br>
(returns 9 rows)<br>
<br>
select<br>
&quot;store&quot;.&quot;store_state&quot; as &quot;c0&quot;,<br>
&quot;agg_c_14_sales_fact_1997&quot;.&quot;the_year&quot; as &quot;c1&quot;,<br>
&quot;product_class&quot;.&quot;product_family&quot; as &quot;c2&quot;,<br>
sum(&quot;agg_c_14_sales_fact_1997&quot;.&quot;unit_sales&quot;) as &quot;m0&quot;,<br>
sum(&quot;agg_c_14_sales_fact_1997&quot;.&quot;store_sales&quot;) as &quot;m1&quot;<br>
from<br>
&quot;store&quot; &quot;store&quot;,<br>
&quot;agg_c_14_sales_fact_1997&quot; &quot;agg_c_14_sales_fact_1997&quot;,<br>
&quot;product_class&quot; &quot;product_class&quot;,<br>
&quot;product&quot; &quot;product&quot;<br>
where<br>
&quot;agg_c_14_sales_fact_1997&quot;.&quot;store_id&quot; = &quot;store&quot;.&quot;store_id&quot; and<br>
&quot;store&quot;.&quot;store_state&quot; in (&#39;CA&#39;, &#39;OR&#39;, &#39;WA&#39;) and<br>
&quot;agg_c_14_sales_fact_1997&quot;.&quot;the_year&quot; = 1997 and<br>
&quot;agg_c_14_sales_fact_1997&quot;.&quot;product_id&quot; = &quot;product&quot;.&quot;product_id&quot;<br>
&nbsp;and<br>
&quot;product&quot;.&quot;product_class_id&quot; = &quot;product_class&quot;.&quot;product_class_id&quot;<br>
group by<br>
&quot;store&quot;.&quot;store_state&quot;,<br>
&quot;agg_c_14_sales_fact_1997&quot;.&quot;the_year&quot;,<br>
&quot;product_class&quot;.&quot;product_family&quot;<br>
<br>
Although the difference in this example is small, with a larger<br>
dataset a situation could occur where there is a lot of data loaded<br>
into the Segment which is not needed to resolve the query. &nbsp;Would it<br>
be possible to have the measure filter applied to the Segment query<br>
(add the having clause) ?<br>
<br>
Thanks,<br>
<br>
Robin<br>
<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>
</blockquote></div><br></div>