MDX Query against Oracle

[fpohlman]

Greetings from Germany.

Thank you for creating such a great framework. I really get upset when I'm thinking about all the possibilities.

Not sure if this is the right place to ask my question, but there is no "Analysis Issues" forum.

I am trying to run a MDX Query against an Oracle9i Database. The action (only the component) im using is defined like this:

Code snipped and attached as file

(User-id, password and connection not shown here for security reasons).

Here are the errors I get:



Failed
--------------------------------------------------------------------------------

[de_72] Error: [de_103] MDXBaseComponent.ERROR_0006 - !MDXBaseComponent.ERROR_0006_EXECUTE_FAILED! (org.pentaho.component.MDXLookupRule)
[de_69] Debug: [de_21] Starting execute of samples/datasources/PredictionMart_Datasource.action.xml (org.pentaho.solution.SolutionEngine)
[de_69] Debug: [de_30] Getting runtime context and data (org.pentaho.solution.SolutionEngine)
[de_69] Debug: [de_33] Loading action sequence definition file (org.pentaho.solution.SolutionEngine)
[de_69] Debug: [de_34] audit: instanceId=acba9cfd-4d59-11da-8034-8161ff8e2d5b, objectId=org.pentaho.runtime.RuntimeContext, messageType=action_sequence_start (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_1] validateComponent validating component org.pentaho.component.MDXLookupRule (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_75] Validating component for action PredictionMart_Datasource.action.xml (org.pentaho.component.MDXLookupRule)
[de_69] Debug: [de_42] Executing action sequence (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_24] Executing action definition: Iteration {0}0 (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_34] audit: instanceId=acba9cfd-4d59-11da-8034-8161ff8e2d5b, objectId=org.pentaho.component.MDXLookupRule, messageType=component_execution_started (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_3] execute pre-audit (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_21] Setting component log level to DEBUG (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_11] Initializing component (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_4] executeComponent starting audited execute (org.pentaho.runtime.RuntimeContext)
[de_69] Debug: [de_94] execute validation=true (org.pentaho.component.MDXLookupRule)
[de_72] Error: [de_103] MDXBaseComponent.ERROR_0006 - !MDXBaseComponent.ERROR_0006_EXECUTE_FAILED! (org.pentaho.component.MDXLookupRule)
[de_69] Debug: [de_15] executeComponent finished audited execute (org.pentaho.runtime.RuntimeContext)
[de_72] Error: [de_103] RuntimeContext.ERROR_0012 - [de_33] ActionDefinition for org.pentaho.component.MDXLookupRule did not execute successfully (org.pentaho.runtime.RuntimeContext)
[de_72] Error: [de_103] SolutionEngine.ERROR_0007 - [de_27] Action sequence execution failed (org.pentaho.solution.SolutionEngine)



And from server.log


