Saiku 3 standalone with PostgreSQL 9.1 - Mondrian Error on Select in database schema different then PUBLIC

[Fizard]

I'm using the latest Saiku 3.8.3 standalone and I'm having some problems using the database PostgreSQL 9.1 with different schemas then PUBLIC.

I made the datasource and it connected correctly, here is the data source I'm using without personal details:

type=OLAP
name=chegadaturistas
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:postgresql://DBNAME:PORT/DB;Catalog=mondrian:///datasources/chegada_turistas.xml;JdbcDrivers=org.postgresql.Driver;
username=postgres
password=PASSWORD
security.enabled=false

I've also created the Mondrian Schema with Legacy option cause I've used Schema Workbench and it seems to be normal without any problem. Here is part of it for an example

<Schema name="DEPES" description="DEPES">
  <Cube name="CHEGADAS" visible="true" cache="true" enabled="true">
    <Table name="chegada" schema="fato" alias="chegada">
    </Table>
    <Dimension type="StandardDimension" visible="true" foreignKey="id_tempo" highCardinality="false" name="Ano">
      <Hierarchy name="ANO" visible="true" hasAll="true" allMemberName="Todos os Anos" primaryKey="id_tempo">
        <Table name="tempo" schema="dimensao" alias="Tempo">
        </Table>
        <Level name="ANO" visible="true" column="nu_ano" nameColumn="nu_ano" ordinalColumn="nu_ano" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="id_origem" highCardinality="false" name="Origem">
      <Hierarchy name="Origem" visible="true" hasAll="true" allMemberName="Todas as Origens" primaryKey="id_origem">
        <Table name="origem" schema="dimensao">
        </Table>
        <Level name="Continente" visible="true" column="no_continente" nameColumn="no_continente" ordinalColumn="no_continente" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
        <Level name="Pais" visible="true" column="no_pais" nameColumn="no_pais" ordinalColumn="no_pais" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
      </Hierarchy>
    </Dimension>

......

The problem is, when I run Saiku I can't see the schema and it has this error in log:

2016-04-08 08:17:31,976 WARN  [mondrian.rolap.RolapSchema] Model is in legacy format
2016-04-08 08:17:36,725 ERROR [org.saiku.web.core.SecurityAwareConnectionManager] Error connecting: chegadaturistas
mondrian.olap.MondrianException: Mondrian Error:Internal error: Reading row count from table [null, null, tempo]; sql=[select count(*) from "tempo"]

So I noticed the select used by mondrian in the COUNT was with just the table and not the schema.table ("dimensao"."tempo" in this case instead of only "tempo") that should be used on PostgreSQL. To get sure I created a VIEW in PostgreSQL public schema with the name "tempo" and it didn't give me this error in the "tempo" table, but gave me on another. Is there a way to force Mondrian to use the SCHEMA.TABLE on the selects for PosgreSQL instead of using only the PUBLIC schema?

[cristia...@gmail.com]

Until that, the only way I resolved this "problem" was changing all schemas to PUBLIC. This is not a solution for me cause it seems bad a tool that doesn't do the select correctly knowing the database and it's schema's. But at least for now it's working.

[Tom Barber]

There's certainly no problem with schema usage, but I suspect you're using a mondrian 3 schema on saiku standalone which uses mondrian 4. Behind the scenes it upgrades your schema and my guess is it's ignoring the schema attribute.

[Tom Barber]

If that is the case. The easy solution would be to uncomment the mondrian block in log4j.xml and copy out the mondrian 4 schema and just add the schema attribute manually

[cristia...@gmail.com]

Yes, I'm using LEGACY MODE. So it validates the DATABASE SCHEMA but while using SELECT it doesn't use the database schema while in LEGACY? Cause when I thought forcing it to use the schema writting on table the "DIMENSAO.CHEGADAS" it gave me an error that this table doesn't exist. I'll try updating the SCHEMA, cause I used the SCHEMA WORKBENCH since I'm new on SAIKU/MONDRIAN. There isn't any app that does the same as SCHEMA WORKBENCH to make mondrian 4.0 schema, is there?

Thanks for your feedback!

[Fizard]

You are the best Tom! It worked like a charm. Just copied and added the DATABASE SCHEMA ATTRIBUTE on all the TABLE tags and saved the XML in Mondrian 4 and it worked!

Thank you very much! I'll now send this solution to others that were in the same problem.

[Tom Barber]

Can someone raise a jira (jira.meteorite.bi) and I'll see if I can find the block of code that deals with table tags in mondrian and fix the schema upgrader so other people don't his the same bug.

[Isaias Sanchez]

If you can, send me your Mondrian 3 schema and I'll take some minutes to upgrade it to Mondrian 4 dialect. Do it privately if you prefer and/or changing schema and table names for security reason, then  you can change it back to real names.

Only need to donate 20 box to meteorite.bi :D (just kidding).

Regards,

Isaias S.

Tom Barber wrote on 11/04/16 20:55:

--
You received this message because you are subscribed to the Google Groups "Saiku Dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dev+uns...@saiku.meteorite.bi.

[ricard...@clouderp.com.ar]

Hello Isaias!! is there any kind of manual for Mondrian 4 dialect? any URL or site ?

plenty of users want it, it would be nice!

To unsubscribe from this group and stop receiving emails from it, send an email to d...@saiku.meteorite.bi.

[Isaias Sanchez]

Yes, there are some docs around you can use:

A good place to start is an old blog post from Diethard Steiner:

http://diethardsteiner.blogspot.com/2013/01/mondrian-4-get-ready.html

It has a simple example that you can extend to fulfill your need.

And to go deeper you'll need the book; Mondrian in Action from William Back, Nicholas Goodman, and Julian Hyde.

https://www.manning.com/books/mondrian-in-action

Mondrian documentation for mondrian 4 is gone, so there are few places to look.

Hope it helps.

Isaias S.

--
To unsubscribe from this group and stop receiving emails from it, send an email to dev+uns...@saiku.meteorite.bi.

[Ricardo Svibel]

Thank you so much Isaias!! ill check these links, special thanks for your time!!!!

        *************

    ERP FULL WEB

   RICARDO SVIBEL

www.clouderp.com.ar