<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<STYLE type=text/css><!-- DIV {margin:0px;} --></STYLE>
<META content="MSHTML 6.00.6000.16546" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=429391810-23112007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV><FONT face=Verdana color=#000080
size=2></FONT><FONT face=Verdana color=#000080 size=2></FONT><FONT face=Verdana
color=#000080 size=2></FONT><FONT face=Verdana color=#000080 size=2></FONT><FONT
face=Verdana color=#000080 size=2></FONT><BR>
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000080 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2> Michael
Bienstein wrote:</FONT></SPAN></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=429391810-23112007></SPAN> </DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>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. <SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2> </FONT></SPAN></DIV></BLOCKQUOTE>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>I hear you. We
need a measure which may or may not exist in the fact table, may or may not
exist in any particular agg table.</FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>A schema
designer should optionally be able to specify that a non-aggregatable measure to
be stored in the fact table. In your case, just don't specify a column when you
define the measure. Mondrian should accept that iff the aggregate function is
'none'.</FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>The measure
would exist only at those levels of aggregation for which there is an aggregate
table and that agg table has a column that maps (according to the mapping rules)
to that measure.</FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>The measure
would also exist if there was a rollup path along rollup-able dimensions. (In
your world, there are no rollup-able dimensions, but in my more general world
they exist and are very useful.)</FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>In other cases
it would be EMPTY.</FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>By the way,
storing measures in agg tables breaks the principle of <A
href="http://en.wikipedia.org/wiki/Data_independence">data independence</A>, so
is not recommended unless you have special requirements. If you store measures
in agg tables, the agg tables have to be set in stone, and you would not be able
to use an automated agg table designer.</FONT></SPAN></DIV>
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000080 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif">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). <SPAN class=429391810-23112007><FONT face=Verdana
color=#000080 size=2> </FONT></SPAN></DIV></BLOCKQUOTE>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>I don't see the
problem. There is no universal 'count' measure in MDX. <SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>The only
'official' use for a count measure is when computing an average, and
AVG(x) always uses the local count from the same cube as
x.</FONT></SPAN></FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2><SPAN
class=429391810-23112007></SPAN></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2><SPAN
class=429391810-23112007>The metamodel is flexible, so you can roll your own
count measure in whatever way works for you. </SPAN>In a virtual cube, you
can have several count measures from several constituent cubes, and they will
give different answers. You're free to create a FACT_COUNT column in an agg
table and populate it with ones, or create a count measure in a fact table which
is a sum of a column.</FONT></SPAN></DIV>
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000080 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif">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.</DIV></BLOCKQUOTE>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>I'd like to
hear from people who are using semi-additive measures (SAMs). Are they doing it
successfully using calculated members? I have a feeling that it would be better
if there were mechanisms to define </FONT></SPAN><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>SAMs as both
stored measures, and as calculated measures.</FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080 size=2>Improved
caching would help calc measures - see my post/email about a week ago on
improved statement-level caching - but they are never going to be as efficient
as stored measures. Stored measures can be included in agg tables, and we can
let SQL do the heavy lifting of multiple layers of aggregation (e.g. first sum
along dimension x, then find the last value along the time dimension, then sum
along dimensions y and z). Modern SQL includes LAST as an agg function, so we
should leverage this.</FONT></SPAN></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial,helvetica,sans-serif"><SPAN
class=429391810-23112007><FONT face=Verdana color=#000080
size=2>Julian</FONT></SPAN></DIV></BODY></HTML>