Fermasoft.com

Today we're talking about Pentaho Mondrian and how to deploy and create the demo suite on a linux box.

I think the instructions I found online to be, at best, confusing. The best bet you have is going with the docs that come along the package ... but there are some mistakes there too .

First things first . I would suggest to download a separate tomcat instance for your tests then, when you're ready and satisfied with the configuration of your mondrian demo, deploy into your "production" tomcat/servlet container.

For my tests and for the sake of these mini how-to, I have used tomcat 6, Sun Jdk 6 and MySQL ( installed using apt-get ).The other thing you need is the MySQL jdbc : download it and put it somewhere on your machine - I have a ~/libs/java dir where I store all my java libraries .

Unzip and start tomcat and fire mysql; test that tomcat responds by looking at http://localhost:8080 ( change the port if you modified the server configuration ) then test that mysql is up and running by issuing mysqlshow -u root mysql . Once you are sure you have everything installed, up and running you can download Mondrian from SourceForge.

Unzip your mondrian archive file somewhere. For my tests I used ~/devel/ETL. Wherever you unzipped your files you should have a tree that looks like this :

+-- demo
|   +-- access
+-- doc
|   +-- api
|   |   +-- mondrian
|   |       +-- calc
|   |       |   +-- impl
|   |       +-- gui
|   |       |   +-- validate
|   |       |       +-- impl
|   |       +-- i18n
|   |       +-- mdx
|   |       +-- olap
|   |       |   +-- fun
|   |       |   |   +-- extra
|   |       |   |   +-- vba
|   |       |   +-- type
|   |       +-- olap4j
|   |       +-- parser
|   |       +-- recorder
|   |       +-- resource
|   |       +-- rolap
|   |       |   +-- agg
|   |       |   +-- aggmatcher
|   |       |   +-- cache
|   |       |   +-- sql
|   |       +-- server
|   |       +-- spi
|   |       |   +-- impl
|   |       +-- test
|   |       |   +-- build
|   |       |   +-- clearview
|   |       |   +-- comp
|   |       |   +-- loader
|   |       +-- tui
|   |       +-- udf
|   |       +-- util
|   |       +-- web
|   |       |   +-- servlet
|   |       |   +-- taglib
|   |       +-- xmla
|   |           +-- impl
|   |           +-- test
|   +-- images
+-- lib

under the lib dir there's what you're looking for : mondrian.war. Now, stop tomcat and unzip mondrian.war into <TOMCAT_HOME>/webapps . At this point you need to create the sample database for your mondrian installation. The Mondrian demo comes with a foodmart database ... in ACCESS ( OMG ! ) format. Thanks God they provided a plain SQL file that you can upload into your mysql ... not ! What they forgot to tell you is that :

1. you have to create the schema yourself
2. you have to create the user yourself
3. the instructions don't work

Let's proceed :

open your mysql console ( as root ) and issue the following :

create database foodmart;
grant all privileges on foodmart.* to foodmart@localhost identified by 'foodmart';

the first line creates a schema 'foodmart', the second line creates a user 'foodmart' with password 'foodmart' that can do anything on the 'foodmart' schema when connecting from localhost.

Now, exit the mysql root shell and test your new user : mysql -u foodmart -p type 'foodmart' (or whatever password you chose) when promped and see if you can connect. If everything went fine you can now exit the console.

Now it's time to fill the demo database : go to <TOMCAT_HOME>/webapps/mondrian/WEB-INF and issue the command :

java -cp "./lib/mondrian.jar:./lib/olap4j.jar:./lib/log4j-1.2.8.jar:./lib/commons-logging-1.0.4.jar:./lib/eigenbase-xom.jar:./lib/eigenbase-resgen.jar:./lib/eigenbase-properties.jar:<WHERE YOU HAVE UNZIPPED YOUR MYSQL JDBC>/mysql-connector-java-3.1.14-bin.jar" mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes -jdbcDrivers=com.mysql.jdbc.Driver -inputFile=<WHERE YOU HAVE UNZIPPED MONDRIAN>/mondrian/demo/FoodMartCreateData.sql -outputJdbcURL="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart"

As I said before, I have my mysql jdbc library in ~/libs/java, change

<WHERE YOU HAVE UNZIPPED YOUR MYSQL JDBC>/mysql-connector-java-3.1.14-bin.jar

into whatever suits your configuration. When you get the prompt again you're almost there : your database is created but you still need to configure your mondrian installation to use mysql ( by default it tries a jdbc/odbc bridge connection to the above mentioned Access database ) .

Now, go to <TOMCAT_HOME>/mondrian/WEB-INF/ and change web.xml so that every instance of

<param-value>@mondrian.webapp.connectString@</param-value>

is migrated to

<param-value>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart?user=foodmart&#38;password=foodmart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver;</param-value>

Edit mondrian.properties ( same directory ) and change the variable mondrian.test.connectString

mondrian.test.connectString=Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/FoodMart.xml;

now you have to tell the same to the jsp pages : goto queries sub directory and change the following jsps :

1. arrows.jsp
2. colors.jsp
3. fourhier.jsp
4. mondrian.jsp

so that the tag jp:mondrianQuery looks like :

<jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml">   

now ... fire tomcat and open mondrian demo page and enjoy !

the references

• I found that the best bits of information for mondrian deployment are shipped together with the zip under <WHERE YOU HAVE UNZIPPED>/doc.
• In order to better understand what the jpivot library ( shipped together with the mondrian demo ) was doing I found this page from ETL-Tools.Info as being of great use

Said that : happy cubing !