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

Manuel Darveau manueldarveau at gmail.com
Fri May 21 10:42:58 EDT 2010


All,

Thank you very much for the answer. I created two measures and a calculated
member and it worked:
    <Measure name="AverageFullPercent_sum" column="fullDockPercent"
datatype="Numeric" aggregator="sum" visible="false">
    </Measure>
    <Measure name="AverageFullPercent_count" column="fullDockPercent"
datatype="Numeric" aggregator="count" visible="false">
    </Measure>
    <CalculatedMember name="AverageFullPercentCalc" caption="Average Full
Percent Calculated"
formula="[Measures].[AverageFullPercent_sum]/[Measures].[AverageFullPercent_count]"
dimension="Measures" visible="true">
        <CalculatedMemberProperty  name="FORMAT_STRING" value="#0.00%"/>
    </CalculatedMember>

Note that the format part does not seem to work with excel. I don't care
that much but do you have any idea why?

Thank you

Manuel Darveau

On Thu, May 20, 2010 at 4:28 PM, Julian Hyde <jhyde at pentaho.com> wrote:

>  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
>
> 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.java: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
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100521/defdd53e/attachment.html 


More information about the Mondrian mailing list