Introduction
In my current company, we have our own propriety olap server. The requirement that came up lately was to support Microsoft Excel 2010. Since we already support Microsoft excel 2007, we had to identify difference between these version. To query our server we used XMLA (server side using olap4j) with Simba O2X as excel and xmla bridge.
There are stark differences between 2007 and 2010 especially in relation to filtering. This is a topic for another blog. To perform the analysis, we wanted to have open source OLAP server deployed, to which we can fire queries from excel. Mondrian was the answer. Our requirement was just to use food mart example in excel.
Problems
- Mondrian latest version does not have food mart example.
- The previous version 3.5.6 of embedded mondrian does not work out of box. This page does not help completely either.
I like to share my experience with Mondrian 3.5.6.
How I solved it
I had apache tomacat 5.0 previously installed. As described in this page, i expanded my embedded-mondrian jar file in webapps folder. Since I was not really interested in having a production data base, I was more than happy to run it against apache derby. This embedded mondrian is available from git repository of mondrian,
- MondrianXMLAServlet not available, Solution: Edit web.xml to have mondrian.xmla.impl.MondrianXmlaServlet as MondrianXMLAServlet instead of mondrian.xmla.impl.DefaultXmlaServlet. The later is abstract class.
- Class not found error, "mondrian.test.DiffRepository" , Solution: clone mondrian repository, deploy it on your eclipse and pick the class from eclipse compiled classes.
URL of XMLA servlet was wrongly specified, correct it as per your local tomact deployment. This is the URL which excel will use to connect to your XMLA servlet. - Wrong connection URL in datasource.xml. You can paste the following directly.
<DataSourceInfo>Provider=mondrian;Jdbc='jdbc:derby:classpath:/foodmart';Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=org.apache.derby.jdbc.EmbeddedDriver;JdbcUser=sa;JdbcPassword=sa;PoolNeeded=false;</DataSourceInfo>
Bingo! I was able to connect my excel 2007 with local mondrian food mart example. For connection with Excel 2010, you have to specify the wrong URL the first time so that a modified window opens up which allows you to select the catalog as well. Specify the correct URL now and catalog list will be populated. Select FoodMart catalog and you are good to go.
