David Ghedini

Linux, Java, Oracle, and PostgreSQL


David Ghedini

Tuesday Mar 01, 2011

Install Mondrian 3 on CentOS

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

Bookmark and Share




Main Menu

Built With

Pages

LinkedIn

Tag Cloud

Hosted by:

Navigation

Visitors

Sponsors

petiole Cloud

Feeds