Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Saturday, March 19, 2016

Need a Mondrian .WAR? Check out XMondrian.

To whom it may concern, this is a quick note to bring the xmondrian project to your attention.

Introduction: Open Source OLAP, Mondrian, Pentaho, and JasperSoft

Mondrian is the open source OLAP engine. Mondrian provides:
  • a multi-dimensional view of a relational database (ROLAP)
  • a MDX query engine
  • Clever, advanced caching layers to speed up OLAP query performance (making it a MOLAP/ROLAN hybrid i.e., HOLAP)
  • Standards compliant OLAP data access by providing XML for Analysis (XML/A) and OLAP4J access APIs
Mondrian was designed and invented by Julian Hyde, who acted as technical and architectural lead of the Mondrian project for many years.

Mondrian was adopted by Pentaho, and is included in the Pentaho BI Stack as Pentaho Analysis Services. Mondrian is also the OLAP engine that ships with the Tibco/JasperSoft Reporting server, and with Meteorite BI's Saiku product.

Running Mondrian Standalone

While Pentaho, Jaspersoft and Meteorite all do a good job of integrating Mondrian inside their respective BI servers, some people would like to run only Mondrian directly in their java servers. The Mondrian project used to make that quite easy, since it shipped a .WAR (web-archive) file containing Mondrian itself, documentation, sample cubes, and the JPivot mondrian client.

Unfortunately, the Mondrian project stopped supporting the .WAR and sample content. This happpened a while ago already, but there are still people that are finding out about it only now. This might have to do with the fact that the Mondrian documentation has not been very well maintained and still refers to the .WAR as if it is part of the Mondrian project.

Introducing XMondrian

I felt the need to have a Mondrian .WAR myself. Main reason is that I created a couple of OLAP client tools myself, and I want to provide potential users with a quick and easy path to check them out. So, I decided to pack them all in a .WAR, together with Mondrian, the Foodmart Sample cube, and an embedded dataset.

The result is called xmondrian which you can find on github.

Getting started with XMondrian

Getting started with XMondrian is easy:
  • Download the .WAR file
  • Deploy to your java server. In theory, the process to do that will be dependent upon which webserver you are running. I tried with Apache Tomcat, Jetty, and Tiny Java Web Server, and for all these products you can simply copy the .WAR to the webapps directory
  • Find the XMondrian homepage by navigating your browser to the xmondrian webapp. For example, suppose you installed Tomcat or Jetty locally, using the default port of 8080, then http://localhost:8080/xmondrian will bring you there.

What's inside XMondrian

Once you're on the XMondrian homepage, you can find more information about what's inside, but I'll summarize below:
  • Mondrian 3.12
  • A web.xml to instantiate and hook up the MondrianXmlaServlet. After installation of xmondrian, your webserver can receive XML/A requests via /xmondrian/xmla
  • HSQLDB embedded database engine
  • Sample Datasets and Schemas Both the Foodmart and Steelwheels datasets are included as embedded hsqldb database in a .jar file. There are predefined Mondrian Schema files for each dataset as well, which specify how these databases are mapped to cubes, measures, dimensions, etc. Finally, there are datasource files that tell mondrian to connect to the sample database and use the respective schema file
  • xmla4js - A javascript XML/A client library. You can use this in browser-based web applications to communicate with Mondrian via the XML/A protocol. Xmla4js ships with code samples as well as API documentation
  • Client Applications
    • XMLash - XML/A Shell: an interactive MDX command line interface for inspecting Mondrian schema objects, an for creating and running MDX queries. (See a demonstration )
    • Xavier - XML/A Visualizer: an interactive OLAP ad-hoc reporting and charting tool with a graphical user interface

The XML/A Shell Application

:

The XML/A Visualizer Application

:

Finally

I hope this post was useful to you. Please let me know how you get along with the xmondrian .WAR. I'm open to suggestions and I would love to collaborate to make xmondrian better. Please use the github issue tracker to provide your feedback. Thanks for your time and interest.

23 comments:

Macray Leo said...

thank you

Mouadh KAABACHI said...

thank you very mucg !!!!

Mouadh KAABACHI said...

is there a solution to display all soap log results (discover/execute) on the server console on each call??

Roland Bouman said...

