You are not logged in.
Hi!
Listed below is an error that is being returned from Sybase SQL Anywhere 12 to ReportServer when attempting to add a dimension that is not "all" to a pivot mode report:
SybSQLException: SQL Anywhere Error -131: Syntax error near '(end of line)' on line 1
I know this is not a native database; what I don't know is how to debug this issue within ReportServer to see if this is something that can be handled by additions/adjustments to the groovy script or if there is another way to achieve this specific functionality in ReportServer for my SQL Anywhere DB or not. Any input will be appreciated!
Thanks!
Rory S.
Offline
Hi Rory,
sorry about the delay, i need a little more information on the problem: you said:
"an error that is being returned from Sybase SQL Anywhere 12 to ReportServer when attempting to add a dimension that is not "all" to a pivot mode report".
When adding a dimension, did you attempt to run a report on a predefined mondrian cube or did you try to use the pivot modus on a dynamic list ?
Can you give me a little more details reg. the error (full stack?)
wbr jan
Offline
Hi Jan,
Thank you for responding. The error occurs when attempting to use the pivot modus on a dynamic list that is based on a sql select that executes as expected from the dynamic list properties setup screen and this is running on a recent Bitnami ReportServer Enterprise stack ( RS3.0.2-5855 (2016-05-29-17-55-24) ) and reporting against SQL Anywhere 12 database.
Here is the error from log fie:
Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Reading row count from query [select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (SELECT * FROM job_consumption_data where fiscal_year >= 2010 ) wrappedQry) as "GenericDynaListCube"]; sql=[select count(*) from (select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (SELECT * FROM job_consumption_data where fiscal_year >= 2010 ) wrappedQry) as "GenericDynaListCube")]
Please note that when I extract the sql from the error message (see below) and run in the db, the same error is generated
select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (SELECT * FROM job_consumption_data where fiscal_year >= 2010 ) wrappedQry) as "GenericDynaListCube"; select count(*) from (select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (SELECT * FROM job_consumption_data where fiscal_year >= 2010 ) wrappedQry) as "GenericDynaListCube")
However when I add the table alias "dummy" to the same SQL statements (see below) the sql runs without error and returns 2 cursors as expected
select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (SELECT * FROM job_consumption_data where fiscal_year >= 2010 ) wrappedQry) as "GenericDynaListCube"; select count(*) from (select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (SELECT * FROM job_consumption_data where fiscal_year >= 2010 ) wrappedQry) as "GenericDynaListCube") as "dummy"
Note that adding the - as "dummy" to the sql statement allowed the statement to process without error.
Please let me know if there is any more info you need.
Thanks!
Rory S.
Offline
Hi Rory,
we tried to generate the same query you are generating in order to reproduce the problem. We get similar queries as yours, but not quite the same, and the queries we generate are all working.
So in order to reproduce the problem, we need the exact steps you are taking in the Pivot mode to generate the query. Maybe some screenshots and the exact steps you take to generate the query you sent.
Best regards,
Eduardo
Offline
Hi Eduardo,
This zip file ( ReportServer Sybase Pivot Mode bug_.zip ) contains a short avi showing the exact steps I am taking that result in the reported error.
Thanks,
Rory
Offline
Hi Rory,
thanks for the avi. I think it describes the error in #1, but this does *not* produce the queries you sent in #3, is this correct? Can you please check if the queries in the video are the ones you posted in #3 ?
Thanks,
Eduardo
Offline
#1's error is: "Syntax error near '(end of line)' on line 1"
#3's error is: "Mondrian Error:Internal error: Reading row count from query"
And in the video, I see "Syntax error near '(end of line)' on line 1". That's why I suppose this.
Regards,
Eduardo
Offline
Hi Eduardo,
Here is a link ( reportserver.2017-03-03.log ) to the report server log file for today. The only report action that should be represented in the log is the same action that you see in the avi. The last step I took was to move the part_number dimension to the row area which is where the (end of line) error appears. That is where the I stopped and the log ends. I took the queries that I tested and referenced in my earlier post from a similar log file entry a few days ago. You can see in the the log that it is calling these queries and then the ( end of line ) error shows up later.
I think the root cause is the same for both errors that you are listing; the errors themselves being expressed at different stages of the process with the Mondrian error being hidden in the process and the (end of line) error being the last error and therefore what is returned for display to the user.
Thanks,
Rory
Offline
Hi Rory,
we are not being able to correctly test the issue, since we cannot find Sybase SQL Anywhere 12 to download.
Would it be possible for you to create a test database we can access in order to properly test?
Best regards,
Eduardo
Offline
Hi Eduardo,
I can possibly do that; however we are also running a development server with the latest SQL ANYWHERE 17 version and I changed the report server data source to access the newer database using an imported copy of the data and get the exact same resulting error
Thanks,
Rory
Offline
Hi Rory,
I took a look at this issue and it is a bug in Mondrian. Take a look at http://jira.pentaho.com/browse/MONDRIAN-1510 for a similar bug report.
The MDX query seems to create the incorrect queries. In order to recreate this issue you can do the following:
1. Create a Mondrian datasource with schema:
<?xml version="1.0"?>
<Schema name="GenericDynaListSchema">
<Cube name="GenericDynaListCube">
<View alias="GenericDynaListCube">
<SQL dialect="generic">
<![CDATA[SELECT * FROM (select * from job_consumption_data where fiscal_year >= 2010) wrappedQry]]>
</SQL>
</View>
<Dimension name="part_number">
<Hierarchy hasAll="true">
<Level name="part_number" visible="true" column="part_number" type="String" uniqueMembers="false" />
</Hierarchy>
</Dimension><Measure name="qty_produced" column="qty_produced" aggregator="sum" formatString="Standard"/></Cube>
</Schema>properties:
type=OLAP
name=
driver=mondrian.olap4j.MondrianOlap4jDriver
jdbcDrivers=com.mysql.jdbc.DriverURL:
jdbc:mondrian:Jdbc=jdbc:sybase:Tds:YOUR_IP:2638?ServiceName=SERVICENAME2. Create a Saiku Report, and select your Mondrian datasource. Enable the "MDX Editor" and select the cube "GenericDynaListCube" from the list.
3. Double click on your new report
4. Switch to MDX Mode
5. Enter:
select {[Measures].[qty_produced]}
ON COLUMNS,
{Hierarchize(
{[part_number].[part_number].Members}
)} ON ROWS
from [GenericDynaListCube]This is the MDX query created by Mondrian if you select "Part number".
6. Run query
--> You get the same error: "SybSQLException: SQL Anywhere Error -131: Syntax error near '(end of line)' on line 1"
If you change the MDX query to the following:
SELECT
{[Measures].[qty_produced]} ON COLUMNS,
{Hierarchize({[part_number].[All part_numbers]})} ON ROWS
FROM [GenericDynaListCube]which is the MDX query created when selecting "all part numbers", the query works.
So, unfortunately, this is a Mondrian query, as stated in the link above. I will raise a reportserver ticket and we will check if it is possible to correct.
Best regards,
Eduardo
Offline
Hi Eduardo,
Thank you for taking the time to look into this. Please let me know when you find out if this is possible to correct.
Thanks!
Rory
Offline
Hi Eduardo,
FYI - It looks like this issue is related to a Mondrian dialect setting called requiresAliasForFromQuery() which apparently needs to be overridden in the dialect setup for Sybase datasources for Mondrian. The similar bug report you mentioned in an earlier post pointed this out. http://jira.pentaho.com/browse/MONDRIAN-1510.
"The Sybase dialect needed to override requiresAliasForFromQuery to make sure that Mondrian would add an alias to the sub-query."
Reference: http://mondrian.pentaho.com/api/mondria … romQuery()
I am not sure where / if that override can be accomplished in the mdx query / schema setup or not or if it needs to be addressed in the code or script that facilitates access to the SQL Anywhere database.
I am pretty certain however, given the results of the Mondrian generated query tests against my SQL Anywhere db and the full text of the referenced bug report, that this Mondrian dialect setting for Sybase is the root cause of this issue.
Thanks,
Rory
Offline
Hi Rory,
thanks for pointing this out.
I will check if (manually) overriding this setting works. As far as I know, in newer versions, this setting is already true, but the bug is still showing. So I will try to figure out if this setting is not being read.
Cheers,
Eduardo
Offline
Hi Rory,
after investigating, it may be a reportserver bug: the SybaseDialect seems not to be properly recognized. So the requiresAliasForFromQuery is not being read correctly. I will update when I have more information.
Cheers,
Eduardo
Offline
Hi Eduardo,
Thanks again for looking into this!
Rory
Offline
Hi Rory,
thank you for your detailed bug report. In fact, this is a Mondrian bug: the Sybase database is only recognized when the driver reports a product name containing the string "sybase". This is not the case with the jconnect driver for Sybase. It reports "SQL Anywhere" instead. This is the reason why requiresAliasForFromQuery is not being read correctly for Sybase.
I created a patched version of the mondrian libraries for Reportserver and created a thread in the Mondrian forums for further investigating.
Anyhow, as I pointed out, I created a patched version of Mondrian, so this will be fixed in the next Reportserver version.
Cheers,
Eduardo
Offline
Hi Eduardo,
Great news! That is awesome! We really appreciate your diligence in searching out the root cause and finding a solution for this issue.
Thanks again!
Rory
Offline