2005-11-04 18:37:30,780 ERROR [org.pentaho.component.MDXLookupRule] acba9cfd-4d59-11da-8034-8161ff8e2d5b:COMPONENT:context-377695-1131125850619:«»PredictionMart_Datasource.action.xml [de_103] MDXBaseComponent.ERROR_0006 - !MDXBaseComponent.ERROR_0006_EXECUTE_FAILED!
mondrian.olap.MondrianException: Mondrian Error:Internal error: while connecting to provider=oracle.jdbc.driver.OracleDriver; Jdbc=jdbc:oracle:oci:@instance-name; Catalog=file:/D:/java/pentaho_demo-0.9.8.1/pentaho-demo/pentaho-solutions/samples/datasources/PredictionMart.xml; user=user; password=pass
at mondrian.olap.MondrianResource.newInternal(MondrianResource.java:45)
at mondrian.olap.DriverManager.getAdomdConnection(DriverManager.java:113)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:167)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:143)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:55)
at org.pentaho.connection.mdx.MDXConnection.init(Unknown Source)
at org.pentaho.connection.mdx.MDXConnection.(Unknown Source)
at org.pentaho.connection.PentahoConnectionFactory.getConnection(Unknown Source)
at org.pentaho.component.MDXBaseComponent.getConnection(Unknown Source)
at org.pentaho.component.MDXBaseComponent.getDatasourceConnection(Unknown Source)
at org.pentaho.component.MDXBaseComponent.executeAction(Unknown Source)
at org.pentaho.component.ComponentBase.execute(Unknown Source)
at org.pentaho.runtime.RuntimeContext.executeComponent(Unknown Source)
at org.pentaho.runtime.RuntimeContext.executeAction(Unknown Source)
at org.pentaho.runtime.RuntimeContext.executeLoop(Unknown Source)
at org.pentaho.runtime.RuntimeContext.executeSequence(Unknown Source)
at org.pentaho.runtime.RuntimeContext.executeSequence(Unknown Source)
at org.pentaho.solution.SolutionEngine.execute(Unknown Source)
at org.pentaho.services.BaseRequestHandler.handleActionRequest(Unknown Source)
at org.pentaho.ui.servlet.ViewAction.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(Unknown Source)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(Unknown Source)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(Unknown Source)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(Unknown Source)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.lang.ClassNotFoundException: Broadbase.mdx.adomd.AdomdConnection
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1332)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1181)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)
at mondrian.olap.DriverManager.getAdomdConnection(DriverManager.java:86)
... 39 more

Some more information regarding my environment:

Pentaho-Demo 0.9.8.1
Oracle 9iR2 (not running on my machine)
Windows NT

The same connection parameters are used successfully with a SQLLookupRule, so the Oracle JDBC Driver is usable (I copied it $PENTAHO_HOME/pentaho-demo/jboss/server/default/lib).

The root cause of the exception above seems like my CLASSPATH is not set up correctly or that the mondrian configuration has to be changed.

Can someone explain what I am missing here?

Thanks in advance

Florian

Post edited by: fpohlman, at: 11/07/2005 03:22

Post edited by: fpohlman, at: 11/07/2005 05:26

Post edited by: fpohlman, at: 11/07/2005 05:28

Post edited by: fpohlman, at: 11/07/2005 05:30

[fpohlman]

Here is the missing action file. http://forums.pentaho.org/archived_att/files/PredictionMart.action.txt

[wseyler]

Hi Florian,

Thanks for evaluating the Pentaho BI platform.

I did a little research into the problem your having and it brought out an issue with the MDXLookupRule class. When I coded it I didn't make it granular enough to allow passing in of the JDBC driver. We've opened a JIRA case for that and I will work on a fix.

Good new is I think I may have a work-around for you. Try replacing your actions with the attached snippet

Hopefully the JDBC driver will be added to the Mondrian connection string and allow you access to your data.

The bad news is I haven't tested this configuration since I don't have access to and Oracle datasource. Please let me know if this works for you. In the next release you should see better support for passing in JDBC driver information.

Good Luck,

Bill http://forums.pentaho.org/archived_att/files/ActionSnippet.txt

Post edited by: wseyler, at: 11/07/2005 15:27

[fpohlman]

Hi Bill,

thank you for working on my problem. This morning I read all the sources (MDXLookupRule and the appropriate mondrian sources) and found that "mondrian" as a location tag value is my choice. Right now I have some problems because our Oracle instance is running under a Real Application Cluster and normally I have to use a 9i jdbc oci driver to connect to it. I got it working with the 8i driver (OCI connect string, not thin) supplied with mondrian but the 9i driver would be better because of some 9i features are only usable with the appropriate driver.

I will try your solution tomorrow morning. As far as I figured it out the drivers are hardcoded into mondrian (configuration)?!? I'll get into that tomorrow, if you need some testing on Oracle databases feel free to contact me.

Thank you all for your great work.

Regards
Florian

[papi]

Tested...it worked!