Mondrian as XMLA Provider

Mondrian is an open source Java OLAP server under Pentaho. One of its great feature is that it can be act as XML/A (XML for Analysis) server that can be consumed by a number of independent XML/A compliant clients.

From Mondrian's documentation I find it quite simple to set up Mondrian as XMLA provider. But I didn't find any adequate information on how to test it directly from Mondrian's own bundled tool.

For any beginner, it will be hard and time consuming to find out whether the provider is already and rightly set up.

I write this article in order to help those with that problem, complementing Mondrian's own documentation .

Setup

Open your /WEB-INF/datasources.xml file and add a data source :

<?xml version="1.0"?>
<DataSources>
<DataSource>
<DataSourceName>
Provider=Mondrian;DataSource=MySQLTest;</DataSourceName>
<DataSourceDescription>Mondrian FoodMart Data
Warehouse</DataSourceDescription>
<URL>http://localhost:8080/mondrian/xmla</URL>
<DataSourceInfo>
Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=root;JdbcPassword=;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>
<ProviderName>Mondrian</ProviderName>
<ProviderType>MDP</ProviderType>
<AuthenticationMode>Unauthenticated</AuthenticationMode>
<Catalogs>
<Catalog name="FoodMart">
<Definition>/WEB-INF/queries/FoodMart.xml</Definition>
</Catalog>
</Catalogs>
</DataSource>
</DataSources>

DataSourceName tag

Although DataSourceName tag can have entry of any name - which can be detected by Mondrian form test - but we will use the "Provider=[value];DataSource=[value]" format instead.

Connection String

Since I use MySQL as my cube's storage that I setup before, so in this particular line I specified connection detail to the server. I specified in the JDBC's URL my host (localhost), database schema (foodmart_mondrian), user (root) with no password and the the driver class itself (com.mysql.jdbc.Driver).

Validate Data Source

I assume that you already have Mondrian running under Tomcat in your local computer. Try browse to http://localhost:8080/mondrian/xmlaTest.jsp. Select "1.discoverDataSource" option from the combo list then click "show request" button until you see the XMLA construct like below.

After that, you might proceed by clicking "Run" to see if your XMLA provider - specified in your DataSourceName - is recognized.

A screen shot below indicated that Mondrian XMLA provider is recognized and ready to be sent any OLAP/MDX query.

Query Testing

Back to your XMLA test page, select "2. executeHR-tabular option list. Click on "Show Request" button.

Once you see the XMLA generated construct you need to modify one thing: DataSourceInfo entry.

Change the tag's value of "DataSource=MondrianFoodMart;" into "DataSource=MySQLTest;". Continue by clicking on "run" button.

If all going right you will have a returning XML SOAP result like below. This means that Mondrian has successfully executed your query.

<_x005b_measures_x005d_._x005b_org_x0020_salary_x005d_ type="xsd:double">
39431.6712




<_x005b_employees_x005d_._x005b_employee_x0020_id_x005d_._x005b_member_caption_x005d_>
Sheri Nowmer

<_x005b_measures_x005d_._x005b_org_x0020_salary_x005d_ type="xsd:double">
39431.6712

Conclusion

Setup Mondrian as XMLA provider is easy and quite straightforward.

However, in order to test it we sometimes at a lost. Since we don't have any adequate information provided with the bundled documentation. This article will serve as a complement for that.

Hope you find this blog entry helpful. Any comment or suggestion to improve this article is highly appreciated.