Wednesday, February 9, 2011

Install Mondrian OLAP 3.2 on CentOS with Tomcat and MySQL

This post will cover installing Mondrian OLAP Engine on CentOS with Tomcat and MySQL.

Mondrian 3.2.0.13661
Tomcat 6.0.30
MySQL 5.0.77


This post assumes you have a working Tomcat installation. If you do not, please follow our step-by-step tutorial to installing Tomcat 6 or Tomcat 7.

You will need to download the following:

Mondrian: mondrian-3.2.0.13661.zip
http://sourceforge.net/projects/mondrian/files/mondrian/mondrian-3.2.0.13661-GA/

Apache Axis 1.4: axis-bin-1_4.tar.gz
http://www.apache.org/dyn/closer.cgi/ws/axis/1_4

MySQL Connector J (if not already installed): mysql-connector-java-5.1.14.tar.gz
http://www.mysql.com/downloads/connector/j/


1. Download the Above Files to the /opt Directory.

Unzip mondrian-3.2.0.13661.zip
[root@srv6 opt]# unzip -q mondrian-3.2.0.13661.zip
Unpack Axis 1.4
[root@srv6 opt]# tar xzf axis-bin-1_4.tar.gz

Unpack mysql-connector-java-5.1.14.tar.gz
[root@srv6 opt]# tar xzf mysql-connector-java-5.1.14.tar.gz
In your /opt directory you should now have:
[root@srv6 opt]# ls
axis-1_4                mondrian-3.2.0.13661
mysql-connector-java-5.1.14


2. Deploy Mondrian to the Tomcat_Home/webapps folder.

Naviagte to /opt/mondrian-3.2.0.13661/lib

You can use one of two methods to deploy the mondrian.war to Tomcat_Home/webapps:

Method 1. Copy or mv the Modrian.WAR file from /opt/mondrian-3.2.0.13661/lib to your Tomcat/webapps directory and restart Tomcat to deploy the WAR.
[root@srv6 opt]# cp /opt/mondrian-3.2.0.13661/lib/mondrian.war /usr/share/apache-tomcat-6.0.30/webapps/mondrian.war
[root@srv6 opt]# service tomcat start (or restart)

Method 2. Create the mondrian directory under Tomcat_Home/webapps and manually and explode the WAR via the CLI
[root@srv6 opt]# mkdir /usr/share/apache-tomcat-6.0.30/webapps/mondrian
[root@srv6 opt]# cp /opt/mondrian-3.2.0.13661/lib/mondrian.war /usr/share/apache-tomcat-6.0.30/webapps/mondrian/mondrian.war
[root@srv6 opt]# cd /usr/share/apache-tomcat-6.0.30/webapps/mondrian
[root@srv6 mondrian]# jar -xvf mondrian.war

3. Copy the Required Jars from Axis and MySQL to the Mondrain Directory Created Above

There are four JAR files we need from /opt/axis-1_4/lib:

axis.jar
commons-discovery-0.2.jar
wsdl4j-1.5.1.jar
jaxrpc.jar

Copy or move these JAR file to /mondrian/WEB-INF/lib:


[root@srv6 mondrian]# cd /opt/axis-1_4/lib
[root@srv6 lib]# cp axis.jar /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/axis.jar
[root@srv6 lib]# cp commons-discovery-0.2.jar /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/commons-discovery-0.2.jar
[root@srv6 lib]# cp wsdl4j-1.5.1.jar /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/wsdl4j-1.5.1.jar
[root@srv6 lib]# cp jaxrpc.jar /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/jaxrpc.jar

Copy or move the MySQL Connector to /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib

[root@srv6 lib]# cp /opt/mysql-connector-java-5.1.14/mysql-connector-java-5.1.14-bin.jar /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/mysql-connector-java-5.1.14-bin.jar



4. Restart Tomcat

[root@srv6 ~]# service tomcat restart
Using CATALINA_BASE:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_HOME:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_TMPDIR: /usr/share/apache-tomcat-6.0.30/temp
Using JRE_HOME:        /usr/java/jdk1.6.0_23
Using CLASSPATH:       /usr/share/apache-tomcat-6.0.30/bin/bootstrap.jar
Using CATALINA_BASE:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_HOME:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_TMPDIR: /usr/share/apache-tomcat-6.0.30/temp
Using JRE_HOME:        /usr/java/jdk1.6.0_23
Using CLASSPATH:       /usr/share/apache-tomcat-6.0.30/bin/bootstrap.jar

5. Create the Foodmart Database and Database User:

[root@srv6 lib]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database foodmart
    -> ;
Query OK, 1 row affected (0.02 sec)

mysql> grant all privileges on *.* to 'foodmart'@'localhost' identified by 'foodmart';
Query OK, 0 rows affected (0.02 sec)

mysql>


6. Load the Foodmart Sample Data.

