Multiple Tables in From Clause

[cmoudy]

I have been struggling to get a simple OLAP example to work against our development database after a nice presentation from the JasperSoft folks. When I dig into the error message it turns out the SQL query being generated actually contains the name of my fact table twice in the from clause. Of course Oracle throws an error saying the statement is not properly ended. I have double checked the data source, foreign keys, table names and so forth. Any help is greatly appreciated!

 

Simple Schema definition:

Code:

<Schema name="EdwardSchema">
	<Cube name="QuickClick">
		<Table name="VASDS.SDS_ASSMT_RESULT_QC"/>
		<Dimension name="Grade" foreignKey="GRADE_ID">
  			<Hierarchy hasAll="true" allMemberName="All Grades" primaryKey="GRADE_ID">
    				<Table name="VAMDS.MDS_GRADE"/>
    				<Level name="Grade" column="GRADE_CODE" uniqueMembers="true"/>
  			</Hierarchy>
		</Dimension>        		            
    		<Measure name="Pass Count" column="PASS_CNT" aggregator="sum" formatString="#"/>    		    
	</Cube>
</Schema>

Simple MDX query:

 

Code:

[code] select {[Measures].[Pass Count]} ON COLUMNS,{[Grade].[All Grades]} ON ROWS FROM [QuickClick]

 

Nasty Error Returned:

 

JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.jsp.el.ELException: An error occurred while getting property "result" from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy

 

Digging into the log file a little results in finding the following…

Code:

[code] mondrian.olap.MondrianException: Mondrian Error:Failed to parse query 'select {[Measures].[Pass Count]} ON COLUMNS,{[Grade].[All Grades]} ON ROWS FROM [QuickClick]'

 

However that looks correct still going down the stack trace we see the culprit.

 

Code:

[code] Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while loading segment; sql=[select sum("VASDS"."SDS_ASSMT_RESULT_QC"."PASS_CNT"«») as "m0" from "VASDS"."SDS_ASSMT_RESULT_QC" "VASDS"."SDS_ASSMT_RESULT_QC"]

 

There are two of my fact tables listed in the From cause why???

[sbirney]

Hello,

 

I think Mondrian is trying to generate an alias for the table, and using the original table name to do that. I'm not sure what the point of having an alias is if it is the same as the table name, but perhaps there are cases when it is different.

 

I think this generated SQL should work if you set the tableName in your mondrian schema to "SDS_ASSMT_RESULT_QC" instead of the fully qualified "VASDS.SDS_ASSMT_RESULT_QC".

 

Let's see what happens after that...

 

 

Sam

[cmoudy]

That worked when I used the actual user of the schema! Now the only problem is the dimensions cannot be drilled down into. Does anyone know how to actually get this to work or work arounds for not having to fully qualify the tables?

 

Thanks again sbirney!!! At least now I have something that sort of works even if I can't drill down and have a better understanding of the error!

[sbirney]

great, glad that helped some. In your simple schema example, it seems there is only one dimension with a single level, so it does not surprise me that there are no ability to drill down. Perhaps if you try a dimension with two levels then drill-down will work? If there is an error message, feel free to post it and I'll look. You were correct in your first post to try to find the "caused by:" log statement, it usually seems to lead to the answer.

 

Sam

[cmoudy]

Actually just got it to work with several dimensions. Turns out what I needed to do for a work around was to provide a private synonym for the dimension tables then I don't have to fully qualify the tables. Honestly surprising there wasn't more about this well call it a "bug" out there, but thanks so much for your help.