Using olap4j with MS MSQL Server Error in getOlapSchema()

My Coyne
2013-08-02
2013-08-03
  • My Coyne
    My Coyne
    2013-08-02

    I'm not quite sure that this is the question for this forum or not; I am trying to ask the same question in both Microsoft and Olap4j forums. I have set up all steps as MS SQL Server required. Since I was able to get the connection and unwrap the connection to Olap4jConnection, successfully get the metadata. But the getOlapSchema() failed (resulting in HTTP response code 500), this is the reason I post the question on this forum. Any help is greatly appreciated.

    ================================

    I would like to use olap4j to access an OLAP db that is on MS SQL Server 2005. I have spent 2 days now try to get a simple task of getting access to the database using Java, but keep getting HTTP response code 500 when trying to gain access to getOlapSchema().

    In my configuration the Analysis Server (OLAP db) is on a different server than the IIS server.

    I have performed the following steps:

    1. http://technet.microsoft.com/en-us/library/cc917711.aspx Note the msmdpump.ini have the server name points to the Analysis server (OLAP db).

    2. The OLAP db is using the credential from Windows Authentication method. The OLAP db named "AnalysisServiceEos"

    Error: HTTP 500 when access msmdpump.dll.

    However, when I debug the simple java codes (below), it seems that the jdbc connection and the unwrap connection to Olap4Jconnection are executed successfully. Examine metadata object, both Olapdatabases field and schema are null.

    Any help is appreciated.

    Java codes:

    package util;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;

    import org.olap4j.OlapConnection;
    import org.olap4j.OlapDatabaseMetaData;
    import org.olap4j.metadata.Cube;
    import org.olap4j.metadata.NamedList;
    import org.olap4j.metadata.Schema;

    public class OLAnalysisService {

       public static void main(String[] args) throws Exception {
              Class.forName ("org.olap4j.driver.xmla.XmlaOlap4jDriver");
              final Connection connection =
                              DriverManager.getConnection (
                                "jdbc:xmla:"
                        +"Server=http://inside.ehr.nsf.gov/OLAP/msmdpump.dll"
                         +";Catalog=AnalysisServiceEos"
                        +";Database=GENOMIC-SQL-01"
                        +";User=windowslogin"
                        +";Password=windowspassword"
                              , null
                              , null
                              );
              System.out.println("get connection successfuly");
              // Unwrap JDBC connction to OlapConnction
              OlapConnection oConnection =  connection.unwrap(OlapConnection.class);
              System.out.println("get connection successfuly");
              // =======
              System.out.println(" About to getMetadata()");
              final OlapDatabaseMetaData metadata = oConnection.getMetaData();
              System.out.println("Get metadata successfully.  metadata <" + metadata.toString() + ">");
    
              //final ResultSet catalog = metadata.getCatalogs();  // this gives errors
    
              System.out.println("About to getOlapSchema()");
              final Schema schema = oConnection.getOlapSchema();   // this gives errors
              for (Cube cube : schema.getCubes())
              {
                     System.out.println(cube.getName());
              }
    
              connection.close();
    
       }
    

    }

    }

    OUTPUT:

    get connection successfuly
    get connection successfuly
    About to getMetadata()
    Get metadata successfully. metadata org.olap4j.driver.xmla.FactoryJdbc41Impl$XmlaOlap4jDatabaseMetaDataJdbc41@7b9bbe8
    About to getOlapSchema()
    Exception in thread "main" java.lang.RuntimeException: org.olap4j.OlapException: This connection encountered an exception while executing a query.
    at org.olap4j.driver.xmla.DeferredNamedListImpl.getList(DeferredNamedListImpl.java:96)
    at org.olap4j.driver.xmla.DeferredNamedListImpl.get(DeferredNamedListImpl.java:120)
    at org.olap4j.driver.xmla.DeferredNamedListImpl.get(DeferredNamedListImpl.java:47)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.getOlapDatabase(XmlaOlap4jConnection.java:459)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.getOlapCatalog(XmlaOlap4jConnection.java:501)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.getOlapSchema(XmlaOlap4jConnection.java:554)
    at util.OLAnalysisService.main(OLAnalysisService.java:43)
    Caused by: org.olap4j.OlapException: This connection encountered an exception while executing a query.
    at org.olap4j.driver.xmla.XmlaHelper.createException(XmlaHelper.java:43)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.executeMetadataRequest(XmlaOlap4jConnection.java:878)
    at org.olap4j.driver.xmla.XmlaOlap4jDatabaseMetaData.getMetadata(XmlaOlap4jDatabaseMetaData.java:137)
    at org.olap4j.driver.xmla.XmlaOlap4jDatabaseMetaData.getMetadata(XmlaOlap4jDatabaseMetaData.java:67)
    at org.olap4j.driver.xmla.XmlaOlap4jDatabaseMetaData.getDatabaseProperties(XmlaOlap4jDatabaseMetaData.java:1044)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.makeConnectionPropertyList(XmlaOlap4jConnection.java:324)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.generateRequest(XmlaOlap4jConnection.java:1037)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.populateList(XmlaOlap4jConnection.java:849)
    at org.olap4j.driver.xmla.DeferredNamedListImpl.populateList(DeferredNamedListImpl.java:136)
    at org.olap4j.driver.xmla.DeferredNamedListImpl.getList(DeferredNamedListImpl.java:90)
    ... 6 more
    Caused by: org.olap4j.driver.xmla.proxy.XmlaOlap4jProxyException: This proxy encountered an exception while processing the query.
    at org.olap4j.driver.xmla.proxy.XmlaOlap4jHttpProxy.getResponse(XmlaOlap4jHttpProxy.java:167)
    at org.olap4j.driver.xmla.proxy.XmlaOlap4jAbstractHttpProxy.get(XmlaOlap4jAbstractHttpProxy.java:181)
    at org.olap4j.driver.xmla.proxy.XmlaOlap4jHttpProxy.get(XmlaOlap4jHttpProxy.java:41)
    at org.olap4j.driver.xmla.XmlaOlap4jConnection.executeMetadataRequest(XmlaOlap4jConnection.java:876)
    ... 14 more
    Caused by: java.io.IOException: Server returned HTTP response code: 500 for URL: http://inside.ehr.nsf.gov/OLAP/msmdpump.dll
    at sun.net.www.protocol.http.HttpURLConnection.getInputStream(Unknown Source)
    at org.olap4j.driver.xmla.proxy.XmlaOlap4jHttpProxy.getResponse(XmlaOlap4jHttpProxy.java:130)
    ... 17 more

     
  • pstoellberger
    pstoellberger
    2013-08-02

    if you get a 500 it means that the pump is throwing an error, have you tried checking the logs there? you should see something in there...

     
  • My Coyne
    My Coyne
    2013-08-03

    Hi pstoellberger,
    Thank you for your reply. I looked at the log where the pump is (on MS IIS Server); but i am not quite sure I see the error, except for the status of 500 is there.

    cs-username is null, is it perhaps the configuration use anonymous user?

    Follows is the log:

    Software: Microsoft Internet Information Services 6.0

    Version: 1.0

    Date: 2013-08-03 00:16:31

    Fields: date time s-sitename s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status

    2013-08-03 00:16:31 W3SVC1917432341 128.150.140.59 POST /olap/msmdpump.dll - 80 - 128.150.140.16 ADOMD.NET 500 0 193
    2013-08-03 00:16:47 W3SVC1917432341 128.150.140.59 POST /olap/msmdpump.dll - 80 - 128.150.117.109 Olap4j(1.1.0) 500 0 64
    2013-08-03 00:17:03 W3SVC1917432341 128.150.140.59 POST /olap/msmdpump.dll - 80 - 128.150.117.109 Olap4j(1.1.0) 500 0 64
    2013-08-03 00:17:43 W3SVC1917432341 128.150.140.59 POST /olap/msmdpump.dll - 80 - 128.150.117.109 Olap4j(1.1.0) 500 0 64
    2013-08-03 00:25:24 W3SVC1917432341 128.150.140.59 GET /olap/msmdpump.dll - 80 - 128.150.117.109

     
    Last edit: My Coyne 2013-08-03
  • My Coyne
    My Coyne
    2013-08-03

    I change the MS IIS security model to basic (which required users and password given at the connection time) and received the following in the pump log file. See attachment; the black mark is the domain and user name. Any idea? Thanks

     
    Attachments