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

Sergey Mazin sergey.mazin at skype.net
Sun Jun 20 15:28:57 EDT 2010


 

 

From: Sergey Mazin [mailto:sergey.mazin at skype.net] 
Sent: Sunday, June 20, 2010 10:02 PM
To: 'jhyde at pentaho.com'
Subject: RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'

 

Julian,

 

I am talking about that issue, when measure has "AVG" aggregator in schema
definition:

 

"

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' "

 

I did small changes and now Mondrian calculates AVG measure exactly as
DISTINCT COUNT measures, sending separate query for each level.

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.

 

Regards,

Sergey

 

From: Julian Hyde [mailto:jhyde at pentaho.com] 
Sent: Saturday, June 19, 2010 4:04 AM
To: 'Sergey Mazin'; 'Mondrian developer mailing list'
Subject: RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'

 

Sergey,

 

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

 

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?

 

Julian

 

  _____  

From: Sergey Mazin [mailto:sergey.mazin at skype.net] 
Sent: Wednesday, June 16, 2010 3:06 PM
To: jhyde at pentaho.com; 'Mondrian developer mailing list'
Subject: RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'

Hi Julian,

 

Once MONDRIAN-682 issue is fixed we can have the same logic for "avg"
measure as for "distinct-count" measure.

We send separate query with avg aggregate function for each level in the
query.

 

I changed 3 lines in the code and now it seems to work for me.

 

Regards,

Sergey

 

 

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
On Behalf Of Julian Hyde
Sent: Thursday, May 20, 2010 11:29 PM
To: 'Mondrian developer mailing list'
Subject: RE: [Mondrian] Excel + MondrianEvaluationException: Don't know
how torollup aggregator 'avg'

 

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.
java:99)
    at
mondrian.olap.fun.AggregateFunDef$AggregateCalc.evaluate(AggregateFunDef.j
ava: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.ja
va: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.jav
a: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:5
20)

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/20100620/955422f4/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: AggregateFunDef.java
Type: text/java
Size: 19350 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20100620/955422f4/attachment.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: RolapAggregator.java
Type: text/java
Size: 8890 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20100620/955422f4/attachment-0001.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: RolapEvaluator.java
Type: text/java
Size: 33295 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20100620/955422f4/attachment-0002.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: RolapResult.java
Type: text/java
Size: 77133 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20100620/955422f4/attachment-0003.bin 


More information about the Mondrian mailing list