[Mondrian] Re: Excel + MondrianEvaluationException: Don't know how to rollup aggregator 'avg'

Rolf Lear rolf.lear at algorithmics.com
Thu May 20 12:59:19 EDT 2010


Hi Manuel.

You will want to have a look at 
http://forums.pentaho.org/showthread.php?t=56603

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).

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).

Rolf

Calum Miller wrote:
> 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:
>
>   
>> 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
>>     
>
> Calum Miller
> Business Intelligence Specialist
> calum at millersoft.ltd.uk
> http://www.millersoft.ltd.uk
> Address: Conference House, 152 Morrison Street, The Exchange, Edinburgh, United Kingdom, EH3 8EB 
> Location: http://bit.ly/dmDiTr
> Telephone: +44 (0) 131 208 0202
> Fax: +44 (0) 131 608 0037
> Mobile: +44 (0) 7973 249 621
> Skype: calummiller
>   


 
--------------------------------------------------------------------------
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.
--------------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100520/004bbb8b/attachment.html 


More information about the Mondrian mailing list