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.

51 comments:

Unknown said...

thank you very mucg !!!!

Unknown said...

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

rpbouman 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.

Unknown 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?

rpbouman 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

rpbouman 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


rpbouman 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.

rpbouman 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

rpbouman 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

Unknown 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!

rpbouman 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.

Anonymous said...

This is a great tool, Roland. I am getting the same connection related error "The Mondrian XML: Mondrian Error:Internal error: No suitable connection found (00HSBE02)" and I could not find anything wrong, below is our connection string
Provider=mondrian;Jdbc=jdbc:hsqldb:res:/hsqldb-foodmart/foodmart;JdbcDrivers=org.hsqldb.jdbc.JDBCDriver

In fact, I haven't changed anything it is just the default data source file came with .war

alpaga said...

Hi Roland,

Thank you for your work, it helps me a lot. For people facing this issue "No suitable connection found", try to change the jvm parameter in tomcat (initial memory parameter / maximum memory pool) because when you look at mondrian logs, it hides a jvm outofmemory exceptio at startup. After restarting tomcat it works perfectly.

Thanks

Unknown said...

Hello Roland,

I am trying to run xmondrian but as some of the comments before mine I am having this error:
Error: SOAP-ENV:Server.00HSBE02: XMLA Discover unparse results error
Error: The Mondrian XML: Mondrian Error:Internal error: No suitable connection found (00HSBE02)
I have changed the DataSourceConfig path as @Nathan said because I am using windows and also chaged the jvm memory options trying to fix the OutOfMemory error below, but it still persists. I dind´t touch the code apart from that. May I be losing something?

Thank you so much in advance.



