[Mondrian] problem connect to sql server and hsqldb

hadi surya kurniawan narou71185 at gmail.com
Fri Jul 25 02:44:47 EDT 2008


i have a problem with connecting Mondrian to Sql Server 2000 and Hsqldb 1.8.0.8,
i use Apache Tomcat 5.5 as the web server and Mozzila Firefox as the browser
the Mondrian version that i use is Mondrian 2.4.2.9831
here is the detail for each problem with the connection
i do nothing with the FoodMart.xml and mondrian.properties

---------------
SQL Server 2000
---------------
when i connect Mondrian to sql server, it return no error, but when i do
the drill down, Mondrian return a null value in each cell.
i connect the Mondrian with SQL Server via ODBC in system DSN, i also
do the login in the ODBC.
i tested this ODBC with Borland Delphi 7.0, it works fine.
i attached file picture when i run the Mondrian.
this is the mondrian.jsp i edited to connect to SQL Server:
------------------------------------
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

<%-- uses a dataSource --%>
<%-- jp:mondrianQuery id="query01" dataSource="jdbc/MondrianFoodmart"
catalogUri="/WEB-INF/demo/FoodMart.xml" --%>

<%-- uses mysql --%>
<%-- jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/foodmart"
catalogUri="/WEB-INF/queries/FoodMart.xml"--%>

<%-- uses a role defined in FoodMart.xml --%>
<%-- jp:mondrianQuery role="California manager" id="query01"
jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUrl="jdbc:odbc:MondrianFoodMart"
catalogUri="/WEB-INF/queries/FoodMart.xml" --%>

<jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUser="narou" jdbcPassword="071185"
jdbcUrl="jdbc:odbc:MondrianSQLServer"
catalogUri="/WEB-INF/queries/FoodMart.xml">
select
  {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store
Sales]} on columns,
  {([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])
</jp:mondrianQuery>

<c:set var="title01" scope="session">Test Query uses Mondrian OLAP</c:set>
--------------------------------------

--------------
Hsqldb 1.8.0.8
--------------
i use HSQL Database Engine Server in Hsqldb for the database. i
attached the picture file
when i run the Hsqldb server and the manager. Hsqldb has this error:
--------------------------------
org.apache.jasper.JasperException: javax.servlet.jsp.JspException:
javax.servlet.jsp.JspException:
Mondrian Error:Internal error: while building member cache;
sql=[select "time_by_day"."the_year" as
"c0" from "time_by_day" as "time_by_day" group by
"time_by_day"."the_year" order by "time_by_day".
"the_year" ASC]

org.apache.jasper.JasperException: javax.servlet.jsp.JspException:
javax.servlet.jsp.JspException:
Mondrian Error:Internal error: while building member cache;
sql=[select "time_by_day"."the_year" as
"c0" from "time_by_day" as "time_by_day" group by
"time_by_day"."the_year" order by "time_by_day".
"the_year" ASC]
	at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:476)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:371)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
	at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
	at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest(RequestSynchronizer.java:127)
	at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:263)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
	at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
	at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
	at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
	at java.lang.Thread.run(Unknown Source)
-----------------------------------

this is the mondrian.jsp i edited to connect to hsqldb:
-----------------------------------
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

<%-- uses a dataSource --%>
<%-- jp:mondrianQuery id="query01" dataSource="jdbc/MondrianFoodmart"
catalogUri="/WEB-INF/demo/FoodMart.xml" --%>

<%-- uses mysql --%>
<%-- jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/foodmart"
catalogUri="/WEB-INF/queries/FoodMart.xml"--%>

<%-- uses a role defined in FoodMart.xml --%>
<%-- jp:mondrianQuery role="California manager" id="query01"
jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUrl="jdbc:odbc:MondrianFoodMart"
catalogUri="/WEB-INF/queries/FoodMart.xml" --%>

<jp:mondrianQuery id="query01" jdbcDriver="org.hsqldb.jdbcDriver"
jdbcUser="sa" jdbcPassword="" jdbcUrl="jdbc:hsqldb:hsql://localhost/"
catalogUri="/WEB-INF/queries/FoodMart.xml">
select
  {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store
Sales]} on columns,
  {([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])
</jp:mondrianQuery>

<c:set var="title01" scope="session">Test Query uses Mondrian OLAP</c:set>
------------------------------------

i think i make a mistake for jdbcUrl in each mondrian.jsp, please
correct this or maybe
someone can make a simple tutorial for me to connect to each database engine

thank you very much,
Hadi
-------------- next part --------------
A non-text attachment was scrubbed...
Name: hsqldb server and manager.JPG
Type: image/jpeg
Size: 166964 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20080725/9ac30c6a/attachment.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: mondrian null.JPG
Type: image/jpeg
Size: 27996 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20080725/9ac30c6a/attachment-0001.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql server null.JPG
Type: image/jpeg
Size: 89426 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20080725/9ac30c6a/attachment-0002.jpe 


More information about the Mondrian mailing list