Aggregate tables with Firebird

Authors Thomas Steinmaurer
Title Aggregate tables with Firebird
Type article
Journal Entwickler Magazin
Number 5,2008
Department IDW
Month May
Year 2009
Pages 88-94
SCCH ID# 931

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.