[Mondrian] Dimension filter and Excel + Simba O2X

Julian Hyde jhyde at pentaho.com
Fri Feb 5 14:03:28 EST 2010


That 'UnsupportedOperationException: getOrdinal' error clearly indicates
that this is  <http://jira.pentaho.com/browse/MONDRIAN-678>
http://jira.pentaho.com/browse/MONDRIAN-678.


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Manuel Darveau
Sent: Friday, February 05, 2010 6:55 AM
To: mondrian at pentaho.org
Subject: [Mondrian] Dimension filter and Excel + Simba O2X


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
<?xml version="1.0" encoding="UTF-8"?><Envelope
xmlns="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Header>
        <Session xmlns="urn:schemas-microsoft-com:xml-analysis" SessionId=""
mustUnderstand="1"/>
    </Header>
    <Body>
        <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
            <Command>
                <Statement>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</Statement>
            </Command>
            <Properties>
                <PropertyList>
                    <Format>Multidimensional</Format>
                    <AxisFormat>TupleFormat</AxisFormat>
 
<DataSourceInfo>Provider=Mondrian;DataSource=bixicube;</DataSourceInfo>
                    <LocaleIdentifier>1033</LocaleIdentifier>
                    <Catalog>bixicube</Catalog>
                    <VisualMode>1</VisualMode>
                </PropertyList>
            </Properties>
        </Execute>
    </Body>
</Envelope>
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:22
8)
    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(VisualTota
lsFunDef.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.j
ava:56)
    at
mondrian.calc.impl.AbstractExpCompiler$MemberListIterCalc.evaluateMemberIter
able(AbstractExpCompiler.java:569)
    at
mondrian.calc.impl.AbstractMemberIterCalc.evaluate(AbstractMemberIterCalc.ja
va: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.<init>(RolapResult.java:273)
    at mondrian.rolap.RolapConnection.execute(RolapConnection.java:604)
    ... 20 more
02.05;09:41:58.653 DEBUG XML/A response content
<?xml version="1.0" encoding="ISO-8859-1"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" >
<SOAP-ENV:Header>
<Session SessionId="" xmlns="urn:schemas-microsoft-com:xml-analysis"
/></SOAP-ENV:Header>
<SOAP-ENV:Body>
<SOAP-ENV:Fault>
  <faultcode>SOAP-ENV:Server.00HSBD02</faultcode>
  <faultstring>XMLA MDX execute failed</faultstring>
  <faultactor>Mondrian</faultactor>
  <detail>
    <XA:error xmlns:XA="http://mondrian.sourceforge.net">
      <code>00HSBD02</code>
      <desc>The Mondrian XML: java.lang.UnsupportedOperationException</desc>
    </XA:error>
  </detail>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100205/3e11382e/attachment.html 


More information about the Mondrian mailing list