Mondrian OLAP on MySQL EC2 Part 1

If you are wondering why there has been no recent postings, it is due to mainly to struggling to get the Mondrian installed. It had nothing to do with the environment and mostly due to documentation missing vital examples or explanation.

It is with relief I can say I have passed the test and can pass over to the fair lands of using Mondrian OLAP and running the demo.

You can download Mondrian from the Pentaho website or directly from sourceforge.
Use this installation document as a guide however it glosses over some of the nice little details which will make the demo work or not.

Comments:

1. Installing the demo was way too hard, just bundle tomcat or build a include everything binary for MySQL, PostgreSQL, Oracle or whatever. You want people to try the program, not many people will persist like I did to get it to work.
   
2. Having to hand edit every file which connects to the database or is used as a tomcat configuration file sucks, ever heard of storing that stuff in a single file or in the database.
3. Provide a simple SQL script to create schema objects and load data. Running the Foodmart loader did not prove you could run the demo! use this SQL script otherwise from Gizzar
   
4. Some kind of example of either a tomcat or other verification tool would be good, rather than having to lather, rinse and repeat over java, JDBC, Tomcat and red herring errors.

Many thanks to some other blogs and sites which helped solved various issues which cropped up
Gizzar article on Mondrian Open Source OLAP with MySQL
University of Vienna (Wien) for Tomcat FAQ solving tomcat shutdown on missing X
Mondrian Forum

Have Fun

Paul

Install:
Note: This really requires a HOWTO document. I will work on that based on this. Hopefully you can use this in conjunction with installation guide and the various other blogs who had fun with this.

1. Install some linux packages: yum install gcc autoconf
   
2. Download the last release of Mondrian non-embedded files from SourceForge.
3. Download and install Java 1.5 or better from Sun Java Downloads.
   
4. Download and install MySQL 5.0 from MySQL Downloads.
5. Download and unzip the MySQL JDBC driver
   
6. Download and install Apache Tomcat 5.0.28.
7. Verify that Java, MySQL and Tomcat are working by doing the following:
   
8. java -version
9. mysql -V
10. /usr/local/tomcat/bin/startup.sh
11. Point your browser at http://yourhostname:8080, if it works, tomcat is working.
    
12. unzip Mondrian.zip -b /usr/local/mondrian
13. Create the MySQL database: mysqladmin create foodmart -u root -p
14. Create the Foodmart User: create user 'foodmart'@'yourhostname' identified by 'foodmart';
15. Grant permissions: grant all privileges on *.* to 'foodmart'@'yourhostname' identified by 'foodmart';
16. Explode the mondrian.war into /usr/local/tomcat/webapps/mondrian: jar -xvf mondrian.war
17. Locate the 4 jar files eigenbase-properties.jar,eigenbase-resgen.jar,eigenbase-xom.jar and log4j-1.2.9.jar
    
18. Run Mondrian.FoodmartLoader to create tables and load data, pass in the full path to all the required JAR files, otherwise it will fail with a Class notFound error. This is an example:
    



java -cp "lib/mondrian.jar: /usr/local/mysql-5.0.45-linux-i686/mysql-connector-java-5.0.7/src/lib/log4j-1.2.9.jar: 
lib/eigenbase-xom.jar:lib/eigenbase-resgen.jar:lib/eigenbase-properties.jar:
/usr/local/mysql/mysql-connector-java-5.0.7/mysql-connector-java-5.0.7-bin.jar" \
mondrian.test.loader.MondrianFoodMartLoader \
-verbose -tables -data -indexes \
-jdbcDrivers=com.mysql.jdbc.Driver \
-inputFile=demo/FoodMartCreateData.sql \
-outputJdbcURL="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart"



19. cp /usr/local/jakarta-tomcat-5.0.28/webapps/mondrian/WEB-INF/lib/xalan.jar /usr/local/jakarta-tomcat-5.0.28/common/endorsed/
20. Modify the query files, web.xml, datasource.xml and mondrian.properties file to replace localhost with your hostname



sed -i`date +%y%m%d` -e "s/localhost/yourhostname/" fourhier.jsp
sed -i`date +%y%m%d` -e "s/localhost/yourhostname/" mondrian.jsp
sed -i`date +%y%m%d` -e "s/localhost/yourhostname/" colors.jsp
sed -i`date +%y%m%d` -e "s/localhost/yourhostname/" arrows.jsp
sed -i`date +%y%m%d` -e "s/localhost/yourhostname/" web.xml
sed -i`date +%y%m%d` -e "s/localhost/yourhostname/" datasource.xml
sed -i`date +%y%m%d` -e "s/localhost/yourhostname/" mondrian.properties



21. Modify the connection string in each file as per install guide, including the #38 stuff it is not a browser character issue.
22. Test connectivity for mysql user foodmart@yourhostname. Connection errors can cause this error



Mondrian Error:Internal error: Error while creating SQL dialect



23. export CATALINA_OPTS='-Djava.awt.headless=true' or add to your shell profile, this stops tomcat dying when X-windows is not found!! Thanks to this link for solving that.