30 April, 2008

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.


10 comments:

Anonymous said...

JRubik has built-in XMLA capabilities, and is opensource. The version that was released in May 2008 has too many bugs to be usable (by me anyway), so I'd recommend using the version before that.

Also, if you pursue it, when you attempt to visit the sourceforge page for it from the jrubik site, there's a spelling error in the link that'll take you to a porn site. You need to change 'sourcefoge' to 'sourceforge' in the URL.

-Brian

Feris Thia said...

Hi Brian,

Thank you for the information and the update.

Will check on the product.

Regards,

Feris Thia

siladitya said...

Hi,
It's Aditya.I'm new to this mondrian.Me using cube generater to create MDX query.But I don't know how to create dynamic dimensions.Like i have a column in my table as 'adddate'.I want to create dimension like addYear,AddMonth by writing syntax "year(adddate) as AddYear" and ,"month(adddate) as AddMonth".
But this syntax is not working.
Pls help me to solve this problem.
U can contact me on siladitya@webedgeindia.com

thanx....

siladitya said...

Hi,
I'm new to this pentaho and these pentaho related tools.Can somebody help me to call procedures in generating the reports and creating aggregate tables to use in cube generator and workbench.

thanx....

Unknown said...

getting the following error after followed steps in this page.



The Mondrian XML: Access denied for data source needing authentication




anyone please help on this?

Anonymous said...

Thank you for this nice article.

Just one short note (which would have saved me some hours): There are problems with XML/A in the mondrian-embbeded distribution. See: http://jira.pentaho.com/browse/MONDRIAN-438

So one should solve this problems first or use the standard mondrian distribution.

Anonymous said...

Im totaly new in this field so I beg for some understanding :)

very good article by the way.

I just dont get it one thing.

I know that I should configure web.xml, datasource.xml and mondrian.properties

But, these files, are they from my Mondrian aplication or from my own aplication.

As I figure out, I should configure Mondrian, and from my application I only have this xmlaQuery that should hit Mondrian OLAP server(application).

Am I right?
Any help is welcomed.
if its easier you can write me on my email: perasr2003@yahoo.com

Thanks

sunmoon said...

Thank for you and your ideas.

Now I've configured and able to access Mondrian service.

If I connect this Mondrian Service using AdodmdConnection() then it connected. But some of the property collection not initialized with Mondrian data.

For Ex., NumericPrecision, NumericScale and Units for a Measure and Expression, Description for a NamedSets.

If I get those information then I'm getting exception like "PRECISION" is not recognized.

Do you have any idea ??

Just Another Blogger said...

Hi,

@All : Sorry for late replies.

@Sunmoon : For the development, I'm not quite familiar with the API. Sorry can't help too much on this.

Regards,

Feris

Unknown said...

I saw that you are interested in mondrian, olap4j and XMLA. Did you know about the XMLA connector that brings mondrian data to Excel? You can see how it works here: http://youtu.be/-m64VsOvvkw