Going for OLAP

To be honest, I have never implemented a data warehouse using Oracle’s OLAP option. True, I used to dabble with Oracle Sales Analyzer and Express but that was a couple of years ago and the technology has moved on. In those days we used the ROLAP model to map what seemed to be hundreds of physical summary tables into an enormous Express database with a full rebuild time (and for ROLAP that’s structure not content) of 14 hours. It caused our customer a lot of inconvenience in that we could only rebuild certain dimensions once a week.

Last week, at the UK OUG BIRT meeting Mark Rittman mentioned a conference paper that he has been asked to write on OLAP performance tuning and Oracle’s Kevin Lancaster put up two informative presentations on the OLAP option. Inspired by all of this I am planning to experiment with some of the newer (to me) OLAP features, such as partitioning, and investigate whether an OLAP cube gives me generically better performance than a set of Materialized Views with query rewrite – remember not all queries fit the MVs exactly and groupings and aggregations still happen, but with less cost than using the base tables. I intend to look at two aspects; time to refresh the cube compared to refreshing the MVs and time answer the queries using both technologies. Hopefully I can find a spare terabyte to make the tests slightly more meaningful.