<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial,helvetica,sans-serif;font-size:12pt"><div style="font-family: arial,helvetica,sans-serif; font-size: 12pt;">Sorry about mailing to your personal address. I hit respond thinking the mail list system would take care of it. I was wondering why they didn't appear actually.<br><br>Michael<br><br><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">----- Message transféré ----<br>De : Julian Hyde <jhyde@pentaho.org><br>À : michael bienstein <mbienstein@yahoo.fr><br>Envoyé le : Vendredi, 23 Novembre 2007, 1h01mn 41s<br>Objet : RE: [Mondrian] Question on non-aggregable measures<br><br>
<div dir="ltr" align="left"><span class="004050100-23112007"><font color="#000080" face="Verdana" size="2">Can you re-post this to the developer's list, please. I
will reply to it there.</font></span></div>
<div dir="ltr" align="left"><span class="004050100-23112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="004050100-23112007"><font color="#000080" face="Verdana" size="2">Julian</font></span></div><br>
<blockquote style="border-left: 2px solid rgb(0, 0, 128); padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div class="OutlookMessageHeader" dir="ltr" align="left" lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><b>From:</b> michael bienstein
[mailto:mbienstein@yahoo.fr] <br><b>Sent:</b> Thursday, November 22, 2007 1:21
PM<br><b>To:</b> jhyde@pentaho.org<br><b>Subject:</b> Re : [Mondrian] Question
on non-aggregable measures<br></font><br></div>
<div></div>
<div style="font-size: 12pt; font-family: arial,helvetica,sans-serif;">
<div style="font-size: 12pt; font-family: arial,helvetica,sans-serif;">I've had
a look through the code. There's a lot of new and quite complex code
around the Grouping Sets in there and the distinct count stuff seems a bit all
over the place and I can't easily follow it. I do however have an idea
to make this simple and would appreciate your opinion(s).<br><br>The sort of
measure I'm referring to would have to be in a separate star/aggregate-schema
because the SQL generated would never have GROUP BY. It would be
impossible to have normal measures in the same star and do queries that
recover normal measures and this sort of measure. So the easiest way to
do it is to use Virtual Cubes between normal measures from one sort of cube
and another type of cube for the special, non-aggregable measures. (The
other way I can think of is to launch separate SQLs against the same set of
aggregate tables but I don't think this is a good fix).<br><br>In this case,
it's essentially a property of a RolaStar. In examining the code I see
the following points:<br>1) AggregationManager.generateSql(). This code
generates a String of SQL to execute. I want to run through the logic of the
aggregation table matching found here. If there's no match I DON'T want
to fall through to aggregating off the fact table. However I can't put a
check in there because we expect to get a non-null string of valid SQL and in
my case there simply won't be any. So I want to see if I can return null
and deal with that case.<br>2)
SegmentLoader.createExecuteSql(GroupingSetsList) is the only place I see that
calls AggregationManager.generateSql(). It currently takes the valid,
non-null SQL and executes it in JDBC returning the wrapped ResultSet in a
SqlStatement. One option would be to check for a null return (then
assert on the type of RolapStar) and if null return a fake SqlStatement object
that returns valid meta-data but no lines. At least I THINK that that
could work. We may expect lines for known members ... I don't know
yet. In any case, I would have trouble faking a ResultSet I think.
The JDBC spec changes so much I don't think its a good idea even if we now
have WrappedResultSet and the like. Much better would be to know that it
is fake.<br>3) SegmentLoader.load(List<GroupingSets>,
RolapAggregationManager.PinSet) is the only place that createExecuteSql is
called. It calls processData() to get a list of rows from the
SqlStatement. The only other thing it does is catch SQLExceptions with
it. If we just return an empty list from processData() or skip
processData if the SqlStatement doesn't wrap a JDBC ResultSet (has a null
instead) then this should work.<br><br>So the proposed changes are:<br>a) Add
a property to the RolapStar to indicate that no rollups are allowed.<br>b)
Include this in the XML DTD.<br>c) Modify SegmentLoader.load() on line 54 to
check if the SqlStatement returned from createExecuteSql() is null or
not. If null, skip the call to processData, set sparse to true ...
(maybe modify the catch clause for SQLException but not necessary).<br>d)
Modify SegmentLoader.createExecuteSql() to check if the sql String returned
from generateSql() is null and if so return a null SqlStatement instead of
calling executeQuery().<br>e) Add a check at line 221 of
AggregationManager.generateSql() to avoid dealing with the Fact table if the
rolap star has the new property set to true. In this case it returns a
null String.<br>f) So as to trick the aggregate table matching we need to have
aggStar.getDistinctMeasureBitKey() return a bitkey with all the measures in
this star on. This is because we don't ever want to rollup from
aggregate tables (just like distinct count measures at the moment). This
is simple: modify AggregationManager.findAgg() line 278. isDistinct =
star.isRollupAllowed() ? /*Current condition*/ : true; //That's the new
property.<br><br>I think that should do it. Am I right? (If so I
will be implementing it in January).<br><br>Michael<br>3)
<br><br>SegmentLoader.loadData() assumes we get a genuine <br><br>
<div style="font-size: 12pt; font-family: times new roman,new york,times,serif;">-----
Message d'origine ----<br>De : Julian Hyde <jhyde@pentaho.org><br>À :
Mondrian developer mailing list <mondrian@pentaho.org><br>Envoyé le :
Jeudi, 22 Novembre 2007, 1h53mn 12s<br>Objet : RE: [Mondrian] Question on
non-aggregable measures<br><br>
<div dir="ltr" align="left"><span class="788184900-22112007"><font color="#000080" face="Verdana" size="2">We already have the mother of all non-aggregable
measures, namely distinct-count. It's slightly different in that it's
computable by going back to the fact table, whereas for what you want, if it's
not in the agg table, it ain't there at all. (Or maybe you would allow
aggregation on some dimensions but not others - I don't know your precise
requirements.)</font></span></div>
<div dir="ltr" align="left"><span class="788184900-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="788184900-22112007"><font color="#000080" face="Verdana" size="2">So, it isn't there, but it could be fairly easily
implemented by generalizing the distinct-count
functionality.</font></span></div>
<div dir="ltr" align="left"><span class="788184900-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="788184900-22112007"><font color="#000080" face="Verdana" size="2">Julian</font></span></div><br>
<blockquote style="border-left: 2px solid rgb(0, 0, 128); padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div class="OutlookMessageHeader" dir="ltr" align="left" lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><b>From:</b> mondrian-bounces@pentaho.org
[mailto:mondrian-bounces@pentaho.org] <b>On Behalf Of </b>michael
bienstein<br><b>Sent:</b> Wednesday, November 21, 2007 11:16
PM<br><b>To:</b> Mondrian developer mailing list<br><b>Subject:</b>
[Mondrian] Question on non-aggregable measures<br></font><br></div>
<div></div>
<div style="font-size: 12pt; font-family: arial,helvetica,sans-serif;">
<div>Hello all,<br><br>I may very well use Mondrian in a short project
soon. One point that I think is probably missing that is part of the
requirements is a non-aggregable measure that is read only from aggregation
tables. E.g. a budget or objective that is filled in at certain
members of an org hierarchy but not all and certainly not aggregated from
facts attached to the leaves. <br><br>I don't know if this can be
handled by the existing code (it's been more than 6 months since I last
looked at the code). Can anyone tell me
please?<br><br>Thanks,<br><br>Michael<br></div></div><br>
<hr size="1">
Ne gardez plus qu'une seule adresse mail ! <a rel="nofollow" target="_blank" href="http://www.trueswitch.com/yahoo-fr/">Copiez
vos mails</a> vers Yahoo! Mail </blockquote></div><br></div></div><br>
<hr size="1">
Ne gardez plus qu'une seule adresse mail ! <a rel="nofollow" target="_blank" href="http://www.trueswitch.com/yahoo-fr/">Copiez vos mails</a> vers Yahoo!
Mail </blockquote></div><br></div></div><br>
<hr size="1">
Ne gardez plus qu'une seule adresse mail ! <a href="http://www.trueswitch.com/yahoo-fr/">Copiez vos mails</a> vers Yahoo! Mail </body></html>