If you are looking for a quick way to connect to an OLAP / multidimensional database like
SQL Server 2008 Analysis Services, here is the procedure.
Be aware that you need to set up XML/A HTTP access for SQL Server Analysis in IIS first.
Basically you need to load a component that enables data exchanges between a client and SSAS.
That component is a DLL : msmdpump.dll.
There are various procedures available that are well explained :
http://bloggingabout.net/blogs/mglaser/archive/2008/08/15/configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx
Do not forget to add a role in the database with appropriate permissions. And to add
the user you’re running the program with to that role. I got stuck several days figurint out what was wrong with my program, not knowing much about Analysis Services database configuration. Here i created the XMLA_Access role.
If you have configured the XML/A HTTP access correctly, you should be able to connect by specifying http://localhost/olap/msmdpump.dll as the server name. Here is a screenshot of SQL Server 2008 Management Studio which is used to administrate, browser, query the Analysis Services database.
As far as I understand, XML/A is a protocol and MDX is a query language. Someone correct me if i am wrong.
To connect from Java, you need the OLAP4J driver which is available here : http://www.olap4j.org
I ran the following program from Eclipse, in a machine running Windows Server 2008 Standard, in the sample
database provided by Microsoft . With Java 1.6.
package tests;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;import org.olap4j.Cell;
import org.olap4j.CellSet;
import org.olap4j.OlapConnection;
import org.olap4j.OlapException;
import org.olap4j.OlapStatement;
import org.olap4j.OlapWrapper;
import org.olap4j.Position;
import org.olap4j.metadata.Member;public class connexionSSAS {
public static void main(String[] args) throws ClassNotFoundException, OlapException {
Class.forName(“org.olap4j.driver.xmla.XmlaOlap4jDriver”);
Connection connection = null;;
try {
connection = DriverManager.getConnection(
“jdbc:xmla:Server=http://localhost/olap/msmdpump.dll;”
+ “Catalog=Adventure Works DW 2008 SE”);
} catch (SQLException e) {
e.printStackTrace();
}
if (null== connection){
System.out.println(“C nul”);
}
OlapWrapper wrapper = (OlapWrapper) connection;
OlapConnection olapConnection = null;
try {
olapConnection = wrapper.unwrap(OlapConnection.class);
} catch (SQLException e) {
e.printStackTrace();
}
OlapStatement statement = null;
CellSet cellSet = null;
try {
statement = (OlapStatement) olapConnection.createStatement();
cellSet =
// statement.executeOlapQuery(
// “SELECT NON EMPTY { [Measures].[Customer Count] } ON COLUMNS FROM [Adventure Works] “);statement.executeOlapQuery(“SELECT NON EMPTY { [Measures].[Internet Gross Profit] } ON COLUMNS, NON EMPTY { ([Promotion].[Promotions].[Promotion].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS “);
} catch (SQLException e) {
e.printStackTrace();
}System.out.println(“Current Java version is: ” + System.getProperty(“java.version”));
for (Position row : cellSet.getAxes().get(1)) {
for (Position column : cellSet.getAxes().get(0)) {
for (Member member : row.getMembers()) {
System.out.println(member.getUniqueName());
}
for (Member member : column.getMembers()) {
System.out.println(member.getUniqueName());
}
final Cell cell = cellSet.getCell(column, row);
System.out.println(cell.getFormattedValue());
System.out.println();
}
}
}
}
The output is :
Current Java version is: 1.6.0_04
[Promotion].[Promotions].[Category].&[Reseller].&[New Product].&[13]
[Measures].[Internet Gross Profit]
€5 618,10[Promotion].[Promotions].[Category].&[Reseller].&[New Product].&[14]
[Measures].[Internet Gross Profit]
€11 727,72[Promotion].[Promotions].[Category].&[Reseller].&[Volume Discount].&[2]
[Measures].[Internet Gross Profit]
€857 661,80
And here are the various jars i needed to include in the project :
Hope it helps some folks out there !