Mondrian, Apache Tomcat and MySQL – Installation and Configuration on Linux

In this post, I am going to walk you through a manual installation and configuration of Mondrian with Apache Tomcat 6. I have always believed to have a clean install from source and thus I am going to follow the same here as well. The following procedure is what I have used in our production and is thoroughly tested in Ubuntu, CentOS and RHEL. Please post your comments/suggestions if you find any flaws in this.

Step – 1: Installing Sun-JDK
There have been many a times that I have faced unknown and hard to debug problems with the default installations of Sun-JDK which comes with Ubuntu/RHEL and other Operating Systems. Specially in production systems, I feel much more comfortable compiling things myself and thus if you are planning to make deployments in production system, it is better that you know what you are installing and where.

Download Sun-JDK

After you have downloaded you can run the following commands to install it.

# Change the jdk version to the latest you wish to install.
$ chmod +x jdk-6u17-linux-x64-rpm.bin 
# Running the binary would ask you set of questions and 
# you would be able to proceed with the install. 
$ sudo ./jdk-6u17-linux-x64-rpm.bin
# Open up your local bashrc profile and add the JAVA_HOME 
# path to persist the variable in your environment
$ vi ~/.bashrc
# At the end of this file add a line
export JAVA_HOME="/usr/java/jdk1.6.0_17"
# Save the file and run the following command to enable 
# the JAVA_HOME in the environment.
$ source ~/.bashrc
# Test the install by running the following command 
# and it would display you the version that is installed
$ java  -version

Step – 2 : Installing Tomcat
Tomcat is the servlet container and we are going to deploy Mondrian OLAP engine war on this.
Download Tomcat 6
After the download is done, run the following commands to get it up and running. Tomcat is a very simple install, just unzip and place it in the directory you wish to run it with. I am putting the installers right now in the HOME directory, but it is absolutely safe to give your own path, if you have permissions to put it there and execute.

$ tar -xf apache-tomcat-6.0.29.tar.gz -C $HOME
$ cd $HOME
# Just renaming the install for ease of use
$ mv apache-tomcat-6.0.29/ apache-tomcat6
# Make sure you have got sun-java6-jdk Installed on your 
# system mentioned in the steps above
$ vi $HOME/apache-tomcat6/conf/server.xml
# On Line 69 change the server port from 8080 to 9090 or 
# whatever you would like to have
# Now we would like to set the CATALINA OPTS for 
# Mondrian and apache to run properly in our system.
# Have given 1024 RAM to Tomcat to startwith since I 
# had enough of it. You can even start with 256 as minimum configuration.
$ vi ~/.bashrc
export CATALINA_OPTS="-Xms1024m -Xmx2048m -XX:MaxPermSize=2048m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000 -Djava.awt.headless=true"

# You can read about this more in Tomcat Configuration 
# documentation. But mostly these are the configuration 
# parameters that work really good with Mondrian. The last 
# parameter  -Djava.awt.headless=true is necessary to 
# avoid Tomcat to die when X Windows is not found. 
# If you wish to throw in more RAM for Mondrian then you can 
# modify the Xms and Xmx and MaxPermSize variables. 

export TOMCAT_HOME="$HOME/apache-tomcat6"

$ source ~/.bashrc

Step – 3 : Configure Tomcat with MySQL
Download MySql connector for Java
Follow the following steps after the download completes.

$ tar -xf mysql-connector-java-3.1.14.tar.gz -C $HOME
$ cd $HOME/mysql-connector-java-3.1.14/
$ cp mysql-connector-java-3.1.14-bin.jar $HOME/apache-tomcat6/lib/

Step – 4 : Testing Tomcat Installation

$ $TOMCAT_HOME/bin/startup.sh

# Browse to http://localhost:9090 and you would get main Tomcat Page.

$ $TOMCAT_HOME/bin/shutdown.sh

Step – 5 : Setting up Mondrian

Download Mondrian
Follow the following steps after the download

$ unzip mondrian-3.2.0.13661.zip -d $HOME
$ cp $HOME/mondrian-3.2.0.13661/lib/mondrian.war $HOME/apache-tomcat6/webapps/

# Test Mondrian Install
$ $TOMCAT_HOME/bin/startup.sh

# http://localhost:9090/mondrian and you would get the Mondrian page

$ $TOMCAT_HOME/bin/shutdown.sh

Step – 6: Configuring Mondrian

# Configuring datasources.xml
$ vi $TOMCAT_HOME/webapps/mondrian/WEB-INF/datasources.xml

Compare below with Original Config and check the differences where I have specified my custom datamart and my cube.

    
    
        
           MySalesDataMart

        
        My Sales Data Warehouse

        
        http://localhost:9090/mondrian/xmla

        
        Provider=mondrian;Jdbc=jdbc:mysql://localhost:3306/my_datamart_db?user=username&password=password;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/MySalesDatamart.xml

        
        Mondrian

        
        MDP

        
        Unauthenticated

        
        
            
            
                
                

                
                /WEB-INF/queries/MySalesDatamart.xml
            
        

    

Configuring mondrian.properties is the next step in configuring mondrian.
Following are the basic configurations that I had put in place. For more detailed configuration options you can refer to http://mondrian.pentaho.com/documentation/configuration.php

# Compare the following the original file and check the differences.
# The comments otherwise are self explanatory
$ vi $TOMCAT_HOME/webapps/mondrian/WEB-INF/mondrian.properties

# Allow the use of aggregates
mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true
mondrian.native.topcount.enable=true
mondrian.native.filter.enable=true

# Enables Mondrian Logging in human understandable formatted SQL format
mondrian.rolap.generate.formatted.sql=true

# Memory Configuration – Prevents Tomcat crashing with Memory Overrun errors
mondrian.util.memoryMonitor.enable=true

# mondrian.properties
mondrian.result.limit=50000

# For XML/A JSPs
mondrian.test.connectString=Provider=mondrian;Jdbc=jdbc:odbc:MondrianFoodMart;JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver;Catalog=/WEB-INF/queries/FoodMart.xml;

References:-
1. Mondrian Documentation
2. Installing Mondrian Guide