In the last post (OLAP Reporting on Open Source Software – I) we spoke about Mondrian, an open-source OLAP server.

In this post we would be setting up OLAP reporting for a hypothetical retailer called FoodMart that sells various grocery products in a chain of stores across US, Canada, and Mexico.

Assuming that you are running MS Windows on your machine, we would need:

    JDK 1.4.2 or above downloadable from Sun’s java download page (I used j2sdk1.4.2)
    Microsoft Access to act as the data store (drop a comment if you do not have MS-Access and I can provide detailed instructions on setting it up on another database such as MySQL)
    Tomcat 5 from apache.org. Go to the bottom and download Windows Binary Distribution (I am using jakarta-tomcat-5.0.28.exe).
    Mondrian along with FoodMart data from sourceforge.net

So now that you have downloaded all the software required, we can go about setting things up.

    Install JDK (say in c:\j2sdk1.4.2). This may require you to reboot your machine a couple of times. Do not install Tomcat yet.
    Right click on My Computer, select Properties -> Advanced -> Environment Variables. Create a new environment variable JAVA_HOME and point it to your JDK instalation (c:\j2sdk1.4.2)
    Now install Tomcat (say in C:\Program Files\Apache Software Foundation\Tomcat 5.0). It should get installed as a service.
    Go to Control Panel -> Administrative Tools -> Services to check if Tomcat5 service is running. If not, start the service.
    Unzip Mondrian2.0.1.zip somewhere (say C:\Mondrian). Copy mondrian.war file from C:\Mondrian\mondrian-2.0.1\lib folder and place it in the webapps folder of your tomcat installation (C:\Program Files\Apache Software Foundation\Tomcat 5.0\webapps).
    Within the unzipped Mondrian (in demo/access folder), you fill find a MS Access database file called MondrianFoodMart.mdb. This is where the data is going to get picked up from once we create a ODBC DSN.
    Go to Control Panel -> Administrative Tools -> Data Sources (ODBC) -> System DSN. Create a new ODBC DSN called ModrianFoodMart pointing to the MondrianFoodMart.mdb above. Make sure you get this step correct (drop a comment if you need help).

Okay, now time to go see the brand new reports by pointing your browser to http://localhost:8080/mondrian/. If everything has gone right, you should see a page with a couple of links there. Click the first link called “JPivot pivot tables” under the heading “Mondrian Examples”. You will see a nice little report showing Unit Sales, Store Cost, and Store Sales for our FoodMart.

JPivot Pivot Table

The user-interface is quite intuitive, so play around by clicking on various buttons. Do not forget to try out the top-left button (OLAP Navigator) that allows you to define your own reports by selecting measures, rows, columns and filters.

Have a go. And do let me know. :-)