My article on aggregate tables with Firebird has been published in the current edition of the German magazine Entwickler Magazin. The original title is:

Aggregatstabellen mit Firebird - Performante Abfragen in OLAP-Szenarien mit Aggregatstabellen

The index of contents of this magazine edition is available here.

UPDATE Dec. 2nd, 2008: The article is now available as download. Look here.

Regards,
Thomas

Mondrian is an Open Source OLAP (Online Analytical Processing) Server with quite an interesting feature set, like:

* High performance, interactive analysis of large or small volumes of information
* "Dimensional" exploration of data, for example analyzing sales by product line, by region, by time period
* Parsing of Multi-Dimensional eXpression (MDX) language into Structured Query Language (SQL) to retrieve answers to dimensional queries
* High-speed queries through the use of aggregate tables in the RDBMS
* Advanced calculations using the calculation expressions of the MDX language

Sounds cool, right? Mondrian doesn't have its own (multi-dimensional) storage engine, but it will operate on an existing relational database, thus following the relational OLAP (ROLAP) paradigm.

We are using Mondrian with Firebird in a data warehousing project and this combination works pretty fine so far. I'm not aware of any official/public document which describes how to setup Mondrian with Firebird, so hopefully this blog will get you going. Although I've used Windows XP Professional here, it's pretty much the same for Linux. I got Mondrian and Firebird running on OpenSuSE 10.3 without any problems.

1) Required Software

The following software (version) has been used:

* Java SDK (1.6.0_03)
* Firebird Classic Server (2.1)
* Jaybird (2.1.6)
* Mondrian (3.0.4.11371) (mondrian-3.0.4.11371.zip)
* Apache Tomcat (6.0.16)

I won't discuss installing the Java SDK, Firebird Server or Apache Tomcat. It's pretty much running an installer or unpacking a zip file. If you read my blog, you are probably used running Firebird anyway.

2) Installing Mondrian

* Unzip mondrian-3.0.4.11371.zip into a directory of your choice. I refer to the destination directory as %MONDRIAN%.

* Extract the following JAR files eigenbase-resgen.jar, eigenbase-xom.jar, log4j-1.2.8.jar and eigenbase-properties.jar from %MONDRIAN%\lib\mondrian.war into %MONDRIAN%\lib

* Extract jaybird-full-2.1.6.jar from the Jaybird distribution into %MONDRIAN%\lib

You should end up with a %MONDRIAN%\lib directory like:

3) Creating the Firebird Foodmart sample database

* Create a new Firebird user called foodmart




* Add a new alias called foodmart.fdb to your aliases.conf file of your Firebird installation. e.g.:

foodmart.fdb=C:\data\foodmart.fdb

* Create the new Firebird database foodmart.fdb




* Open a dos console, go to the %MONDRIAN% directory and run

java -cp "./lib/mondrian.jar;./lib/log4j-1.2.8.jar;./lib/eigenbase-xom.jar;./lib/eigenbase-resgen.jar;./lib/eigenbase-properties.jar;./lib/jaybird-full-2.1.6.jar" mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes -jdbcDrivers=org.firebirdsql.jdbc.FBDriver -inputFile=demo/FoodMartCreateData.sql -outputJdbcURL="jdbc:firebirdsql:localhost:foodmart.fdb" -outputJdbcUser=foodmart -outputJdbcPassword=foodmart

This will take a few minutes. After that, your Firebird foodmart sample database has been filled with data.

4) Deploying the OLAP web application

%TOMCAT% is the root directory of your Apache Tomcat installation.

* Extract jaybird-full-2.1.6.jar from the Jaybird distribution into %TOMCAT%\lib

* Create a new directory %TOMCAT%\webapps\mondrian_firebird

* Extract %MONDRIAN%\lib\mondrian.war into %TOMCAT%\webapps\mondrian_firebird

* Open %TOMCAT%\webapps\mondrian_firebird\WEB-INF\mondrian.properties and add the following line:

mondrian.jdbcDrivers=org.firebirdsql.jdbc.FBDriver

* Open %TOMCAT%\webapps\mondrian_firebird\WEB-INF\web.xml

Replace:

<context-param>
  <param-name>connectString</param-name>
  <param-value>@mondrian.webapp.connectString@</param-value>
</context-param>

