<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR>
<STYLE>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
p.MsoToc1, li.MsoToc1, div.MsoToc1
{margin-top:6.0pt;
margin-right:0in;
margin-bottom:6.0pt;
margin-left:0in;
font-size:12.0pt;
font-family:Arial;
text-transform:uppercase;
font-weight:bold;}
p.MsoToc2, li.MsoToc2, div.MsoToc2
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:12.0pt;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:Arial;
font-variant:small-caps;}
p.MsoToc3, li.MsoToc3, div.MsoToc3
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:24.0pt;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:Arial;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle20
{mso-style-type:personal;
font-family:Arial;
color:windowtext;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in .7in 1.0in 1.0in;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:415397990;
mso-list-template-ids:1608933470;}
@list l1
{mso-list-id:2065055004;
mso-list-type:hybrid;
mso-list-template-ids:1313232118 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l1:level1
{mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level2
{mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level3
{mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level4
{mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level5
{mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level6
{mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level7
{mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level8
{mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level9
{mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><FONT face=Verdana color=#000080
size=2></FONT> </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><BR>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000080 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE class=Section1
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: medium none; PADDING-LEFT: 4pt; PADDING-BOTTOM: 0in; MARGIN: 5pt 0in 5pt 3.75pt; BORDER-LEFT: navy 1.5pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: medium none">
<P class=MsoNormal><FONT face=Verdana color=#000080><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><SPAN
class=362035008-10072007><FONT face=Verdana>Somita Arora
wrote:</FONT></SPAN></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Verdana color=#000080><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><SPAN
class=362035008-10072007> </SPAN></SPAN></FONT><FONT face=Arial><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><BR>Is it possible to perform
calculations on fact table columns without applying any aggregator on
measure.<BR>Eg. We want to calculate the sale amount on each record in fact
table using the columns as ‘qty’ * ‘price’ where ‘qty’ and ‘price’
exist as columns in fact table.<BR>The problem is how do we specify this
calculation in schema without using an aggregator. We did see post on this
at <A
href="http://forums.pentaho.org/showthread.php?t=47865&highlight=fact+aggregator">http://forums.pentaho.org/showthread.php?t=47865&highlight=fact+aggregator</A>
, but would like an approach that didn’t involve a code
modification.<o:p></o:p></SPAN></FONT> <SPAN
class=362035008-10072007><FONT face=Verdana color=#000080
size=2> </FONT></SPAN></P></BLOCKQUOTE></BLOCKQUOTE>
<DIV class=MsoNormal dir=ltr
style="MARGIN-BOTTOM: 12pt; mso-list: l1 level1 lfo3"><SPAN
class=362035008-10072007><FONT face=Verdana color=#000080 size=2>I don't know
whether you saw this, at <A
href="http://mondrian.pentaho.org/documentation/schema.php#Measures">http://mondrian.pentaho.org/documentation/schema.php#Measures</A>:</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=MsoNormal dir=ltr
style="MARGIN-BOTTOM: 12pt; mso-list: l1 level1 lfo3"><SPAN
class=362035008-10072007><FONT face=Verdana color=#000080 size=2>
<P>Rather than coming from a column, a measure can use a <A
href="http://mondrian.pentaho.org/documentation/schema.php#Cell_reader"><FONT
color=#0066cc>cell reader</FONT></A>, or a measure can use a SQL expression to
calculate its value. The measure "Promotion Sales" is an example of this.</P>
<BLOCKQUOTE><CODE><<A
href="http://mondrian.pentaho.org/documentation/schema.php#XML_Measure"><FONT
color=#0066cc>Measure</FONT></A> name="Promotion Sales" aggregator="sum"
formatString="#,###.00"><BR><SPAN style="PADDING-LEFT: 10px"><<A
href="http://mondrian.pentaho.org/documentation/schema.php#XML_MeasureExpression"><FONT
color=#0066cc>MeasureExpression</FONT></A>></SPAN><BR><SPAN
style="PADDING-LEFT: 20px"><<A
href="http://mondrian.pentaho.org/documentation/schema.php#XML_SQL"><FONT
color=#0066cc>SQL</FONT></A> dialect="generic"></SPAN><BR><SPAN
style="PADDING-LEFT: 30px">(case when sales_fact_1997.promotion_id =
</SPAN><BR><SPAN style="PADDING-LEFT: 30px">0 then 0 else
sales_fact_1997.store_sales end)</SPAN><BR><SPAN
style="PADDING-LEFT: 20px"></<A
href="http://mondrian.pentaho.org/documentation/schema.php#XML_SQL"><FONT
color=#0066cc>SQL</FONT></A>></SPAN><BR><SPAN
style="PADDING-LEFT: 10px"></<A
href="http://mondrian.pentaho.org/documentation/schema.php#XML_MeasureExpression"><FONT
color=#0066cc>MeasureExpression</FONT></A>></SPAN><BR></<A
href="http://mondrian.pentaho.org/documentation/schema.php#XML_Measure"><FONT
color=#0066cc>Measure</FONT></A>> </CODE></BLOCKQUOTE>
<P>In this case, sales are only included in the summation if they correspond
to a promotion sales. Arbitrary SQL expressions can be used, including
subqueries. However, the underlying database must be able to support that SQL
expression in the context of an aggregate. Variations in syntax between
different databases is handled by specifying the dialect in the SQL
tag.</P></FONT></SPAN></DIV></BLOCKQUOTE>
<DIV class=MsoNormal dir=ltr
style="MARGIN-BOTTOM: 12pt; mso-list: l1 level1 lfo3"><SPAN
class=362035008-10072007><FONT face=Verdana color=#000080 size=2>Those values
will still be aggregated. I can't think how it could possibly be meaningful to
use values from the fact table without aggregating them, because any given cell
might have more than one row from the fact table.</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000080 2px solid; MARGIN-RIGHT: 0px">
<BLOCKQUOTE class=Section1
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: medium none; PADDING-LEFT: 4pt; PADDING-BOTTOM: 0in; MARGIN: 5pt 0in 5pt 3.75pt; BORDER-LEFT: navy 1.5pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: medium none">
<OL style="MARGIN-TOP: 0in" type=1>
<LI class=MsoNormal style="mso-list: l1 level1 lfo3"><FONT face=Arial
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">The mondrian
2.3.2 allows ‘date’ type for ‘type’ attribute in level. Is it possible to
apply date manipulations on such types. Eg. Finding date differences
between two member values of type date. We get 0 as result value when we
apply a difference of these values. This feature would also be
useful if we have to find difference between current date and a date
member.<BR>This query is also posted on forums as <A
href="http://forums.pentaho.org/showthread.php?t=54268">http://forums.pentaho.org/showthread.php?t=54268</A></SPAN></FONT> <SPAN
class=362035008-10072007><FONT face=Verdana color=#000080
size=2> </FONT></SPAN></LI></OL></BLOCKQUOTE></BLOCKQUOTE>
<DIV class=MsoNormal dir=ltr style="mso-list: l1 level1 lfo3"><SPAN
class=362035008-10072007><FONT face=Verdana color=#000080 size=2>Mondrian's
support for the date datatype is not very complete. First, I would not be
surprised if there were bugs loading date columns from SQL into memory. Second,
no operators are defined which work on dates. You should be able to define UDFs
so you can manipulate dates in MDX; for example, you might implement a
<EM>Date_Subtract(<Date>, <Date>) returns <Numeric></EM>
operator which returns the number of days between two dates.</FONT></SPAN></DIV>
<DIV class=MsoNormal dir=ltr style="mso-list: l1 level1 lfo3"><SPAN
class=362035008-10072007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV class=MsoNormal dir=ltr style="mso-list: l1 level1 lfo3"><SPAN
class=362035008-10072007><FONT face=Verdana color=#000080
size=2>Julian</FONT></SPAN></DIV></BODY></HTML>