Aggregate tables with Firebird

T. Steinmaurer. Aggregate tables with Firebird. Entwickler Magazin, number 5,2008, pages 88-94, 5, 2009.

  • Thomas Steinmaurer
JournalEntwickler Magazin
Abstract How many units of a particular product have been sold in Europe in Q1/2008? What was the sales volume of my subsidiary on the Island of Fiji in 2007? These are typical business critical questions, which management may demand from the back office. To answer these questions, the relevant data volume can be > 100 million records, especially for a big, world-wide operating company. Processing must be – of course – fast. For an IT department head or for a database administrator, this is quite a common requirement. You want to spend >20K Euro on licenses for “well-known” commercial database management systems (DBMS) to handle this requirement? No? This article describes how to speed-up analytical queries typical for OLAP scenarios by pre-aggregating data in aggregate tables with a trigger-based mechanism for the open source DBMS Firebird and the open source OLAP server Mondrian, which is able to re-use data in aggregate tables due to its query rewrite capabilities.