<div>Robin,</div><div><br></div>I'm surprised the constraint <div> `product_class`.`product_family` in ('Food', 'Non-Consumable')</div><div><br></div><div>is missing from the second query. The RolapNativeFilter evaluator reduced the set of tuples down to </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. (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. You'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"><<a href="mailto:rtharappel@gmail.com">rtharappel@gmail.com</a>></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] > 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>
"product_class"."product_family" as "c0",<br>
"store"."store_country" as "c1",<br>
"store"."store_state" as "c2"<br>
from<br>
"product" "product",<br>
"product_class" "product_class",<br>
"agg_c_14_sales_fact_1997" "agg_c_14_sales_fact_1997",<br>
"store" "store"<br>
where<br>
"product"."product_class_id" = "product_class"."product_class_id"<br>
and "agg_c_14_sales_fact_1997"."product_id" = "product"."product_id"<br>
and "agg_c_14_sales_fact_1997"."store_id" = "store"."store_id"<br>
and "agg_c_14_sales_fact_1997"."the_year" = 1997<br>
and ("store"."store_country" = 'USA')<br>
group by "product_class"."product_family","store"."store_country","store"."store_state"<br>
having (sum("agg_c_14_sales_fact_1997"."unit_sales") > 20000.0)<br>
order by "product_class"."product_family" ASC,<br>
"store"."store_country" ASC,<br>
"store"."store_state" ASC<br>
<br>
Below is the SQL generated by Segment without the measure filter<br>
(returns 9 rows)<br>
<br>
select<br>
"store"."store_state" as "c0",<br>
"agg_c_14_sales_fact_1997"."the_year" as "c1",<br>
"product_class"."product_family" as "c2",<br>
sum("agg_c_14_sales_fact_1997"."unit_sales") as "m0",<br>
sum("agg_c_14_sales_fact_1997"."store_sales") as "m1"<br>
from<br>
"store" "store",<br>
"agg_c_14_sales_fact_1997" "agg_c_14_sales_fact_1997",<br>
"product_class" "product_class",<br>
"product" "product"<br>
where<br>
"agg_c_14_sales_fact_1997"."store_id" = "store"."store_id" and<br>
"store"."store_state" in ('CA', 'OR', 'WA') and<br>
"agg_c_14_sales_fact_1997"."the_year" = 1997 and<br>
"agg_c_14_sales_fact_1997"."product_id" = "product"."product_id"<br>
and<br>
"product"."product_class_id" = "product_class"."product_class_id"<br>
group by<br>
"store"."store_state",<br>
"agg_c_14_sales_fact_1997"."the_year",<br>
"product_class"."product_family"<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. 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>