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 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
webappsdirectory - 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
51 comments:
thank you very mucg !!!!
is there a solution to display all soap log results (discover/execute) on the server console on each call??
@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.
You just made my day, thanks a lot
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?
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?
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
@Dimas, thanks! Let's continue discussion on github.
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
Thanks @Nathan!
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.
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
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
@Anonymous - sorry your request is too vague. I don't know where to start answering it.
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
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)
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
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?
I am trying to connect MySQL database as provided in the link But i could't connect DB, i have faced following exception.
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!
@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
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.
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
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
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)
@Javier,
I'm sorry man. Not sure. Looks like you need more memory. This is not specific to the war.
@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
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
@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.
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.
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
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.
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.
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.
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?
how can I use olap4j api for xmondrian
Hi Roland, thank you very much, your xmondrian its great!!! works fine on a xammp stack !! you help me a lot.
Regards.
Hi Roland, how can I get to make Mondrian 4 run with xMondrian?
Hi! I haven't touched it in ages. Might be better of giving it a try yourself.
Hi Roland, how can I connect xMondrian to Postgres?
PLease checkout out the mondrian/pentaho documentation. Thank you.
Hello Roland, does Xavier support MDX queries? Is it possible to use any other client? Thank you!
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.
Thanks, is there a way to autogenerate a schema from a postgres db?
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.
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.
@Anonymous, please review the Mondrian / Pentaho documentation. Or find a forum. thank you.
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
Thanks @Artoo!
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
@Almani74, please check @Artoo's comment, and try the same. Thank you.
Post a Comment