[Mondrian] Dimension filter and Excel + Simba O2X

tom at wamonline.org.uk tom at wamonline.org.uk
Fri Feb 5 10:02:10 EST 2010 

  BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px;
}Manuel we are doing a simba eval at the moment and get the same
error when querying a dimension thats constructed from a case
when....  
 Haven't yet filed a bug report... I dunno who's fault it is ;)
 Tom
 On Fri  5/02/10  2:55 PM , Manuel Darveau manueldarveau at gmail.com
sent:
 Hi,
 I am using Excel 2007 with the Simba O2X plugin.
 I have a pivot table on "Rentals" with only one count mesure
("RentalsCount") and a dimension called "Subscription". The possible
values for the "Subscription" dimension are "Casual", "Subscriber"
and "Unknown".
 If I simply add the dimension, I get a correct result. If I add a
filter only on one item of the dimension (say "Casual"), I get a
correct result. However, if I filter on two items of the dimension
(say "Casual" and "Subscriber"), I get a popup stating that the query
did not run.
 On the server side I activated some debug and got the following
(sorry for the log dump but I tought that it would be usefull):
 02.05;09:41:58.626 DEBUG XML/A request content
                 WITH  SET [XL_Row_Dim_0] AS
'VisualTotals(Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual]), Ascendants([Subscription].[All
Subscriptions].[Subscriber]), Descendants([Subscription].[All
Subscriptions].[Subscriber])})))'  SELECT NON EMPTY
Hierarchize(Intersect({DrilldownLevel({[Subscription].[All
Subscriptions]})}, [XL_Row_Dim_0])) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS  FROM [Rentals] WHERE
([Measures].[RentalsCount]) CELL PROPERTIES VALUE
                     Multidimensional
                     TupleFormat
                     Provider=Mondrian;DataSource=bixicube;
                     1033
                     bixicube
                     1
 02.05;09:41:58.646 DEBUG 1: with set [XL_Row_Dim_0] as
'VisualTotals(Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual]), Ascendants([Subscription].[All
Subscriptions].[Subscriber]), Descendants([Subscription].[All
Subscriptions].[Subscriber])})))'
 select NON EMPTY
Hierarchize(Intersect({DrilldownLevel({[Subscription].[All
Subscriptions]})}, [XL_Row_Dim_0])) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS
 from [Rentals]
 where [Measures].[RentalsCount]
 02.05;09:41:58.651 DEBUG 1: exec: 4 ms
 02.05;09:41:58.651 ERROR Errors when handling XML/A message
 mondrian.xmla.XmlaException: Mondrian Error:XMLA MDX execute failed
     at mondrian.xmla.XmlaHandler.executeQuery(XmlaHandler.java:1695)
     at mondrian.xmla.XmlaHandler.execute(XmlaHandler.java:642)
     at mondrian.xmla.XmlaHandler.process(XmlaHandler.java:552)
     at
mondrian.xmla.impl.DefaultXmlaServlet.handleSoapBody(DefaultXmlaServlet.java:411)
     at mondrian.xmla.XmlaServlet.doPost(XmlaServlet.java:311)
     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)
 Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal
error: Error while executing query [with set [XL_Row_Dim_0] as
'VisualTotals(Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual]), Ascendants([Subscription].[All
Subscriptions].[Subscriber]), Descendants([Subscription].[All
Subscriptions].[Subscriber])})))'
 select NON EMPTY
Hierarchize(Intersect({DrilldownLevel({[Subscription].[All
Subscriptions]})}, [XL_Row_Dim_0])) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS
 from [Rentals]
 where [Measures].[RentalsCount]
 ]
     at
mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:816)
     at mondrian.olap.Util.newInternal(Util.java:1492)
     at mondrian.olap.Util.newError(Util.java:1508)
     at
mondrian.rolap.RolapConnection.execute(RolapConnection.java:633)
     at mondrian.xmla.XmlaHandler.executeQuery(XmlaHandler.java:1691)
     ... 19 more
 Caused by: java.lang.UnsupportedOperationException
     at
mondrian.olap.fun.VisualTotalsFunDef$VisualTotalMember.getOrdinal(VisualTotalsFunDef.java:246)
     at
mondrian.olap.fun.FunUtil.compareSiblingMembers(FunUtil.java:1854)
     at
mondrian.olap.fun.FunUtil.compareHierarchically(FunUtil.java:1806)
     at
mondrian.olap.fun.FunUtil$HierarchizeComparator.compare(FunUtil.java:2841)
     at
mondrian.olap.fun.FunUtil$HierarchizeComparator.compare(FunUtil.java:2834)
     at java.util.Arrays.mergeSort(Arrays.java:1270)
     at java.util.Arrays.sort(Arrays.java:1210)
     at java.util.Collections.sort(Collections.java:159)
     at
mondrian.olap.fun.FunUtil.hierarchizeMemberList(FunUtil.java:735)
     at
mondrian.olap.fun.HierarchizeFunDef$1.evaluateMemberList(HierarchizeFunDef.java:56)
     at
mondrian.calc.impl.AbstractExpCompiler$MemberListIterCalc.evaluateMemberIterable(AbstractExpCompiler.java:569)
     at
mondrian.calc.impl.AbstractMemberIterCalc.evaluate(AbstractMemberIterCalc.java:52)
     at mondrian.rolap.RolapResult.executeAxis(RolapResult.java:731)
     at mondrian.rolap.RolapResult.evalLoad(RolapResult.java:574)
     at mondrian.rolap.RolapResult.loadMembers(RolapResult.java:540)
     at mondrian.rolap.RolapResult.(RolapResult.java:273)
     at
mondrian.rolap.RolapConnection.execute(RolapConnection.java:604)
     ... 20 more
 02.05;09:41:58.653 DEBUG XML/A response content
   SOAP-ENV:Server.00HSBD02
   XMLA MDX execute failed
   Mondrian
       00HSBD02
       The Mondrian XML: java.lang.UnsupportedOperationException
 I just upgraded to mondrian 3.1.5.13307 (from 3.1.1.12687) since I
had other issues with filters probably related to
http://jira.pentaho.com/browse/MONDRIAN-295 [4] which is now fixed.
My guess is that excel generates MDX that mondrian does not expect
but unfortunately, I can't modify the generated MDX.
 Is there anything else I can do to help diagnose the problem?
 Thank you very much, your support if greatly appreciated.
 Manuel Darveau


Links:
------
[1] http://www.w3.org/2001/XMLSchema
[2] http://www.w3.org/2001/XMLSchema-instance
[3] http://schemas.xmlsoap.org/soap/encoding/
[4] http://jira.pentaho.com/browse/MONDRIAN-295
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100205/9ad65b2f/attachment.html

More information about the Mondrian mailing list