<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><HTML><head><META content="text/html; charset=utf-8" http-equiv="Content-Type">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head><BODY>
<DIV>
Hi Manuel.<br>
<br>
You will want to have a look at
<a class="moz-txt-link-freetext" href="http://forums.pentaho.org/showthread.php?t=56603">http://forumspentaho.org/showthread.php?t=56603</a><br>
<br>
The issue is relatively complex to explain, but, the effect is that,
with mondrian, it is not possible to have Measure defined with an 'avg'
aggregation (well, you can define it, but, if mondrian actually has to
use it, then it fails with the exception you are seeing).<br>
<br>
If you really need an 'Average' for a measure then you need to
calculate it in your MDX/mondrian schema using calculated measures of
the form Average = Sum (Measure) / Count(Measure).<br>
<br>
Rolf<br>
<br>
Calum Miller wrote:
<blockquote cite="mid:C96487E0-BB89-4D73-BAAD-E31C64BDBD4C@millersoft.ltd.uk" type="cite">
<pre wrap>Hi Manuel,
Glad you are having such a positive experience with Excel and Mondrian. The error is a Mondrian error and it is complaining about not having a function defined for AVG, which is odd since the function in your query is AGGREGATE. I think you may be missing an MDX query in your log file which references AVG...can you please have a look?
Thanks
Calum
On 20 May 2010, at 16:54, Manuel Darveau wrote:
</pre>
<blockquote type="cite">
<pre wrap>Hi,
I am using excel 2007 (+ simba O2X plugin) and connect to mondrian via XMLA.
Most queries are working perfectly (as far as mondrian is concerned) but he's having a hard time with one particular type of query.
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.
If I query fact count, min, max, I don't have any problem. If I query an avg dimension, I get the following exception:
2010.05.20;11:38:49.892 WARN Mondrian: exception in executeStripe.
mondrian.olap.fun.MondrianEvaluationException: Don't know how to rollup aggregator 'avg'
at mondrian.olap.fun.FunUtil.newEvalException(FunUtil.java:76)
at mondrian.olap.fun.AggregateFunDef$AggregateCalc.aggregate(AggregateFunDefjava:99)
at mondrian.olap.fun.AggregateFunDef$AggregateCalc.evaluate(AggregateFunDef.java:71)
at mondrian.rolap.RolapEvaluator.evaluateCurrent(RolapEvaluator.java:512)
at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:902)
at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1040)
at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1040)
at mondrian.rolap.RolapResult.executeBody(RolapResult.java:798)
at mondrian.rolap.RolapResult.<init>(RolapResult.java:416)
at mondrian.rolap.RolapConnection.execute(RolapConnection.java:607)
at mondrian.xmla.XmlaHandler.executeQuery(XmlaHandler.java:1668)
at mondrian.xmla.XmlaHandler.execute(XmlaHandler.java:635)
at mondrian.xmla.XmlaHandler.process(XmlaHandler.java:551)
at mondrian.xmla.impl.DefaultXmlaServlet.handleSoapBody(DefaultXmlaServlet.java:426)
at mondrian.xmla.XmlaServlet.doPost(XmlaServlet.java:329)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:389)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:534)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:879)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:747)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:520)
The MDX query is:
with member [Date.date_weekday].[XL_QZX] as 'Aggregate({[Date.date_weekday].[2010].[April].[Saturday], [Date.date_weekday].[2010].[April].[Sunday]})'
select NON EMPTY Hierarchize({DrilldownLevel({[Time.TimeToQuarter].[All Time.TimeToQuarters]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
from [Station]
where ([Measures].[AverageFullPercent], [Date.date_weekday].[XL_QZX])
I don't speak MDX fluently so can anybody confirm that this is a mondrian or an excel problem?
I am using the mondrian 3.2.0.13583 but I am willing to try nightly builds.
Thank you very much, any help is greatly appreciated!
Manuel
</pre>
</blockquote>
<pre wrap><!---->
Calum Miller
Business Intelligence Specialist
<a class="moz-txt-link-abbreviated" href="mailto:calum@millersoft.ltd.uk">calum@millersoft.ltd.uk</a>
<a class="moz-txt-link-freetext" href="http://www.millersoft.ltd.uk">http://www.millersoft.ltd.uk</a>
Address: Conference House, 152 Morrison Street, The Exchange, Edinburgh, United Kingdom, EH3 8EB
Location: <a class="moz-txt-link-freetext" href="http://bit.ly/dmDiTr">http://bit.ly/dmDiTr</a>
Telephone: +44 (0) 131 208 0202
Fax: +44 (0) 131 608 0037
Mobile: +44 (0) 7973 249 621
Skype: calummiller
</pre>
</blockquote>
<br>
</DIV>
<DIV> </DIV>
<DIV STYLE="FONT-SIZE: 9pt; FONT-FAMILY: Courier New">
<HR>
<DIV ALIGN="justify" STYLE="FONT-SIZE: 9pt; FONT-FAMILY: Courier New"><FONT FACE="Arial" SIZE="1">This email and any files transmitted with it are confidential and proprietary to Algorithmics Incorporated and its affiliates ("Algorithmics"). If received in error, use is prohibited. Please destroy, and notify sender. Sender does not waive confidentiality or privilege. Internet communications cannot be guaranteed to be timely, secure, error or virus-free. Algorithmics does not accept liability for any errors or omissions. Any commitment intended to bind Algorithmics must be reduced to writing and signed by an authorized signatory.</FONT></DIV>
<HR>
<DIV></DIV></DIV></BODY></HTML>