Aggregate tables with Firebird

Autoren Thomas Steinmaurer
Titel Aggregate tables with Firebird
Typ Artikel
Journal Entwickler Magazin
Nummer 5,2008
Abteilung IDW
Monat May
Jahr 2009
Seiten 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.