@Mouadh,

check out the WEB-INF/classes dir of the extracted XMondrian web application. You'll notice it has a log4j.xml configuration file.

XMondrian is pre-configured to write mondrian, mdx and sql logs to the logs directory. You should see files in there like mondrian.log, mondrian-mdx.log and mondrian-sql.log

If the log levels are not to your liking you could experiment by changing the settings in the log4j.xml file. If you need advice on how to set the log levels, please find suppport on the mondrian mailing list.

HTH, Roland.

Mouadh KAABACHI said...

You just made my day, thanks a lot

dimas said...

Hi Roland, I took your solution, but got an error at run time, which I can not understand. Could you post the full source code of the solution, not only WAR-file?

Roland Bouman said...

Hi Dimas,

for source code, please checkout the mondrian project https://github.com/pentaho/mondrian. That's not my code and I don't think I should redistribute it.

The only thing I'm doing is pulling mondrian jars together. I'm doing that with a simple ant script. It's not particularly valuable to me but it's also not of very high quality that's why I'd rather not share it.

What error message are you getting btw?

dimas said...

I described it on github. https://github.com/rpbouman/xmondrian/issues/3

Most likely I have not installed (or not available), some dependencies needed to run... but I can not understand what.

The environment in which I have tried:
Windows; Java 1.7; Tomcat 8.5

Roland Bouman said...

@Dimas, thanks! Let's continue discussion on github.

Nathan said...

Just a note:-

If using Windows; in web.xml relying on the default path for datasources.xml doesn't seem to work.

Uncomment init-param as follows:


DataSourcesConfig
file:///C:/path/to/tomcat/webapps/xmondrian/WEB-INF/datasources.xml


Roland Bouman said...

Thanks @Nathan!

Anonymous said...

Hi Roland, I have installed older versions of mondrian, and able to open the page http://localhost:8080/mondrian/

However I don't know how do I view my olap cubes created in my Schema workbench.

Or is there any way to create olap cubes here on the web application?

Any help is highly appreciated.

Roland Bouman said...

Hi @anonymous,

It really is not a big deal. Just copy your schema to the java web server where modnrian is running and edit the datasources.xml file.

See http://mondrian.pentaho.com/documentation/installation.php#5_1_Describe_the_data_sources_in_datasources.xml

Anonymous said...

Hello Roland
I am very new to this field plz help me out.. I want to develop a web application that can use xmondrian in the bckgrnd.. but i dnt know how to begin plzzz help me plzz

Roland Bouman said...

@Anonymous - sorry your request is too vague. I don't know where to start answering it.

Anonymous said...

Hello Roland
Actually i want to do OLAP for text fields in the database.. and the text dimension hierarchy is constructed using probabilistic topic model.. the problem is i want to display word distribution (output of topic modeling) as the measure in the olap cube.. or if that is not possible I would like to display it near the cube on the same web page.. please help me Roland

Anonymous said...

I am not able to explore Mondrian by playing with the sample applications and data due to this error.. both the Xmlash and Xavier are not opening..
Initializing Datasources. This may take a while, please wait.
Trying url 1 of 1: http://localhost:8080/xmondrian/xmla
Error: SOAP-ENV:Server.00HSBE02: XMLA Discover unparse results error
Error: The Mondrian XML: Mondrian Error:Internal error: No suitable connection found (00HSBE02)

Anonymous said...

Good afternoon.

I'm getting the same error:
Initializing Datasources. This may take a while, please wait.
Trying url 1 of 1: http://192.168.180.128:8080/webapps/xmondrian/xmla
Error: SOAP-ENV:Server.00HSBE02: XMLA Discover unparse results error
Error: The Mondrian XML: Mondrian Error:Internal error: No suitable connection found (00HSBE02)

In the Tomcat logfile bin/logs/mondrian.log, at startup, it gives the error:
2017-01-31 13:10:20,447 WARN [mondrian.xmla.XmlaServlet] Use default character encoding from HTTP client
2017-01-31 13:10:20,497 INFO [mondrian.olap.MondrianProperties] Mondrian: properties loaded from 'file:/srv/tomcat/apache-tomcat-8.5.5/notWebapps/xmondrian/WEB-INF/classes/mondrian.properties'
2017-01-31 13:10:20,498 INFO [mondrian.olap.MondrianProperties] Mondrian: loaded 0 system properties
2017-01-31 13:10:20,741 INFO [mondrian.rolap.RolapUtil] Mondrian: JDBC driver org.hsqldb.jdbc.JDBCDriver loaded successfully
2017-01-31 13:10:20,813 INFO [org.apache.commons.vfs2.impl.StandardFileSystemManager] Using "/srv/tomcat/tomcat8_5/temp/vfs_cache" as temporary files store.
2017-01-31 13:10:36,026 FATAL [hsqldb.db.HSQLDB5187597468.ENGINE] statement error processing log /hsqldb-foodmart/foodmart line: 1604
org.hsqldb.HsqlException: file input/output error
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.scriptio.ScriptReaderText.readLoggedStatement(Unknown Source)
at org.hsqldb.persist.ScriptRunner.runScript(Unknown Source)
at org.hsqldb.persist.ScriptRunner.runScript(Unknown Source)
at org.hsqldb.persist.Log.processLog(Unknown Source)

. . .

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.io.IOException: Stream closed
at java.util.zip.InflaterInputStream.ensureOpen(InflaterInputStream.java:67)
at java.util.zip.InflaterInputStream.read(InflaterInputStream.java:142)
at org.apache.catalina.webresources.AbstractArchiveResource$JarInputStreamWrapper.read(AbstractArchiveResource.java:255)
at java.io.BufferedInputStream.fill(BufferedInputStream.java:246)
at java.io.BufferedInputStream.read(BufferedInputStream.java:265)
at org.hsqldb.lib.LineReader.readLine(Unknown Source)
... 71 more


Thanks

prabu samynathan said...

Hi Roland,

I am trying to connect MySQL database as provided in the link (http://business-intelligence.phi-integration.com/2008/04/mondrian-mysql-setup.html) using this XMondrian. But i could't connect DB, i have faced following exception.

Trying url 1 of 1: http://localhost:8080/xmondrian/xmla
Error: SOAP-ENV:Server.00HSBE02: XMLA Discover unparse results error
Error: The Mondrian XML: Mondrian Error:Internal error: No suitable connection found (00HSBE02)


My datasource.xml is,




Provider=mondrian;DataSource=FoodMart;
Mondrian FoodMart Data Warehouse
http://localhost:8080/xmondrian/xmla
Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=root;JdbcPassword=root;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/schema/Foodmart.xml;
Mondrian
MDP
authenticated


/WEB-INF/schema/Foodmart.xml





I don't know what i am missing in my configuration, while i am using mondrian.war(3.5.0) its working but not for xmondrian, Can you help me?

osclass plugins said...

I am trying to connect MySQL database as provided in the link But i could't connect DB, i have faced following exception.

Krone said...


Roland, hi we've installed your xmondrian project, and we're almost there but we're having a tough time trying to connect it with MS SQL and our Datawarehouse.
We keep getting this errors:

-Error: The Mondrian XML: Mondrian Error:Internal error: No suitable connection found (00HSBE02)

-2017-05-19 13:27:30,538 WARN [mondrian.olap.MondrianServer] Failed getting connection. Skipping

we've tried jtds.jar and sqljdbc42.jar drivers both to no avail. Any help at least regarding the factibility of using xmondrian with MS SQL would be really appreciated. Let us know if we can send you more info, what would be helpfull to have. Thanks!

Roland Bouman said...

@Krone,

please let me know:
- where did you put the driver jar?
- did you restart the server after putting the jar there?
- what did you modify in the datasources file to make the connection?

you can email it to me privately if you like roland.bouman@gmail.com

Krone said...

Roland, thanks for your reply. Minutes ago we finally managed to make it work, just to help others the main difficulties we had were related to:
- ";" at the end of the connection string in DatasourceInfo section inside the datasource.xml, there should be NO ";"
- Case sensitiviness in the Schema (mainly referring to property names)
- Spaces separing attributes in Levels definitions
- Even though there may be errors only in the schema file, you still get "Error: The Mondrian XML: Mondrian Error:Internal error: No suitable connection found (00HSBE02)" errors that seem to be connection related

That's all, now we are running fine (and very happy by the way)

Thanks again for everything.