java -cp "/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/mondrian.jar:/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/log4j-1.2.8.jar:/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/commons-logging-1.0.4.jar:/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/commons-collections-3.1.jar:/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/eigenbase-xom.jar:/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/eigenbase-resgen.jar:/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/eigenbase-properties.jar:/usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/lib/mysql-connector-java-5.1.14-bin.jar" mondrian.test.loader.MondrianFoodMartLoader 
-verbose -tables -data -indexes -jdbcDrivers=com.mysql.jdbc.Driver 
-inputFile=/opt/mondrian-3.2.0.13661/demo/FoodMartCreateData.sql
-outputJdbcURL="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart"

  • In the above, I have used the full directory paths for everything. So you can run the above from any directory. It also allows you to see exactly where everything is coming from.

  • My Tomcat installation is located at /usr/share/apache-tomcat-6.0.30, so if yours is elsewhere, you'll need to update this above.

  • Similarly, I have placed mysql-connector-java-5.1.14-bin.jar in mondrain/WEB-INF/lib. If you already have this elsewhere or are using a different version of connector j, update accordingly.

  • The Pentaho/Mondrian docs do not mention using commons-collections-3.1.jar, but I needed to use this to get the data to load.

Now that we have loaded all of the require jars and loaded the sample data, we need to create a data source, and edit some files.


7. Create a Data Source:


Create a Data Source, we'll call it DsMySQL.

Open /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/datasurce.xml and edit to look like below.


 
   Provider=Mondrian;DataSource=DsMySQL;
   FoodMart Data Warehouse
   http://localhost:8080/mondrian/xmla
   

Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;
  Mondrian
  MDP
  Unauthenticated
   
    
     /WEB-INF/queries/FoodMart.xml
    
   





8. Edit Mondrian web.xml.

Replace the two instances of @mondrian.webapp.connectString@ with the following:

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


Original web.xml entries:


    connectString@mondrian.webapp.connectString@

  
    MDXQueryServlet
    mondrian.web.servlet.MdxQueryServlet
    
      connectString@mondrian.webapp.connectString@
  


Web.xml after editing:


    connectStringProvider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver;
  
    MDXQueryServlet
    mondrian.web.servlet.MdxQueryServlet
    
      connectStringProvider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver;
  




9. Edit Queries

Under /usr/share/apache-tomcat-6.0.30/webapps/mondrian/WEB-INF/queries, edit the following:

fourhier.jsp
mondrian.jsp
colors.jsp
arrows.jsp

In each case, replace this:



with this...





For testrole.jsp, replace this:



with this...




For xmla.jsp, replace this:




with this...(using the Data Source, DsMySQL, we created in step 7 above):




10. Edit Catalina.sh to force use of Axis for SOAP:

/usr/share/apache-tomcat-6.0.30/bin/Catalina.sh

Add the following:
JAVA_OPTS="-Djavax.xml.soap.MessageFactory=org.apache.axis.soap.MessageFactoryImpl -
Djavax.xml.soap.SOAPConnectionFactory=org.apache.axis.soap.SOAPConnectionFactoryImpl -Djavax.xml.soap.SOAPFactory=org.apache.axis.soap.SOAPFactoryImpl"


11. Restart Tomcat to Reload Edited Files.

[root@srv6 ~]# service tomcat restart
Using CATALINA_BASE:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_HOME:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_TMPDIR: /usr/share/apache-tomcat-6.0.30/temp
Using JRE_HOME:        /usr/java/jdk1.6.0_23
Using CLASSPATH:       /usr/share/apache-tomcat-6.0.30/bin/bootstrap.jar
Using CATALINA_BASE:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_HOME:   /usr/share/apache-tomcat-6.0.30
Using CATALINA_TMPDIR: /usr/share/apache-tomcat-6.0.30/temp
Using JRE_HOME:        /usr/java/jdk1.6.0_23
Using CLASSPATH:       /usr/share/apache-tomcat-6.0.30/bin/bootstrap.jar


That should do it :)

Navigate to http://YourIP:8080/modrian or http://YourDomain.com:8080/mondrian and verify that all samples are working correctly.



Notes:

Mondrian Installation Docs:
http://mondrian.pentaho.com/documentation/installation.php

Forcing Mondrian to use axis (SOAP) and Xalan libraries for JPivot by Sherman Wood;
http://lists.pentaho.org/pipermail/mondrian/2009-January/001550.html
http://lists.pentaho.org/pipermail/mondrian/2009-January/001553.html

I did not use Xalan in the above post.


A great step-by-step guide to trouble-shooting each sample app from Feris Thia:
http://pentaho-en.phi-integration.com/mondrian/configuring-mondrian-sample

2 comments:

Gaurav said...

Hi David,

Thanx for nice blog.

I need some info. We are deploying JPivot+Mondrain on Jboss5.1 . we are trying to use data source in our tag.




we configure data source in ds.xml say "mysql-ds.xml".

But its giving problem.

Could please suggest!

KR,
Gaurav

David Ghedini said...

Hi Gaurav -
Can you more specific as to the problem(s)?