With:

  <context-param>
    <param-name>connectString</param-name>
    <param-value>Provider=mondrian;Jdbc=jdbc:firebirdsql:localhost:foodmart.fdb;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=org.firebirdsql.jdbc.FBDriver;JdbcUser=foodmart;JdbcPassword=foodmart</param-value>
  </context-param>

And:

  <servlet>
    <servlet-name>MDXQueryServlet</servlet-name>
    <servlet-class>mondrian.web.servlet.MDXQueryServlet</servlet-class>
    <init-param>
      <param-name>connectString</param-name>
      <param-value>@mondrian.webapp.connectString@</param-value>
    </init-param>
  </servlet>

With:

  <servlet>
    <servlet-name>MDXQueryServlet</servlet-name>
    <servlet-class>mondrian.web.servlet.MDXQueryServlet</servlet-class>
    <init-param>
      <param-name>connectString</param-name>
      <param-value>Provider=mondrian;Jdbc=jdbc:firebirdsql:localhost:foodmart.fdb;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=org.firebirdsql.jdbc.FBDriver;JdbcUser=foodmart;JdbcPassword=foodmart</param-value>
    </init-param>
  </servlet>

* Open arrows.jsp, colors.jsp, fourhier.jsp and mondrian.jsp in %TOMCAT%\webapps\mondrian_firebird\WEB-INF\queries

In each file, replace the line starting with:

<jp:mondrianQuery id="query01" ...

With:

<jp:mondrianQuery id="query01" jdbcDriver="org.firebirdsql.jdbc.FBDriver" jdbcUrl="jdbc:firebirdsql:localhost:foodmart.fdb" jdbcUser="foodmart" jdbcPassword="foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml">

5) Starting the OLAP web application

* Start Apache Tomcat with %TOMCAT%\bin\startup.bat

* Open a web browser and type the following URL

http://localhost:8080/mondrian_firebird

* Click, e.g. the first link 'JPivot pivot table'. You should end up with the following screen:



Welcome to the world of OLAP! Now, lets "Drill-Down" along the Product dimension, by expanding the "All Products" entry:



With a click on the chart button, you get a graphical representation of the analyzed data:



We finally got Mondrian up and running with Firebird. More advanced topics on Mondrian/Firebird would be aggregate tables, Mondrian as XMLA capable server and more. Possibly I will blog on that later.

Feel free to give me feedback on this article. Either via the 'add comment' link or by sending an email.

My article on Aggregate Tables with Firebird - a very effective way to enhance query performance in OLAP scenarios - has been published in the current edition (5.08) of the German magazine Entwickler Magazin.

Regards,
Thomas

I'm a regular author in the German developer magazine Entwickler Magazin of mainly Firebird related articles. With their permission, I'm allowed to make my articles available as download. The articles are in German.

* Edition 03/2004: Serverseitiges Auditing für InterBase
* Edition 05/2004: Roll-Forward-Log für InterBase
* Edition 02/2005: Firebird 1.5
* Edition 05/2005: Eigentümermigration in InterBase und Firebird
* Edition 01/2006: Firebird 2.0 - Teil 1
* Edition 03/2006: Firebird Embedded Server
* Edition 02/2007: Fyracle
* Edition 03/2007: Firebird 2.0 - Teil 2
* Edition 05/2007: Firebird 2.1
* Edition 05/2008: Aggregatstabellen mit Firebird

The timetable for the Firebird Conference 2008, Bergamo, Italy 25th-27th Sept. is now available.

There will be interesting stuff to listen. A pitty, but I won't make it this year. Hopefully I can join the party next year again. If you are there, enjoy yourself. It's a great opportunity to get in touch with people behind the project, speakers, users and the bar keeper and the bar propper. And don't miss the mad auction!

Thomas

I've continued the work on the Firebird Transaction Statistic Logger (FTSL) and could not resist adding visualization capabilities to this utilitity. It now will be able to show a chart for the OIT/OAT/OST/NT numbers and also for the calculated gap values.

The following chart shows the absolut transaction ID for the transaction counters (click to enlarge):




If you are more interested in getting a chart with the gaps, this one will be useful (gap between OAT and NT):



FTSL now also can be installed as a Windows service with multi-instance capabilities, which allows to install one monitoring service per monitored database. You won't have the GUI gimmicks then of course.

Regards,
Thomas