Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-1510

SQL Syntax error on cardinality query using Generic SQLDialect for Sybase IQ 12.7 and 15.4 database.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.5.0 GA (4.8.0 GA Suite Release)
    • Component/s: None
    • Labels:
      None
    • Story Points:
      1
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      We are using Sybase IQ 12.7 for the data. We are connecting to Sybase IQ 12.7 using the JDBC driver jconn3.jar.
      We created a datasource using the option File-->New --> Data Source... using the sql query.

      I am attaching a document explaining the steps followed and steps to replicate the issue.
      I have also attached the mondrian schema file created and all the logs (pentaho.log, mondrian.log, mondrian_mdx.log and mondrian_sql.log) with the error.

      In the pentaho.log we get the following error:
      java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.sybase.jdbc3.jdbc.SybSQLException: SQL Anywhere Error -131: Syntax error near '(end of line)' on line 14

      This is because the following query generated by mondrian fails.

      select count from (select distinct "FACT"."CompanyName" as "c0" from (SELECT
      ID,
      Surname,
      GivenName,
      Street,
      City,
      State,
      Country,
      PostalCode,
      Phone,
      CompanyName
      FROM
      GROUPO.Customers
      where Country='Canada') "FACT")

      The sql query is failing because of the missing alias at the end.

      Workaround:
      Add the attribute "approxRowCount" at each Level definition. For example
      <Level name="Surname" column="Surname" uniqueMembers="false" approxRowCount="100">
      </Level>

      Instead of mondrian to query for the count, you explicitly specify the approximate row count by using the attribute "approxRowCount".
      By this way mondrian will not query for count.

      This issue happens even with Sybase IQ 15.4 database.

        Attachments

        1. mondrian_mdx.log
          2 kB
        2. mondrian_sql.log
          5 kB
        3. mondrian.log
          14 kB
        4. pentaho.log
          14 kB
        5. sql_syntax_error_with_sybaseiq.docx
          1.02 MB
        6. sybase_datasource.mondrian.xml
          5 kB

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              gdev Gurudev
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: