Oracle 8 Tips   by Burleson Consulting

The Data Warehouse Development Life Cycle

Online Analytical Processing and Oracle

OLAP, ROLAP, And MOLAP

Speed Vs. Flexibility

To the end user, ROLAP and MOLAP are transparent. The front ends for these tools are similar, and the types of decision support activities are roughly the same. There are, however, significant differences between the operational details of ROLAP and MOLAP that are of primary concern to the data warehouse designer. The biggest difference between ROLAP and MOLAP involves the tradeoff between speed and flexibility (see Figure 5.16). MOLAP engines, by virtue of their pre-summarization and loading, have the data ready to display and give the end user incredibly fast response times. ROLAP engines, by virtue of their ad hoc data extraction and summarization, give end users incredible flexibility in their choices of queries. This is the very heart of the difference between ROLAP and MOLAP--speed versus flexibility.


Figure 5.16 MOLAP speed versus ROLAP flexibility.

There is also the issue of economics. For non-relational shops, such as IMS installations, MOLAP can be far less expensive than ROLAP solutions. Whereas a MOLAP database can be purchased and configured for as little as $200,000, ROLAP solutions have a much higher expense in terms of human resources for setup and configuration, as well as increased processing demands on the computer hardware.

On the other hand, shops that already have a relational database such as Oracle can quickly extract de-normalized data from their operational databases for downloading into MS-Excel pivot tables. This is by far the cheapest approach to OLAP because there is no investment in either hardware or software. Table 5.6 gives a listing of the most popular ROLAP/MOLAP products.

Vendor                                  Tool                            Description
Oracle                             Express                   Excel spreadsheet extension, true OO
Oracle                             Oracle 7.3               STAR query hints, parallel query, bitmap indexes
Microstrategy                   DSS Agent               MDDB queries against RDBMS
D&B                                Pilot Lightship           OLAP with custom and spreadsheet GUI
IBI                                  Focus/Fusion            MDDB engine
VMark                             uniVerse                   NT-based MDDB engine
Kenan                             Accumate ES            MDDB with PC-OLAP GUI
Arbor                              OLAP Builder             Extracts data from DW for Essbase
Arbor                              Essbase                   MDDB engine with Excel spreadsheet GUI
Think Systems                  FYI Planner               PC-GUI, with MDDB and OLAP server

Table 5.6 OLAP/MOLAP product information.

Over the past 10 years, there have been significant advances in ROLAP and MOLAP, but there remains a chasm between these technologies. Let's take a look at the most salient differences between the technologies.

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

 

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.