OLAP——JAVA
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.
Overview
To summarise the above. Your Java program uses a web services client or a JDBC like driver to comminicate with IIS. The web services client or driver talks XMLA to IIS. IIS handles the HTTP requests using msmdpump.dll to pass them to SSAS.
Now we need to write our Java program...
Step 1 - Setup the JAR file
Copy the .jar files to your class path and then import it in your Java program.
Step 2 - Setup the Connection
Step 3 - Execute your OLAP query
Here you can pass any MDX query to SSAS.
Step 4 - Process
The code below is using a couple of for loops to populate a dataset from another api. The key thing is you can access column and rows from the results of the MDX query as you require.
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.
Overview
To summarise the above. Your Java program uses a web services client or a JDBC like driver to comminicate with IIS. The web services client or driver talks XMLA to IIS. IIS handles the HTTP requests using msmdpump.dll to pass them to SSAS.
Now we need to write our Java program...
Step 1 - Setup the JAR file
Copy the .jar files to your class path and then import it in your Java program.
import org.olap4j.* Step 2 - Setup the Connection
Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
OlapConnection con = (OlapConnection)DriverManager.getConnection("jdbc:xmla:Server=http://myserver/olap/msmdpump.dll;Catalog=MyCatalog");
OlapWrapper wrapper = (OlapWrapper) con;
OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
OlapStatement stmt = olapConnection.createStatement(); Step 3 - Execute your OLAP query
Here you can pass any MDX query to SSAS.
CellSet cellSet = stmt.executeOlapQuery("SELECT {[Measures].[Qty], [Measures].[Cost Base]} ON Columns, {[Product].[Category].[Category]} ON Rows FROM [Invoices]"); Step 4 - Process
The code below is using a couple of for loops to populate a dataset from another api. The key thing is you can access column and rows from the results of the MDX query as you require.
DataSet ds = new DataSet();
for (Position rowPos : cellSet.getAxes().get(1)) {
ds.addRow();
for (Position colPos : cellSet.getAxes().get(0)) {
test += Integer.toString(rowPos.getOrdinal()) + " : " + Integer.toString(colPos.getOrdinal());
Cell cell = cellSet.getCell(colPos, rowPos);
test += "Value: " + cell.getFormattedValue() + "<br />";
ds.addValue("column" + Integer.toString(colPos.getOrdinal()), cell.getFormattedValue());
}
}