Cube Organized Materialized Views
Yesterday, Mark mentioned a new Oracle white paper on Cube Organized Materialized Views - now that is a bit of a mouthful and shouts out to be made COMV or something equally short and saving of typing. I say that because I have recently submitted the final version (in time so as to be included on the white paper CD) of my Collaborate 08 presentation on COMV, and I used Cube Organized Materialized Views in full so many times!
Well without revealing the whole paper just now; I am reserving that for the early morning (08:30) crowd (???) I get in Denver on April 17! I can say that Mark is right that I am enthusiastic about the technology.
Ignoring the fact that one of my first Oracle data warehouse systems used Oracle Sales Analyzer (and hence, Express) as a front-end, so I already knew quite a bit about Oracle OLAP; why should a person who has spent many years working in summary management and BI reporting be interested in OLAP cubes? The answer to this lies in query rewrite.
One of the big problems in traditional data warehouse + reporting software configurations is getting good query performance; traditionally, you build a load of summary tables in the batch (once) that answer many user queries and hence remove the need to repeatedly make the same expensive joins and aggregations at query time. Before query rewrite came on the scene each summary table needed to be mapped into the reporting tool , if you were lucky the query tool was aggregate aware and would choose the best summary table for the query, if not the users had to be instructed on the aggregate to use. Query rewrite changed that, we just needed to map in the base fact table and the denormalized dimensions and let the optimizer work out the best summary materialized view to use.
That still left the problem of being smart about the summaries to build - the wrong ones (or too few) and query performance is not as good as you could get, too many and it takes too long work out which summary to use! Step up COMV; build a simple OLAP cube, just the fact measures (no flashy OLAP calculations) and the dimensions, expose it as a query rewrite enabled materialized view and away you go, one summary to cover virtually all ad-hoc queries.
Next time I will write a bit about COMV performance.