Mondrian saiku - vertica query translation error

Question

Hi trying to use saiku with vertica.

Vertica has the concept of db -> schemas -> tables. So in the xml file, instead of the table name, I am giving schemaName.tableName

<?xml version="1.0"?>
<Schema name="Sales" metamodelVersion='3.6' quoteSql='false'>
<Cube name="Sales" defaultMeasure="sales">
<Table name="schemaName.factName"></Table>
  <Dimension name="date_mysql">
    <Hierarchy hasAll="true">
      <Level name="date" column="date" type="Date" uniqueMembers="false"/>
    </Hierarchy>
  </Dimension>
  <Measure name="sales" aggregator="sum" column="sales" formatString="#,###" />
  <Measure name="orders" aggregator="sum" column="orders" formatString="#,###" />
</Cube>
</Schema>

This seem to work, and mondrian is able to pick up the measure and dimension properly. The problem is the SQL query generated is syntactically wrong

select "schemaName"."tableName"."date" as "c0" 
from "schemaName"."tableName" as "schemaName"."tableName" 
group by "schemaName"."tableName"."date" 
order by CASE WHEN "schemaName"."tableName"."date" IS NULL THEN 1 ELSE 0 END, "schemaName"."tableName"."date" ASC

There are two problems here.

Vertica treats double quotes as any other character and hence "tableName" and tableName are distinct. ( quoteSql='false' doesnt work as Iam using metamodel 3.6)
Mondrian seems to generate alias from the the table name specified (which here is schema.table) which goes wrong here.

Is there any other way to mention schema? And how to get rid of the double quotes?

amrk7 amrk7 · Accepted Answer · 2014-02-13T06:05:41

The table tag carries a schema attribute as well.(Thanks to Paul Stoellberger for pointing out) So

<Table name="factName" schema="schemaName"></Table>

This takes care of the dialect and quoting problems

http://mondrian.pentaho.com/documentation/xml_schema.php#Table

Mondrian saiku - vertica query translation error

1 Answers