<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
{font-family:"Lucida Sans";
panose-1:2 11 6 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Plain Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.5pt;
font-family:Consolas;}
span.PlainTextChar
{mso-style-name:"Plain Text Char";
mso-style-priority:99;
mso-style-link:"Plain Text";
font-family:Consolas;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:56.7pt 42.5pt 56.7pt 85.05pt;}
div.WordSection1
{page:WordSection1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=WordSection1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Sergey Mazin
[mailto:sergey.mazin@skype.net] <br>
<b>Sent:</b> Sunday, June 20, 2010 10:02 PM<br>
<b>To:</b> 'jhyde@pentaho.com'<br>
<b>Subject:</b> RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='font-family:"Calibri","sans-serif"'>Julian,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-family:"Calibri","sans-serif"'>I am
talking about that issue, when measure has “AVG” aggregator in schema
definition:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal>“<o:p></o:p></p>
<p class=MsoNormal style='margin-bottom:12.0pt'>I have a dimension with days of
week (the dimension hierarchy is year/month/day of week) and I would like to
filter so only some day of weeks are used. In excel, you can add a filter,
check the "Select Multiple Items" and select elements you need.<o:p></o:p></p>
<p class=MsoNormal>If I query fact count, min, max, I don't have any problem.
If I query an avg dimension, I get the following exception:<br>
<br>
2010.05.20;11:38:49.892 WARN Mondrian: exception in executeStripe.<br>
mondrian.olap.fun.MondrianEvaluationException: Don't know how to rollup
aggregator 'avg' ”<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I did small changes and now Mondrian calculates AVG measure
exactly as DISTINCT COUNT measures, sending separate query for each level.<o:p></o:p></p>
<p class=MsoNormal>Please check the files attached. That fix seems to work for
me and I am able to use measures with “avg” aggregator in Excel 2007 drilling
up and down and filtering on dimensions.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Regards,<o:p></o:p></p>
<p class=MsoNormal>Sergey<o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Julian Hyde [mailto:jhyde@pentaho.com]
<br>
<b>Sent:</b> Saturday, June 19, 2010 4:04 AM<br>
<b>To:</b> 'Sergey Mazin'; 'Mondrian developer mailing list'<br>
<b>Subject:</b> RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>Sergey,</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>I fixed MONDRIAN-682 on Tuesday of this week. (Sorry it took me so
long... I really intended to include your patch in 3.2.0, but I forgot; it will
be in 3.2.1.)</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>Can you clarify. Does that mean that "AVG" should be able
to rollup automatically? Or that there is a workaround by rephrasing the query?
Or is another change to mondrian's source code required in order to fix
MONDRIAN-675?</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>Julian</span><o:p></o:p></p>
</div>
<blockquote style='border:none;border-left:solid navy 1.5pt;padding:0in 0in 0in 4.0pt;
margin-left:3.75pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>
<p class=MsoNormal><o:p> </o:p></p>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=2 width="100%" align=center>
</div>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> Sergey Mazin
[mailto:sergey.mazin@skype.net] <br>
<b>Sent:</b> Wednesday, June 16, 2010 3:06 PM<br>
<b>To:</b> jhyde@pentaho.com; 'Mondrian developer mailing list'<br>
<b>Subject:</b> RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Hi
Julian,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoPlainText><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Once
</span>MONDRIAN-682<span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>
issue is fixed we can have the same logic for “avg” measure as for
“distinct-count” measure.<o:p></o:p></span></p>
<p class=MsoPlainText><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>We
send separate query with avg aggregate function for each level in the query.<o:p></o:p></span></p>
<p class=MsoPlainText><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoPlainText>I changed 3 lines in the code and now it seems to work
for me.<o:p></o:p></p>
<p class=MsoPlainText><o:p> </o:p></p>
<p class=MsoPlainText>Regards,<o:p></o:p></p>
<p class=MsoPlainText>Sergey<o:p></o:p></p>
<p class=MsoPlainText><o:p> </o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
mondrian-bounces@pentaho.org [mailto:mondrian-bounces@pentaho.org] <b>On Behalf
Of </b>Julian Hyde<br>
<b>Sent:</b> Thursday, May 20, 2010 11:29 PM<br>
<b>To:</b> 'Mondrian developer mailing list'<br>
<b>Subject:</b> RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>It's Mondrian problem, not specific to Excel.</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>Mondrian is being truthful -- it can't know to roll up
averages. If I tell you the average age of Male customers is 32 and the
average age of Female customers is 28, what's the average age of all customers?
There's simply not enough information.</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>However, if you replace your 'avg' measure with two stored measures
'sum' and 'count', then create a calculated measure from these, you will be
able to roll up.</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>There is a feature request to make mondrian smart enough to do this
automatically: <a href="http://jira.pentaho.com/browse/MONDRIAN-675"><span
style='font-size:12.0pt;font-family:"Times New Roman","serif"'>http://jira.pentaho.com/browse/MONDRIAN-675</span></a></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Sans";
color:navy'>Julian</span><o:p></o:p></p>
</div>
<blockquote style='border:none;border-left:solid navy 1.5pt;padding:0in 0in 0in 4.0pt;
margin-left:3.75pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>
<p class=MsoNormal><o:p> </o:p></p>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=2 width="100%" align=center>
</div>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> mondrian-bounces@pentaho.org
[mailto:mondrian-bounces@pentaho.org] <b>On Behalf Of </b>Manuel Darveau<br>
<b>Sent:</b> Thursday, May 20, 2010 8:54 AM<br>
<b>To:</b> mondrian@pentaho.org<br>
<b>Subject:</b> [Mondrian] Excel + MondrianEvaluationException: Don't know how
torollup aggregator 'avg'</span><o:p></o:p></p>
<p class=MsoNormal>Hi,<br>
<br>
I am using excel 2007 (+ simba O2X plugin) and connect to mondrian via XMLA.<br>
Most queries are working perfectly (as far as mondrian is concerned) but he's
having a hard time with one particular type of query.<br>
<br>
I have a dimension with days of week (the dimension hierarchy is year/month/day
of week) and I would like to filter so only some day of weeks are used. In
excel, you can add a filter, check the "Select Multiple Items" and
select elements you need.<br>
If I query fact count, min, max, I don't have any problem. If I query an avg
dimension, I get the following exception:<br>
<br>
2010.05.20;11:38:49.892 WARN Mondrian: exception in executeStripe.<br>
mondrian.olap.fun.MondrianEvaluationException: Don't know how to rollup
aggregator 'avg'<br>
at mondrian.olap.fun.FunUtil.newEvalException(FunUtil.java:76)<br>
at
mondrian.olap.fun.AggregateFunDef$AggregateCalc.aggregate(AggregateFunDef.java:99)<br>
at
mondrian.olap.fun.AggregateFunDef$AggregateCalc.evaluate(AggregateFunDef.java:71)<br>
at mondrian.rolap.RolapEvaluator.evaluateCurrent(RolapEvaluator.java:512)<br>
at
mondrian.rolap.RolapResult.executeStripe(RolapResult.java:902)<br>
at
mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1040)<br>
at
mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1040)<br>
at mondrian.rolap.RolapResult.executeBody(RolapResult.java:798)<br>
at
mondrian.rolap.RolapResult.<init>(RolapResult.java:416)<br>
at
mondrian.rolap.RolapConnection.execute(RolapConnection.java:607)<br>
at mondrian.xmla.XmlaHandler.executeQuery(XmlaHandler.java:1668)<br>
at mondrian.xmla.XmlaHandler.execute(XmlaHandler.java:635)<br>
at mondrian.xmla.XmlaHandler.process(XmlaHandler.java:551)<br>
at
mondrian.xmla.impl.DefaultXmlaServlet.handleSoapBody(DefaultXmlaServlet.java:426)<br>
at mondrian.xmla.XmlaServlet.doPost(XmlaServlet.java:329)<br>
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:727)<br>
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:820)<br>
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)<br>
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:389)<br>
at
org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)<br>
at
org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)<br>
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)<br>
at org.mortbay.jetty.Server.handle(Server.java:326)<br>
at
org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:534)<br>
at
org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:879)<br>
at
org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:747)<br>
at
org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)<br>
at
org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)<br>
at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)<br>
at
org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:520)<br>
<br>
The MDX query is:<br>
with member [Date.date_weekday].[XL_QZX] as
'Aggregate({[Date.date_weekday].[2010].[April].[Saturday],
[Date.date_weekday].[2010].[April].[Sunday]})'<br>
select NON EMPTY Hierarchize({DrilldownLevel({[Time.TimeToQuarter].[All
Time.TimeToQuarters]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS<br>
from [Station]<br>
where ([Measures].[AverageFullPercent], [Date.date_weekday].[XL_QZX])<br>
<br>
I don't speak MDX fluently so can anybody confirm that this is a mondrian or an
excel problem?<br>
<br>
I am using the mondrian 3.2.0.13583 but I am willing to try nightly builds.<br>
<br>
Thank you very much, any help is greatly appreciated!<br>
<br>
Manuel<o:p></o:p></p>
</blockquote>
</blockquote>
</div>
</body>
</html>