<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;">Once again, sorry for not mailing it to the developers list. I just hit reply.<br>I've read your response. I don't think it will work for my requirements because the data has to come from aggregate tables and not the fact table at all. Actually there's a case of when the fact table IS the lowest level of aggregation already, but I don't think Mondrian deals with that (Count will count the rows in fact tables but sum the count measure in aggregate tables for example). <br><br>However I think it is a good idea, if orthogonal. The only disagreement I have is the semi-additive measure system you've got. MDX should be used to define these sorts of things. It's great if there is a mechanism to place
these cells into the cell cache (calculated cells aren't right now I think) but don't use "last" like you have. Once again all this is I think orthogonal to my needs.<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>Cc : mondrian@pentaho.org<br>Envoyé le : Vendredi, 23 Novembre 2007, 1h00mn 43s<br>Objet : RE: [Mondrian] Question on non-aggregable measures<br><br>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">This thread should have stayed on the developers' list. (I
don't usually broaden personal messages to public lists, because I don't want to
inadvertently publish confidential information. But I took the liberty of Cc:ing
the developer's list this time.)</font></span></div></font></span></div>
<div><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><font color="#000080" face="Verdana" size="2"></font><br></div>
<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"><font face="Tahoma" size="2">michael bienstein <span class="461022723-22112007"><font color="#000080" face="Verdana"> wrote: </font></span></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;">The
requirement is that at any organisation/time crossing the budget user can
enter a number. Even if all child organisations have numbers the parent
is NOT aggregated and could be contradictory. Bizarre requirement that,
but that's the way it's currently done and they want the same thing only for
it to run faster so I'm proposing to change technology.</div></div></blockquote>
<div style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><span class="461022723-22112007">
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">Since we're talking about developing a feature, let's see
how we can broaden the requirement to cover other common cases, without making
the specification or implementation much more complicated.</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">In the case of non-aggregatable measures, we could model
these as an aggregation function NONE.</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">I suppose if you are going to define such a measure, you
need to ensure that your fact table contains at must one row per cell. Mondrian
could enforce that by generating MAX and MIN and comparing
them.</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">Also, I can imagine a measure which can be rolled up along
some dimensions but not others; maybe not in your application, but in
others.</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">I experienced this with distinct-count measures. For
example, the number of distinct customers can be aggregated over the gender
dimension, but not over the time dimension. (If you have 10 distinct female
customers in Q1 and 5 distinct male customers in Q1, you definitely have 15
distinct customers in Q1. Why can you say that? Because gender is functionally
dependent on customer id.)</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">Also, consider semi-additive measures, the textbook example
of which is inventory-on-hand. This measure is aggregated using the LAST
operator on the time dimension, SUM operator on other dimensions (e.g.
product).</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">Putting this all together, I see a measure being defined
with a default aggregation function, which may be SUM, AVG, FIRST, LAST, NONE;
and being able to override the aggregation function for certain
dimensions:</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"><Cube></font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> ...</font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> <Measure name="Inventory on hand"
aggregator="sum"></font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> <RollupRule
dimension="Time" aggregator="last"/></font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> </Measure></font></span></div>
<div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> <Measure name="Customer count"
aggregator="distinct-count"></font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> <RollupRule
dimension="Gender" aggregator="sum"/></font></span></div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> </Measure></font></span></div></div>
<div><font color="#000080" face="Verdana" size="2">
<div>
<div dir="ltr" align="left"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"> <Measure name="Budget"
aggregator="none"/></font></span></div></div></font></div>
<div><font color="#000080" face="Verdana" size="2"><span class="461022723-22112007"></Cube></span></font></div>
<div><font color="#000080" face="Verdana" size="2"></font> </div>
<div><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">Is
this more general scheme still consistent with your requirements? It would
certainly be nice to deal with semi-additive measures properly at the same
time.</font></span></div>
<div><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2">Julian</font></span></div></span></div>
<blockquote style="border-left: 2px solid rgb(0, 0, 128); padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><span class="461022723-22112007"><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><span class="461022723-22112007"> </span><br>Thanks Julian, I'll have a look at
that.<br>Michael<br><br><br></div>
<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>
<div style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><br></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>