search

pentaho / mondrian

Mondrian is an Online Analytical Processing (OLAP) server that enables business users to analyze large quantities of data in real-time.
http://mondrian.pentaho.com/
Other
1.13k stars 720 forks source link

How to force Sybase dialect in Mondrian connection? #1046

Closed pgschr closed 6 years ago

pgschr commented 6 years ago

I'm trying to implement Mondrian/olap4j with SAP Hana database (columnar/in-memory). SAP Hana originated from a Sybase database, and the dialects are similar.

Mondrian is not detecting that the database is Sybase, as it's inserting unneeded quotes, so I'd like to force the dialect (maybe in the connection string?):

This works (code in Scala):

      val connectionString = "jdbc:mondrian:Jdbc=jdbc:sap://10.194.19.20:39013?" + 
          "user=uuuuu&password=pppp;" + 
          "Catalog=lib/Schema.xml;JdbcDrivers=com.sap.db.jdbc.Driver"
      val connection = DriverManager.getConnection(connectionString)
      val olapConnection = connection.unwrap(classOf[OlapConnection])

      val parserFactory = olapConnection.getParserFactory
      val parser = parserFactory.createMdxParser(olapConnection)
      val query = parser.parseSelect(
            "select from [DDABalances] WHERE [Bank].[1]")
       val statement = olapConnection.createStatement

However when I try to run this statement, I get an exception, because "BANKS"."sk" is incorrect as it shouldn't have quotes:

val cellSet = statement.executeOlapQuery(query)

The exception:

Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: while counting members of level '[Bank].[Bank SK]; sql=[select count(*) as "c0" from (select distinct "BANKS"."sk" as "c0" from "BANKS" as "BANKS") as "init"] at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:987) at mondrian.olap.Util.newInternal(Util.java:2410) at mondrian.olap.Util.newError(Util.java:2426) at mondrian.rolap.SqlStatement.handle(SqlStatement.java:364) at mondrian.rolap.SqlStatement.execute(SqlStatement.java:254) at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:350) at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:303) at mondrian.rolap.SqlMemberSource.getMemberCount(SqlMemberSource.java:149) at mondrian.rolap.SqlMemberSource.getLevelMemberCount(SqlMemberSource.java:142) at mondrian.rolap.SmartMemberReader.getLevelMemberCount(SmartMemberReader.java:158)

Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [260] (at 54): invalid column name: BANKS.sk: line 1 col 55 (at pos 54) at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._createException(SQLExceptionSapDB.java:208)

pgschr commented 6 years ago

For those having the same issue, this is how you solve it: You need to touch a source JdbcDialectImpl.java adding a line (see below) and recompile Mondrian:

        } else if (upperProductName.indexOf("SYBASE") >= 0
            || upperProductName.indexOf("ADAPTIVE SERVER") >= 0 
            || upperProductName.equals("HDB")) {   // <-- I added this line
            return DatabaseProduct.SYBASE;