Thursday, May 21, 2009

Connect Microsoft SQL Server from olap4j

Browsing my Google Analytics statistics, I realized there is a lot of people out there that are searching for ways to connect Microsoft SQL Server with olap4j.

Here is a nice example.

// We must use the XMLA driver.
Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");


// This code is for Java 5. With Java 6, you can directly
// unwrap the underlying connection with the .unwrap() call.
OlapConnection connection =
(OlapConnection) DriverManager.getConnection(


// This is the SQL Server service end point.
"jdbc:xmla:Server=http://example.com/olap/msmdpump.dll"


// Tells the XMLA driver to use a SOAP request cache layer.
// We will use an in-memory static cache.
+ ";Cache=org.olap4j.driver.xmla.cache.XmlaOlap4jNamedMemoryCache"


// Sets the cache name to use. This allows cross-connection
// cache sharing. Don't give the driver a cache name and it
// disables sharing.
+ ";Cache.Name=MyNiftyConnection"


// Some cache performance tweaks.
// Look at the javadoc for details.
+ ";Cache.Mode=LFU;Cache.Timeout=600;Cache.Size=100",


// XMLA is over HTTP, so BASIC authentication is used.
"username",
"password" );



// We can execute a query. MDX of course.
CellSet set = connection.createStatement().executeOlapQuery(
"SELECT {} ON COLUMNS FROM CUBE");


Update : Some useful links

4 comments:

  1. Thanks for the useful info. It's so interesting

    ReplyDelete
  2. Can you share an example for Java 1.4.2

    ReplyDelete
  3. How interesting! I have an iPhone game called DONKEY! and this blog is a DONKEY BLOG! NICE :)

    ReplyDelete
  4. I am able to use the code and retrieve the catalogs and that would help to set initial catalog. How to retrieve the DataMember for that catalog inorder to set it to pivot?

    Thanks in advance!

    ReplyDelete