2018-05-03 12:21:00,975 INFO [org.apache.commons.vfs2.impl.StandardFileSystemManager] Using "C:\path\to\Tomcat 9.0\temp\vfs_cache" as temporary files store.
2018-05-03 12:21:00,975 INFO [org.apache.commons.vfs2.impl.StandardFileSystemManager] Using "C:\path\to\Tomcat 9.0\temp\vfs_cache" as temporary files store.
2018-05-03 12:27:05,867 INFO [hsqldb.db.HSQLDB5187597468.ENGINE] Database closed
2018-05-03 12:27:05,871 FATAL [hsqldb.db.HSQLDB5187597468.ENGINE] could not reopen database
org.hsqldb.HsqlException: java.lang.OutOfMemoryError: Java heap space
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Database.reopen(Unknown Source)
at org.hsqldb.Database.open(Unknown Source)
at org.hsqldb.DatabaseManager.getDatabase(Unknown Source)
at org.hsqldb.DatabaseManager.newSession(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.(Unknown Source)

rpbouman said...

@Javier,

I'm sorry man. Not sure. Looks like you need more memory. This is not specific to the war.

Unknown said...

@Roland

I could not solve the problem on my Windows but I installed an Ubuntu partition on my pc and now it works perfectly. Anyway, thank you for your fast response and for to share this project.

Live long and prosper :D

Unknown said...

Hi Roland, shouldn't you access Mondrian/XMLA through Olap4J? I thought that after Mondrian version 3 the only way to execute XMLA requests was through Olap4J.

Thanks
Pablo

rpbouman said...

@pablo S,

Last time I checked the olap4j servlet had some issues. Like this https://jira.pentaho.com/browse/MONDRIAN-2108

If you manage to get it working, then I'd be happy to learn from you how to do it.

George Ts said...

Hi Roland.

In mondrian.log i'm getting the following errors:
2018-09-20 00:41:57,720 WARN [mondrian.olap.MondrianServer] Failed getting connection. Skipping
mondrian.olap.MondrianException: Mondrian Error:Internal error: Virtual file is not readable: C:/Program Files/Apache Software Foundation/Tomcat 9.0/webapps/xmondrian/WEB-INF/schema/test_schema.xml

Could please provide any feedback that could help me identify why Xmondrian cannot connect to my database and why my schema file is "unreadable"?

Thank you in advance!
Regards.

rpbouman said...

Hi George TS,

this is all very mondrian specific - I don't think it has to do with XMondrian directly. Please try the mondrian mailing list.

HTH

Roland

Ramesh G said...

Hi Roland,

How to connect mondrian server purely on client side (XML/A acces by Ajax)?

I have posted my query in stack overflow. Can you suggest me how to get result?

https://stackoverflow.com/questions/55156755/how-to-connect-the-mondrian-server-purely-on-client-side-xml-a-access-by-ajax

Regards,
Ramesh G.

rpbouman said...

Hi Ramesh,

did you experience this error with XMondrian and the packaged xmla4js client?

In the SO post, you're using naked xhr and a manually assembled SOAP message.
I suggest trying with xmla4js, and if that works, compare the messages.

HTH,

cheers.

Ramesh G said...

Hi Roland,

Thank you for your reply.

Yes, i have faced the error in XMondrian with packaged xmla4js client. I can see the folder 'xmla4js' inside the extracted folder 'xmondrian' in tomcat webapps directory.

Then i have tried below code with XMLHttpRequest option which was getting from your presentation about XMLA4js.
https://wiki.pentaho.com/download/attachments/14846971/Xmla4js.pdf?version=1&modificationDate=1263491311000&api=v2

var url = "http://localhost:8080/xmondrian/xmla";
var datasource = "FoodMart";
var catalog = "FoodMart";
var mdx = "SELECT [Measures].[Profit] ON COLUMNS," +
" [Product].[All Products].Children ON ROWS " +
"FROM [Sales]";
var request = "" +
" " +
" " +
" " +
" " + mdx + "" +
" " +
" " +
" " +
" " + datasource + "" +
" " + catalog + "" +
" Multidimentional" +
" " +
" " +
" " +
" " +
"";
var xhr = new XMLHttpRequest();
xhr.open("POST", url, false);
xhr.setRequestHeader("Content-Type", "text/xml");
xhr.send(request);
var response = xhr.responseXML;

But, i am getting null value in xhr.responseXML.

configuration is follows,

TomCat 8.5,
MySQL 5.2,
jdk1.8.0_144,
MySQL 5.14 JDBC Driver,

Can you suggest me to get the response ?

Thanks and Regards,
Ramesh G.

rpbouman said...

Ramesh,

I still don't understand. You're getting the error also with xmla4js? If so then what is the xmla4js code you're using to do the request?

Are any of the applications that you can find on the xmondrian/index.html page working?

Are other Mondrian / XML?A applications working?

Anonymous said...

how can I use olap4j api for xmondrian

Anonymous said...


Hi Roland, thank you very much, your xmondrian its great!!! works fine on a xammp stack !! you help me a lot.

Regards.

Anonymous said...

Hi Roland, how can I get to make Mondrian 4 run with xMondrian?

rpbouman said...

Hi! I haven't touched it in ages. Might be better of giving it a try yourself.

Anonymous said...

Hi Roland, how can I connect xMondrian to Postgres?

rpbouman said...

PLease checkout out the mondrian/pentaho documentation. Thank you.

Anonymous said...

Hello Roland, does Xavier support MDX queries? Is it possible to use any other client? Thank you!

rpbouman said...

Xavier generates MDX queries, so you don't have too.

XMondrian also comes with xmlash, which is a browser based XML/A shell - it's an interactive query tool where you can enter your own mdx queries.

Anonymous said...

Thanks, is there a way to autogenerate a schema from a postgres db?

rpbouman said...

There is always a way. Pentaho used to have some automodeling features - not sure if they are still there.

Mondrian schemas (3) are not very complicated - you can quite easily generate them from the information schema, certainly if it's just for one table. If you have good naming conventions, then generating more complex schemas is possible too.

If you want to go into more detail, then feel free to email me roland.bouman@gmail.com
Depending on what you need we can work out some arrangement.

Anonymous said...

Good morning Roland,

I am struggling to define a schema, in particular I need to define a dimension without Hierarchies, this dimension has two flat attributes, such as a Name and a Code, so I don't need an Hierarchy. How can I shape it? Thanks in advance.

rpbouman said...

@Anonymous, please review the Mondrian / Pentaho documentation. Or find a forum. thank you.

Artoo said...

1. Download and install the Tomcat on Apache appliance from Turnkey Linux. I installed it as a virtual machine on VirtualBox. Open up the appliance and follow the prompts to set up passwords, download security patches etc.
2. Access the virtual machine in your browser, and navigate to the Web Apps Management Screen
3. Use the Deploy functionality to select xmondrian.war from your host computer and upload and deploy it as a webapp on your tomcat server
4. Edit the mod_jk.conf file located in etc/tomcat8. Add in the following lines at the end of the file
JkMount /xmondrian ajp13_worker
JkMount /xmondrian/* ajp13_worker
5. Go back to the Tomcat webapp management page and click on the xmondrian page – this should now take you to the main page
6. Access the virtual machine on your browser, and navigate to the webmin section
7. In the MySQL server settings, create a new database called foodmart, and a new user – username=foodmart, password=foodmart
8. Use the FoodMartLoader.sh to build the rest of the database
9. Locate the JDBC driver located at /usr/share/java/mysql-connector-java-5.1.42.jar and copy it to /var/lib/tomcat8/webapps/xmondrian/WEB-INF/lib
10. Locate the datasources.xml file in /var/lib/tomcat8/webapps/xmondrian/WEB-INF
11. Replace the DataSourceInfo element with the following
Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/schema/Foodmart.xml
12. Access the virtual machine on your browser, navigate to the webapp management section and restart xmondrian
13. Click on XMLASH or XAVIER and get slicing

rpbouman said...

Thanks @Artoo!

Almani74 said...

Hi Roland I am trying to connect it with mysql database; but it is giving an error that is as below.
No suitable connection found.

my data-source configuration is as below.

Provider=mondrian; JdbcUser=root; JdbcPassword=root; Jdbc=jdbc:mysql://localhost:3306/newDB;JdbcDrivers=com.mysql.jdbc.Driver

I also have tried mysql connection information as we do spring boot and pure java JDBC.

But No luck can you please provide a little info about connection with mysql or oracle
Thanks IN Advance

rpbouman said...

@Almani74, please check @Artoo's comment, and try the same. Thank you.

UI5 Tips: Persistent UI State

This tip provides a way to centrally manage UI state, and to persist it - automatically and without requiring intrusive custom code sprinkle...