<div dir="ltr">Tomasz-<div>I've entered <a id="key-val" rel="98845" href="http://jira.pentaho.com/browse/MONDRIAN-1370" style="background-color:transparent;font-family:arial,FreeSans,Helvetica,sans-serif;font-size:1.167em;line-height:1.429;color:rgb(153,102,0)">MONDRIAN-1370</a> for the bug you found. I expect it will get picked up for the next release. Thanks for identifying this.</div>
<div><br></div><div style>Matt</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jan 8, 2013 at 5:44 PM, Matt Campbell <span dir="ltr"><<a href="mailto:mkambol@gmail.com" target="_blank">mkambol@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><br></div><div>I see what your saying-- the aggregator selected when using the ExplicitRecognizer is different from the DefaultRecognizer. A count distinct measure in an explicit aggregate can be incorrectly summed. </div>
<div><br></div><div>I'll discuss this with the team and get back to you.</div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jan 8, 2013 at 3:24 PM, Tomasz Karlik <span dir="ltr"><<a href="mailto:tkarlik@gmail.com" target="_blank">tkarlik@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I suppose that without this change Mondrian will always use aggregate<br>
table. Aggregate table column is handled by RolapAggregator.Sum, so<br>
code created to deal with different distinct count scenarios is<br>
omitted.<br>
<br>
When using rule-based recognition Mondrian uses Recognizer.java code<br>
(lines 782-792):<br>
<br>
// NOTE: This assumes that the aggregate table does not have an explicit<br>
// average column.<br>
if (factAgg == RolapAggregator.Avg) {<br>
String columnExpr = getFactCountExpr(aggUsage);<br>
return new RolapAggregator.AvgFromSum(columnExpr);<br>
} else if (factAgg == RolapAggregator.DistinctCount) {<br>
//return RolapAggregator.Count;<br>
return RolapAggregator.DistinctCount;<br>
} else {<br>
return factAgg;<br>
}<br>
Are there really necessary separate methods to deal with both of<br>
recognizers? How to change schema definition, to handle distinct-count<br>
aggregated measures correctly when using explicit recognizer?<br>
<div><div><br>
<br>
On Mon, Jan 7, 2013 at 3:46 PM, Matt Campbell <<a href="mailto:mkambol@gmail.com" target="_blank">mkambol@gmail.com</a>> wrote:<br>
> Distinct count measures in aggregate tables are tricky and require care on<br>
> the part of the schema author. There are some cases where it is appropriate<br>
> to pull a distinct count value from an aggregate table--i.e. when the<br>
> attributes are dependent on the measure (like gender in the case of customer<br>
> count).<br>
><br>
> I'm hoping Julian or Luc can jump in, but I'd be worried your change would<br>
> cause Mondrian to always query the fact table for count distincts, even when<br>
> it may be appropriate to query the agg table.<br>
><br>
><br>
> On Mon, Jan 7, 2013 at 3:47 AM, Tomasz Karlik <<a href="mailto:tkarlik@gmail.com" target="_blank">tkarlik@gmail.com</a>> wrote:<br>
>><br>
>> SQL seems to be wrong:<br>
>><br>
>> select<br>
>> "agg_c_10_sales_fact_1997"."the_year" as "c0",<br>
>> sum("agg_c_10_sales_fact_1997"."customer_count") as "m0"<br>
>> from<br>
>> "agg_c_10_sales_fact_1997" as "agg_c_10_sales_fact_1997"<br>
>> where<br>
>> "agg_c_10_sales_fact_1997"."the_year" = 1997<br>
>> group by<br>
>> "agg_c_10_sales_fact_1997"."the_year"<br>
>><br>
>> Log file is in attachment.<br>
>><br>
>> On Mon, Jan 7, 2013 at 9:19 AM, Julian Hyde <<a href="mailto:jhyde@pentaho.com" target="_blank">jhyde@pentaho.com</a>> wrote:<br>
>> > The easiest way to approach this is to look at the SQL Mondrian is<br>
>> > generating. Is it asking the right question of the DBMS?<br>
>> ><br>
>> > Can you turn on SQL tracing and post the log you get during the run that<br>
>> > produces the incorrect results.<br>
>> ><br>
>> > Julian<br>
>> ><br>
>> ><br>
>> > On Jan 7, 2013, at 12:10 AM, Tomasz Karlik <<a href="mailto:tkarlik@gmail.com" target="_blank">tkarlik@gmail.com</a>> wrote:<br>
>> ><br>
>> >> I am getting different results while running simple query on FoodMart<br>
>> >> (PostgreSql db). Result differs and seems to be wrong when using<br>
>> >> explicit aggregate. I've created simple test which shows this<br>
>> >> behavior. Also I've made some patch, but i'm not sure if the problem<br>
>> >> is in sources? The problem doesn't appear when aggregate table is<br>
>> >> recognized by the default rules.<br>
>> >><br>
>> >> query:<br>
>> >><br>
>> >> select<br>
>> >><br>
>> >> NON EMPTY {[Measures].[Customer Count]} ON COLUMNS,<br>
>> >><br>
>> >> NON EMPTY {[Time].[Year].Members} ON ROWS<br>
>> >><br>
>> >> from [Sales]<br>
>> >><br>
>> >> aggregate table: agg_c_10_sales_fact_1997<br>
>> >><br>
>> >><br>
>> >> expected result:<br>
>> >><br>
>> >> Axis #0:<br>
>> >><br>
>> >> {}<br>
>> >><br>
>> >> Axis #1:<br>
>> >><br>
>> >> {[Measures].[Customer Count]}<br>
>> >><br>
>> >> Axis #2:<br>
>> >><br>
>> >> {[Time].[1997]}<br>
>> >><br>
>> >> Row #0: 5 581<br>
>> >><br>
>> >><br>
>> >> actual result:<br>
>> >><br>
>> >> Axis #0:<br>
>> >><br>
>> >> {}<br>
>> >><br>
>> >> Axis #1:<br>
>> >><br>
>> >> {[Measures].[Customer Count]}<br>
>> >><br>
>> >> Axis #2:<br>
>> >><br>
>> >> {[Time].[1997]}<br>
>> >><br>
>> >> Row #0: 16 947<br>
>> >><br>
>> >> <ExplicitRecognizerTest.java><mondrian_rolap_aggmatcher_Recognizer.java.patch>_______________________________________________<br>
>> >> Mondrian mailing list<br>
>> >> <a href="mailto:Mondrian@pentaho.org" target="_blank">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>
>> ><br>
>> > _______________________________________________<br>
>> > Mondrian mailing list<br>
>> > <a href="mailto:Mondrian@pentaho.org" target="_blank">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>
>><br>
>> _______________________________________________<br>
>> Mondrian mailing list<br>
>> <a href="mailto:Mondrian@pentaho.org" target="_blank">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>
>><br>
><br>
><br>
> _______________________________________________<br>
> Mondrian mailing list<br>
> <a href="mailto:Mondrian@pentaho.org" target="_blank">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>
><br>
_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">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>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>