The SQL that Mondrian generates is, until now, different than the SQL that most people would write by hand. Most people don’t use spaces or punctuation in table and column names, and don’t enclose identifiers in quotation marks when writing SQL DDL, DML or queries. Mondrian, on the other hand, religiously quotes every identifier, whether it needs it or not.
The two styles are not compatible because on many databases (Oracle is one example) unquoted identifiers are implicitly converted to upper-case. If you use lower-case table and column names in Mondrian’s schema, they will not match the upper-case identifiers created during DDL.
For instance, if you create a table in Oracle using
then Oracle creates a table called
EMP with columns
DEPTNO. When you query it using
the effect is as if you had written
Now, if you’d told Mondrian that the table was called “
Mondrian tries to be helpful. It generates the query
Of course, there is no table called “
emp”, only one called “
so on case-sensitive databases such as Oracle this causes an
error. You then need to go back to your schema and change
and all other table and column names in your schema. Yuck!
There is now a simpler way. The Schema XML element has a
If you set
quoteSql='false', Mondrian will not quote identifiers
when generating SQL. (Actually, it will still quote them if they
contain spaces and such. But we recommend that if you use
quoteSql='false', you use sensible table names containing only
alphanumeric characters and ‘_’.)
More details can be found in MONDRIAN-887. It is only fixed in the lagunitas branch (i.e., mondrian-4.0 alpha), and only in new-style schemas (not mondrian-3 style schemas automatically upgraded). Give it a try and let me know how it works for you.