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
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.
Excel spreadsheet extension, true OO
STAR query hints, parallel query, bitmap indexes
MDDB queries against RDBMS
OLAP with custom and spreadsheet GUI
NT-based MDDB engine
MDDB with PC-OLAP GUI
Extracts data from DW for Essbase
MDDB engine with Excel spreadsheet GUI
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.