Mondrian + MySQL Setup

Introduction

Mondrian is a very popular open source OLAP Server and is also one of the products that are very oftenly asked in several discussion forums. One of those including installation and initial configuration issues.

In this article I will try to guide step by step on how to get Mondrian installed and configured using MySQL community database server and Windows XP as its operating system platform.

Prerequisites

These are the applications that are needed before you can using Mondrian, follow on the link for more information on how to setup each application :

• Java Development Kit 5 or later
• Apache Tomcat
• MySQL 5
• MySQL 5 JDBC driver
• Windows XP Pro SP2 Edition

MySQL Foodmart Database Sample

Mondrian provided Foodmart database sample in MS Access format. But I have provided a full generated MySQL script populate Foodmart so you use it in MySQL Community database server. You can download the tar.gz compressed file from my wiki site (pictured).

Extract the script file using your favorite compression utility - 7zip is the one I would highly recommend.

Create a database and execute the script. Here I show you a sample commands to create a Foodmart database - assuming that your mysql is running and your mysql.exe client is located in C:\mysql\bin with your previously extracted script file in C:\foodmart_mysql.sql.

Open command line prompt (Start | Run | type cmd | click OK)

cd C:\mysql\bin
mysql.exe -uroot -e "create database Foodmart"
mysql.exe -uroot Foodmart < C:\foodmart_mysql.sql
mysql.exe -uroot Foodmart -e "show tables"

You should see result like below if all running well...

+-------------------------------+
| Tables_in_foodmart            |
+-------------------------------+
| account                       |
| agg_c_10_sales_fact_1997      |
| agg_c_14_sales_fact_1997      |
| agg_c_special_sales_fact_1997 |
| agg_g_ms_pcat_sales_fact_1997 |
| agg_l_03_sales_fact_1997      |
| agg_l_04_sales_fact_1997      |
| agg_l_05_sales_fact_1997      |
| agg_lc_06_sales_fact_1997     |
| agg_lc_100_sales_fact_1997    |
| agg_ll_01_sales_fact_1997     |
| agg_pl_01_sales_fact_1997     |
| category                      |
| currency                      |
| customer                      |
| days                          |
| department                    |
| employee                      |
| employee_closure              |
| expense_fact                  |
| inventory_fact_1997           |
| inventory_fact_1998           |
| position                      |
| product                       |
| product_class                 |
| promotion                     |
| region                        |
| reserve_employee              |
| salary                        |
| sales_fact_1997               |
| sales_fact_1998               |
| sales_fact_dec_1998           |
| store                         |
| store_ragged                  |
| time_by_day                   |
| warehouse                     |
| warehouse_class               |
+-------------------------------+

Configuring and Running Tomcat

You will need to copy mysql driver, i.e. mysql-connector-java-5.1.6-bin.jar file to your Tomcat's library folder.

• Extract your MySQL zipped driver file you have downloaded before to get the jar file
  
• Copy the jar driver to C:\apache-tomcat-5.5.17\common\lib folder - which is the location for any jar drivers used in our web application. For version 6 of tomcat you just put it in APACHE_TOMCAT/lib folder
  

Run your server by typing "catalina run" in Tomcat's bin folder.

cd c:\apache-tomcat-5.5.17\bin
catalina run

Check your running server by browsing into http://localhost:8080. If everything works fine, our next step is to deploy Mondrian web application.

Deploying Mondrian

Extract mondrian distribution package and goto lib directory. You will find mondrian.war file there.

Copy the war file into your Tomcat's webapps directory while Tomcat is still running. The war file will be extracted and deployed into Tomcat automatically.

Browse into your http://localhost:8080/mondrian and you shall get a Mondrian welcome page like below.

Configure JSP File

Browse into Mondrian's application queries folder where you can find several sample jsp files. Here I open my C:\apache-tomcat-5.5.17\webapps\mondrian\WEB-INF\queries folder and find the following jsp files :

• fourheir.jsp
• mondrian.jsp
• colors.jsp
• arrows.jsp

Edit those files using your favorite text editor, and change following line :

<jp:mondrianQuery id="query01"
jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUrl="jdbc:odbc:MondrianFoodMart"
catalogUri="/WEB-INF/queries/FoodMart.xml">

..... this is your existing mdx query ....

</jp:mondrianquery>

into your MySQL database configuration. For example ...

<jp:mondrianQuery id="query01"
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/Foodmart?user=root&password="
catalogUri="/WEB-INF/queries/FoodMart.xml">
 
..... this is your existing mdx query ....
 
</jp:mondrianQuery>

Browse Your Multi Dimensional Data

Now you are ready to to play with JPivot. Browse into your local mondrian host address : http://localhost:8080/mondrian, choose the first mondrian examples "JPivot pivot table".

It will take some time as Mondrian will need to populate its initial cache before the view shows up.

You will then see a table which you can drill through in a hierarchical way and a heading toolbar which comprises of configuration icons.

Congratulations, your Mondrian with MySQL database has been successfully setup !

Updated : More on Mondrian Sample's Configuration

To find more about configuring each sample to work well with above installation please refer to our wiki page at http://pentaho-en.phi-integration.com/mondrian/configuring-mondrian-sample.