Simple MDX Query against MS SASS 12 fails with SOAP-Fault

[ThomasWiesner]

Hello,

i'm working on a proof concept using pentaho report designer with an OLAP datasource connected to a MS SASS 12. The first step was trying a simple MDX Query in the datasource editor of the designer.

I've configured following datasource:

Data -> Add datasource -> OLAP -> OLAP4J

And added the following database connection:

Generic Database
Access: Native (JDBC)

Connection-URL: jdbc:xmla:Server=http://192.168.100.20/olap/msmdpump.dll;Catalog='Analysis Services Tutorial';Cube='Analysis Services Tutorial'
Custom Driver: org.olap4j.driver.xmla.XmlaOlap4jDriver
User and password are set.

The test was ok. In the datasource i added the following query:

SELECT Measures.MEMBERS ON COLUMNS FROM [Analysis Services Tutorial]

While starting the preview for this connection, i'll get the following exception (see full exception in attachments):

java.lang.RuntimeException: org.olap4j.OlapException: XMLA provider gave exception: <soap:Fault xmlns="http://schemas.xmlsoap.org/soap/envelope/"> [^]
<faultcode>
XMLAnalysisError.0xc10f0006
</faultcode>
<faultstring>
XML parsing failed at line 15, column 10: Ungültiges qualifiziertes Zeichen.
.
</faultstring>
<detail>
<Error Description="XML parsing failed at line 15, column 10: Ungültiges qualifiziertes Zeichen.
." ErrorCode="3238985734" HelpFile="" Source="Microsoft SQL Server 2012 Analysis Services">
</Error>
</detail>
</soap:Fault>

Request was:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" [^]
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> [^]
<SOAP-ENV:Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> [^]
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<::PENTAHO-REPORTING::HOSTNAME></::PENTAHO-REPORTING::HOSTNAME>
<::PENTAHO-REPORTING::PORT>1521</::PENTAHO-REPORTING::PORT>
<::PENTAHO-REPORTING:ATABASE-TYPE>GENERIC</::PENTAHO-REPORTING:ATABASE-TYPE>
<::PENTAHO-REPORTING::NAME>MS SASS</::PENTAHO-REPORTING::NAME>
<CUBE>Analysis Services Tutorial</CUBE>
<::PENTAHO-REPORTING:ATABASE-NAME></::PENTAHO-REPORTING:ATABASE-NAME>
<Content>Data</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>

If i try this query within a simple java class using OLAP4J with same connection parameters, i got no errors. I also figured out, that in this case the request looks like

<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" [^]
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> [^]
<SOAP-ENV:Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> [^]
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<CUBE>Analysis Services Tutorial</CUBE>
<Content>Data</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>

It seems , that the pentaho reporting properties are not allowed !?

Has anybody a hint or a solution for this problem ?

Kind regards,

Thomas Wiesner

[Taqua]

yes, and it seems that the mondrian driver does not filter them out either. Can you please file me a JIRA case for it?

http://jira.pentaho.com/browse/PRD

[ThomasWiesner]

Ok, jira case created

http://jira.pentaho.com/browse/PRD-3825

Can you please give me a feedback how fast you can solve this issue, or in which version you will plan to solve this. It is very important for our proof of concept and further decisions.

Kind regards,
Thomas Wiesner

[alan_leavy]

Hi Thomas,

I have exactly the same issue, running a query from PRD against SSAS 2012.
Did you ever find a solution?

I can run the same query using the OLAP Input step in Pentaho Data Integration so I guess that's a possible work around: having PRD use a PDI transformation rather than querying SSAS directly via an OLAP4J data source.
Would be nice not to have that extra layer though...

Regards,
Alan.