[Mondrian] Dimension filter and Excel + Simba O2X

Sun Feb 7 20:37:40 EST 2010

We're working to get a Hudson build of mondrian-3.2 going. It just isn't
quite up yet. Sorry about that -- and thanks Calum for doing this build as a
stopgap.

Julian

  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Calum Miller
Sent: Sunday, February 07, 2010 4:05 PM
To: Mondrian developer mailing list
Subject: Re: [Mondrian] Dimension filter and Excel + Simba O2X

Hi Manuel, 

I've placed a copy of mondrian.jar from build 13343 on my server
http://www.millersoft.ltd.uk/mondrian.jar
You may also find this video helpful, which explains how to connect Simba up
to Mondrian: http://bit.ly/c6zYSS

Calum

On 5 Feb 2010, at 19:46, Manuel Darveau wrote:

Hi,

I would like to try the 3.2 branch but it does not seems to be built by
http://ci.pentaho.com and I can't find the SVN repo.
Could you send me the SVN repository URL or a jar of the mondrian 3.2
branch?

Thank you

Manuel

On Fri, Feb 5, 2010 at 10:21 AM, Sergey Mazin <sergey.mazin at skype.net>
wrote:

Hi folks,

I think that is the same issue you are facing with.

http://jira.pentaho.com/browse/MONDRIAN-678

As Calum suggested pick Mondrian 3.2 from SVN. Build 13343 is more or less
stable.

Regards,

Sergey

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Calum Miller
Sent: Friday, February 05, 2010 5:11 PM
To: tom at wamonline.org.uk; Mondrian developer mailing list
Subject: Re: [Mondrian] Dimension filter and Excel + Simba O2X

Hi Tom/Manuel,

The latest Excel/Simba support is in the Mondrian 3.2 branch. I'd be
interested to learn of any issues you experience with Excel using this
branch?

Calum

On 5 Feb 2010, at 15:02, tom at wamonline.org.uk wrote:

Manuel we are doing a simba eval at the moment and get the same error when
querying a dimension thats constructed from a <sql>case when.... </sql> 

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
http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="
<http://www.w3.org/2001/XMLSchema-instance>
http://www.w3.org/2001/XMLSchema-instance">

                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: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.(RolapResult.java:273)
    at mondrian.rolap.RolapConnection.execute(RolapConnection.java:604)
    ... 20 more
02.05;09:41:58.653 DEBUG XML/A response content

http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" >

  SOAP-ENV:Server.00HSBD02
  XMLA MDX execute failed
  Mondrian

    http://mondrian.sourceforge.net">
      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 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

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian

Calum Miller

Business Intelligence Specialist

calum at millersoft.ltd.uk

http://www.millersoft.ltd.uk

Telephone: +44 (0) 131 208 0202

Mobile: +44 (0) 7973 249 621

Skype: calummiller

<image001.jpg>

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian

Calum Miller
Business Intelligence Specialist
calum at millersoft.ltd.uk
http://www.millersoft.ltd.uk
Telephone: +44 (0) 131 208 0202
Mobile: +44 (0) 7973 249 621
Skype: calummiller

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100207/97beea9d/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 8712 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20100207/97beea9d/attachment.jpe 