[Mondrian] Excel + MondrianEvaluationException: Don't know how torollup aggregator 'avg'

Julian Hyde jhyde at pentaho.com
Thu May 20 16:28:57 EDT 2010


It's Mondrian problem, not specific to Excel.
 
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.
 
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.
 
There is a feature request to make mondrian smart enough to do this
automatically:  <http://jira.pentaho.com/browse/MONDRIAN-675>
http://jira.pentaho.com/browse/MONDRIAN-675
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Manuel Darveau
Sent: Thursday, May 20, 2010 8:54 AM
To: mondrian at pentaho.org
Subject: [Mondrian] Excel + MondrianEvaluationException: Don't know how
torollup aggregator 'avg'


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(AggregateFunDef.ja
va:99)
    at
mondrian.olap.fun.AggregateFunDef$AggregateCalc.evaluate(AggregateFunDef.jav
a: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:22
8)
    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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100520/1e484ad2/attachment.html 


More information about the Mondrian mailing list