[Mondrian] problem with connect to sql server and hqldb

hadi ganteng narou71185 at yahoo.com
Sun Jul 20 00:05:20 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
i do nothing with the FoodMart.xml and mondrian.properties

here is the detail for each problem with the connection

---------------
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 each jdbcUrl in the 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 --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20080719/c2c22fdc/attachment.html 
-------------- 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/20080719/c2c22fdc/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/20080719/c2c22fdc/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/20080719/c2c22fdc/attachment-0002.jpe

More information about the Mondrian mailing list