PDA

View Full Version : MDX parse errors.



kmuglurmath
07-28-2003, 10:22 AM
Hi,
I got the demo application working completely on Oracle 9i and mondrian 0.6. I now am trying to get it to work on a simple table of my own. The fact and dimensions are part of the same, single table. I have a table called h_mv with the following fields:
hb.factnumber NOT NULL NUMBER(38)
hls.day NOT NULL NUMBER(38)
hls.month NOT NULL NUMBER(38)
hls.year NOT NULL NUMBER(38)
hls.week NOT NULL NUMBER(38)
hls.weekdesc NOT NULL VARCHAR2(50)
hls.quarter NOT NULL NUMBER(38)
hc.name NOT NULL VARCHAR2(255)
ha.anumber NOT NULL VARCHAR2(50)
hi.name NOT NULL VARCHAR2(50)
I used the foodmart.xml as a reference, and created a mytest.xml file, which is as follows:
<?xml version="1.0"?>
<Schema name="dwdba">
<Cube name="Activities">
<Table name=h_mv/>
<Dimension name="Time">
<Hierarchy hasAll="true" allMemberName="All Times">
<Level name="Year" column="hls.year" type="Numeric" uniqueMembers="true"/>
<Level name="Quarter" column="hls.quarter" uniqueMembers="false" type="Numeric"/>
<Level name="Month" column="hls.month" uniqueMembers="false" type="Numeric"/>
<Level name="Week" column="hls.weekdesc" uniqueMembers="false" type="String"/>
<Level name="Day" column="hls.day" uniqueMembers="false" type="Numeric"/>
</Hierarchy>
</Dimension>
<Dimension name="HC">
<Hierarchy hasAll="true" allMemberName="All HCs">
<Level name="HC" column="hc.name" type="String" uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="AP">
<Hierarchy hasAll="true" allMemberName="All APs">
<Level name="AP" column="ha.number" type="String" uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="HI">
<Hierarchy hasAll="true" allMemberName="All HIs">
<Level name="HI" column="hi.name" type="String" uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Measure name="Ls" column="hb.factnumber" aggregator="count" formatString="#,#" />
</Cube>
</Schema>
Using WEB-INF/queries/mondrian.jsp as a reference, I have WEB-INF/queries/myquery.jsp, which has:
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="
http://www.tonbeller.com/jpivot/mondrian
" prefix="mondrian" %>
<%@ taglib prefix="c" uri="
http://java.sun.com/jstl/core
" %>
<mondrian:query id="query01" jdbcDriver="oracle.jdbc.OracleDriver" jdbcUrl="jdbc:oracle:thin:user/password@server:dbname" catalogUri="/WEB-INF/queries/mytest.xml">
select
{[Time].[All Times]} ON columns,
{[Measures].[Ls]} on rows
from Activities
</mondrian:query>
<c:set var="title01" scope="session">Test Query</c:set>
Now, when I call testpage.jsp?query=myquery, I get a
mondrian.xom.XOMException: Document parse failed: [Fatal Error] :5:-1: Value must be quoted.
at mondrian.xom.wrappers.GenericDOMParser.handleErrors(GenericDOMParser.java:134)
at mondrian.xom.wrappers.JaxpDOMParser.parseInputSource(JaxpDOMParser.java:72)
at mondrian.xom.wrappers.GenericDOMParser.parse(GenericDOMParser.java:54)
at mondrian.xom.wrappers.GenericDOMParser.parse(GenericDOMParser.java:116)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:88)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:34)
at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:247)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:125)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:53)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:122)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:53)
at com.tonbeller.jpivot.mondrian.MondrianModel.initialize(MondrianModel.java:185)
at com.tonbeller.jpivot.olap.model.OlapModelDecorator.initialize(OlapModelDecorator.java:109)
at com.tonbeller.jpivot.olap.model.OlapModelProxy.setDelegate(OlapModelProxy.java:44)
at com.tonbeller.jpivot.olap.model.OlapModelProxy.setOlapModel(OlapModelProxy.java:33)
at com.tonbeller.jpivot.olap.OlapModelTag.doAfterBody(OlapModelTag.java:33)
at org.apache.jsp.housing_jsp._jspx_meth_mondrian_query_0(housing_jsp.java:94)
at org.apache.jsp.housing_jsp._jspService(housing_jsp.java:57)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
error.
I cannot figure out where this is coming from. Could you please help?
Thanks.

jhyde
07-28-2003, 12:21 PM
The problem is the table name:
<Table name=h_mv/>
In XML (unlike HTML) attribute values must be quoted.
By the way, an easy way way to validate XML is to open the file in Internet Explorer. That's how I found this error.

kmuglurmath
07-29-2003, 03:38 AM
Thanks. After quoting the tablename, I get "table or view not found" error from Oracle. It turns out that all tables must be created in Oracle using quotes. So if the table was created using
create table h_mv ....
it should now be created using the statement
create table "h_mv" ...
This is not a problem for newly created warehouses, but how can I resolve it in databases that have tables already created and tablenames cannot be altered?

jhyde
07-29-2003, 09:32 AM
Here's how it (namely, Oracle) works.
Oracle is normally case insensitive. If you write 'create table foo' or 'create table FOO', you will end up with a table called 'FOO'.
You can make Oracle case sensitive by using double-quotes. If you write 'create table "Foo"' you will get a table called 'Foo'. (Using double-quotes, you can even include spaces in table names.)
Mondrian is case sensitive (because XML is case sensitive). If you have a table called 'H_MV', you have to write '<table name="H_MV"/>' in the Mondrian catalog.
There are several ways around those rules, but my recommendation is that you always double-quote table and column names in both Oracle DDL statements and in Mondrian XML files. It's up to you whether you prefer upper or lower case table names.

kmuglurmath
07-30-2003, 08:00 AM
That worked. Thanks for your help!