Included Pentaho Report Properties result in invalid XMLA

Alan Leavy
2013-04-08
2013-04-10
  • Alan Leavy

    Alan Leavy - 2013-04-08

    I'm trying to access a Microsoft SSAS cube from a Pentaho report, using olap4j.
    A connection test is successful but a simple query fails:

    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: Illegal qualified name character.
    .
        </faultstring>
        <detail>
            <Error Description="XML parsing failed at line 15, column 10: Illegal qualified name character.
    ." 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::DATABASE-TYPE>GENERIC</::PENTAHO-REPORTING::DATABASE-TYPE>
            <::PENTAHO-REPORTING::NAME>Analysis Services Tutorial</::PENTAHO-REPORTING::NAME>
            <::PENTAHO-REPORTING::DATABASE-NAME></::PENTAHO-REPORTING::DATABASE-NAME>
            <Content>Data</Content>
          </PropertyList>
        </Properties>
      </Discover>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
    

    This issue was previously reported to Pentaho by someone else:
    http://forums.pentaho.com/showthread.php?95342-Simple-MDX-Query-against-MS-SASS-12-fails-with-SOAP-Fault

    Should the olap4j driver filter out these properties (e.g. <::PENTAHO-REPORTING::HOSTNAME>)?

    Would it be complex to modify the driver to do this?
    Willing to collaborate in any required development if so.
    Alan.

     
    • Julian Hyde

      Julian Hyde - 2013-04-08

      I think there are two issues.

      First, Pentaho Report Designer should not be sending these properties if the target is SSAS. (They are not supported by this server.)

      Second, and less important, the olap4j XMLA driver should convert the properties XML identifiers that SSAS considers valid. (Per the XMLA specification, it is valid for an XML identifier to start with a colon, but SSAS does not agree with this, and who are we to argue.) The conventional way to do this is in XMLA is to encode the character code. Thus

      <_x003ax003a_PENTAHO-REPORTING_x003ax003a_PORT>1521

      because ':' is unicode 0x003a.

      I have re-opened http://jira.pentaho.com/browse/PRD-3825.

      Thomas, What do you think?

      Julian

      On Apr 8, 2013, at 8:47 AM, Alan Leavy alanleavy@users.sf.net wrote:

      I'm trying to access a Microsoft SSAS cube from a Pentaho report, using olap4j.
      A connection test is successful but a simple query fails:

      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: Illegal qualified name character.
      .
      </faultstring>
      <detail>
      <Error Description="XML parsing failed at line 15, column 10: Illegal qualified name character. ." 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::PORT>1521
      <::PENTAHO-REPORTING::DATABASE-TYPE>GENERIC
      <::PENTAHO-REPORTING::NAME>Analysis Services Tutorial
      <::PENTAHO-REPORTING::DATABASE-NAME>
      <Content>Data</Content>
      </PropertyList>
      </Properties>
      </Discover>
      </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>
      This issue was previously reported to Pentaho by someone else:
      http://forums.pentaho.com/showthread.php?95342-Simple-MDX-Query-against-MS-SASS-12-fails-with-SOAP-Fault

      Should the olap4j driver filter out these properties (e.g. <::PENTAHO-REPORTING::HOSTNAME>)?

      Would it be complex to modify the driver to do this?
      Willing to collaborate in any required development if so.
      Alan.

      Included Pentaho Report Properties result in invalid XMLA

      Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/olap4j/discussion/577988/

      To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

       
      • pstoellberger

        pstoellberger - 2013-04-08

        I think this is a result of a change of mine where I added support of adding properties to the XMLA request.

        Back then I was considering to check if that property is actually supported by this XMLA server (using the DISCOVER_PROPERTIES call)
        I think I ended up not doing it because it was too expensive. Can't really say.

        Maybe that would be an option for that problem as well?

        Apart from the encoding issue, which should be addressed anyway

        -Paul

        On Apr 8, 2013, at 6:39 PM, Julian Hyde wrote:

        I think there are two issues.

        First, Pentaho Report Designer should not be sending these properties if the target is SSAS. (They are not supported by this server.)

        Second, and less important, the olap4j XMLA driver should convert the properties XML identifiers that SSAS considers valid. (Per the XMLA specification, it is valid for an XML identifier to start with a colon, but SSAS does not agree with this, and who are we to argue.) The conventional way to do this is in XMLA is to encode the character code. Thus

        <_x003ax003a_PENTAHO-REPORTING_x003ax003a_PORT>1521

        because ':' is unicode 0x003a.

        I have re-opened http://jira.pentaho.com/browse/PRD-3825.

        Thomas, What do you think?

        Julian

        On Apr 8, 2013, at 8:47 AM, Alan Leavy alanleavy@users.sf.net wrote:

        I'm trying to access a Microsoft SSAS cube from a Pentaho report, using olap4j.
        A connection test is successful but a simple query fails:

        java.lang.RuntimeException: org.olap4j.OlapException: XMLA provider gave exception:

        XMLAnalysisError.0xc10f0006

        XML parsing failed at line 15, column 10: Illegal qualified name character.
        .

        Request was:
        <?xml version="1.0" encoding="UTF-8"?>

        DISCOVER_DATASOURCES

        <::PENTAHO-REPORTING::HOSTNAME>
        <::PENTAHO-REPORTING::PORT>1521
        <::PENTAHO-REPORTING::DATABASE-TYPE>GENERIC
        <::PENTAHO-REPORTING::NAME>Analysis Services Tutorial
        <::PENTAHO-REPORTING::DATABASE-NAME>
        Data

        This issue was previously reported to Pentaho by someone else:
        http://forums.pentaho.com/showthread.php?95342-Simple-MDX-Query-against-MS-SASS-12-fails-with-SOAP-Fault

        Should the olap4j driver filter out these properties (e.g. <::PENTAHO-REPORTING::HOSTNAME>)?

        Would it be complex to modify the driver to do this?
        Willing to collaborate in any required development if so.
        Alan.

        Included Pentaho Report Properties result in invalid XMLA

        Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/olap4j/discussion/577988/

        To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

        Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/olap4j/discussion/577988/

        To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

         
  • pstoellberger

    pstoellberger - 2013-04-08

    I think this is a result of a change of mine where I added support of adding properties to the XMLA request.

    Back then I was considering to check if that property is actually supported by this XMLA server (using the DISCOVER_PROPERTIES call)
    I think I ended up not doing it because it was too expensive. Can't really say.

    Maybe that would be an option for that problem as well?

    Apart from the encoding issue, which should be addressed anyway

    -Paul

     
    • Julian Hyde

      Julian Hyde - 2013-04-08

      Where do the property values come from? Does client code call "setProperty" or something?

       
      • Alan Leavy

        Alan Leavy - 2013-04-10

        Hi Julian,

        Looking at https://github.com/pentaho/pentaho-reporting/blob/master/designer/datasource-editor-olap4j/source/org/pentaho/reporting/ui/datasources/olap4j/OlapConnectionPanel.java

        I see the following snippet:

        return new DriverConnectionDefinition
            (customName, dcp.getDriver(), dcp.getUrl(),
                dcp.getProperty("user"), dcp.getProperty("password"),
                dcp.getProperty("::pentaho-reporting::hostname"),
                dcp.getProperty("::pentaho-reporting::database-name"),
                dcp.getProperty("::pentaho-reporting::database-type"),
                dcp.getProperty("::pentaho-reporting::port"),
                p);
        

        The DriverConnectionDefinition constructor sets those same properties in its class.
        I'm guessing that's the source of the offending XML elements added to the XMLA request by makeConnectionPropertyList() in /org/olap4j/driver/xmla/XmlaOlap4jConnection.java

        With regard to your proposed change in https://sourceforge.net/p/olap4j/bugs/81/, I downloaded the latest olap4j code from git, with the intention of trying it out locally, by modifying xmlEncode() in XmlaOlap4jConnection.java. After compiling the latest snapshot, without implementing any change, I was surprised to find that the error I originally reported was no longer occurring. Perhaps the properties are no longer being added by the latest code.

        However, I'm now seeing the same issue as was reported here:
        http://jira.pentaho.com/browse/PRD-3697?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel
        - My simple query works when previewed in the PRD query dialog but the actual datasource creation (clicking OK) generates an exception and it cannot be used in the report:

        "java.lang.IllegalArgumentException: No enum const class org.olap4j.metadata.Property$StandardMemberProperty.BACKGROUND_COLOR"
        
         
        • Julian Hyde

          Julian Hyde - 2013-04-10

          On Apr 10, 2013, at 5:47 AM, Alan Leavy alanleavy@users.sf.net wrote:

          Looking at https://github.com/pentaho/pentaho-reporting/blob/master/designer/datasource-editor-olap4j/source/org/pentaho/reporting/ui/datasources/olap4j/OlapConnectionPanel.java

          I see the following snippet:

          return new DriverConnectionDefinition
          (customName, dcp.getDriver(), dcp.getUrl(),
          dcp.getProperty("user"), dcp.getProperty("password"),
          dcp.getProperty("::pentaho-reporting::hostname"),
          dcp.getProperty("::pentaho-reporting::database-name"),
          dcp.getProperty("::pentaho-reporting::database-type"),
          dcp.getProperty("::pentaho-reporting::port"),
          p);
          The DriverConnectionDefinition constructor sets those same properties in its class.
          I'm guessing that's the source of the offending XML elements added to the XMLA request by makeConnectionPropertyList() in /org/olap4j/driver/xmla/XmlaOlap4jConnection.java

          I agree. Thanks for investigating.
          With regard to your proposed change in https://sourceforge.net/p/olap4j/bugs/81/, I downloaded the latest olap4j code from git, with the intention of trying it out locally, by modifying xmlEncode() in XmlaOlap4jConnection.java. After compiling the latest snapshot, without implementing any change, I was surprised to find that the error I originally reported was no longer occurring. Perhaps the properties are no longer being added by the latest code.

          Most mysterious.

          We should still go ahead and fix issue 81, to encode non-alphanumeric characters in properties.

          Microsoft does not specify that is a valid property name, but their built-in properties are all UpperCamelCase; see http://msdn.microsoft.com/en-us/library/ms186627.aspx.

          I STRONGLY recommend that people adhere to that standard for property names. Even underscores will cause problems, because we will encode _ to 0x005f and that may confuse the server receiving the request.

          However, I'm now seeing the same issue as was reported here:
          http://jira.pentaho.com/browse/PRD-3697?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel
          - My simple query works when previewed in the PRD query dialog but the actual datasource creation (clicking OK) generates an exception and it cannot be used in the report:

          "java.lang.IllegalArgumentException: No enum const class org.olap4j.metadata.Property$StandardMemberProperty.BACKGROUND_COLOR"
          That seems to be an issue (or two) in PRD. I have added a comment to that case.

          Julian

           

Log in to post a comment.