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 (picture).

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

Create a database and execute the script. Below is 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)

C:\mysql\bin\mysql.exe -Uroot -e "create database Foodmart"

C:\mysql\bin\mysql.exe -Uroot Foodmart < C:\foodmart_mysql.sql

C:\mysql\bin\mysql.exe -Uroot Foodmart -e "show tables"

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

+-------------------------------+
| Tables_in_foodmart_mondrian   |
+-------------------------------+
| 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 editor, and change following line :

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

into your MySQL database configuration. For example ...

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

Browse Your Multi Dimensional Data

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

This will show a view of multidimensional query but It will take some time as Mondrian will need to populate its initial cache.

After that, you will see a table with multidimensional which you can drill in hierarchical way and a heading toolbar which comprises of configuration icons.

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