Getting data from SQL Analysis Services using Java and olap4j
The Problem
You have a Microsoft SQL Server Analysis Services cube you want to get at using Java. Accessing SSAS from Microsoft clients is a well documented and well travelled path. Getting data out of SSAS using Java is a less well documented. It is possible though, due to SSAS's use of XMLA.
The first thing you will need to do is setup HTTP access. Once this is done there are a few options. You could develop a web services client to consume the SSAS web service. Alternatively there are two JDBC drivers I am aware of olap4j and Jdbc4Olap that expose an API for data access.
In my case I had a SQL Server 2008 cube which I wanted to use. I was developing a suite of dashboards on the Websphere Application Server 7.0 (WAS). This meant JDK 1.6. After a lot of experimentation I settled on olap4j, so that is what is used in the examples below.
These instructions, although specific to my environment, should work with any version of SSAS with JDK 1.5 and above.
Tags: connectivity