Supporting technology for Data Marts, Data Mining, Business Intelligence tools and Decision Support Systems:

OLAP and MDDBMS

Not being a recent technological development, multidimensional databases and database management systems have been in use since the 1970's. Their theoretical underpinnings have been available since the 1960's. What is new is the identification of hybrid database systems that optimize retrieval and processing speed for on-line analytical processing applicatons.

Pilot Software, SAS and Cognos are examples of companies whose core competencies are in the area of OLAP applications front ending data marts and data warehouses. This same technology is used for many companies' ERP systems.

Pilot has published an extensive white paper on OLAP that can be found at http://www.pilotsw.com/olap/olap.htm.

OLAP is contrasted with OLTP in that OLAP "describes a class of technologies that are designed for live ad hoc data access and analysis….transaction processing generally relies solely on relational databases and OLAP has become synonymous with multidimensional databases or multidimensional views of business data.

Multidimensional databases allow for the clustering of data along two or more axes. Imagine a 2-dimensional matrix with x-axis labeled region (east, west and central) and the y-axis labeled product. The matrix is populated with sales data. What happens when a user wants to query the table for trends or aggregates of sales by region or product? In traditional RDBMS, each record would have to be read and calculations made, then the analysis completed. Given a database of sufficient size, and due to normalization, this information would more than likely be broken up into many tables that would require hours to respond to a query that involved retrieving multiple numbers and aggregate them. The multidimensional database would be able to complete the same query in a manner of seconds. It is capable of adding up numbers in rows and columns at a rate of 10,000 per second. The key to this consistently fast response is consolidation.

Other sources of information on MDDBMS are located at www.dbpd.com/vault/9803feat.htm, www.planetit.com/prodreviews/internetwk/intwk2, www.sas.com/rnd/web/mddbapp/index.html.

Mike Williams

March 29, 1999