Oracle Data Warehouse OLAP,
ROLAP, and MOLAP

Oracle Data Warehouse Tips by Burleson Consulting

OLAP, ROLAP, And MOLAP

Now that we understand that OLAP is a tool
that displays summarized data, plotting one dimension against
another, let's look at the vendor implementations of this
technology. As you saw in the previous section, pivot tables are an
excellent way to display multidimensional data, but OLAP involves
more than just the multidimensional display of information. OLAP
tools also must be able to extract and summarize requested data
according to the needs of an end user, and there are two approaches
for this data extraction that need to be discussed.

When multidimensional OLAP was first
introduced, data was extracted from the relational engine and loaded
into a proprietary architecture called a multidimensional database.
The data was displayed quickly by accessing the pre-summarized data.
This type of OLAP utilizes a multidimensional database, which has
become known as MOLAP, or multidimensional OLAP. The other approach
to data extraction uses a mapping facility and extracts the raw data
from an operational relational database at runtime, summarizing and
displaying the data. Because this approach does not require a
multidimensional database, it has become know as ROLAP, or
relational OLAP.

There are many different types of OLAP and
MDDB products on the market today. As shown in Table 5.5, OLAP and
MDDB have their own relative advantages and disadvantages, and they
are both fighting to achieve recognition for their strengths.

Trait ROLAP MDDB

Speed Slow Fast

Queries Flexible Fixed

Disk cost Low
High

Table 5.5 OLAP versus MDDB.

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[TZ98], 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.

Multidimensional OLAP (MOLAP)

Multidimensional OLAP is generally thought
of as the traditional multidimensional database (MDDB), and many of
the early offerings advertised themselves as ?pure? multidimensional
databases. As we have discussed, a multidimensional database is a
database structure optimized for storing facts categorized along
many dimensions. The MDDBs are far more effective for storing OLAP
data than relational databases because they were designed
exclusively with this purpose in mind. The other major consideration
with multidimensional OLAP is the fact that all of the data is
loaded, summarized, and stored in the MDDB prior to making the
database available to end users. Because all the calculations have
already been performed, multidimensional OLAP offers astounding
response times. For these reasons, multidimensional OLAP is the best
choice for applications with the following characteristics:

* Impatient end users--MOLAP engines
offer end users fast and predictable response times for their
queries. In some cases, end users need to be able to quickly create
new queries based on the responses from previous queries without
losing their train-of-thought. This speed differential is getting
smaller as the speed of relational databases improves, but there
remains a dramatic difference between the retrieval of
pre-summarized data from an MDDB and the runtime extraction and
summarization from a relational back end. It is not uncommon for an
end user to report a system outage when the ROLAP tool takes several
hours to roll-up summaries from a relational database extract.

* Sophisticated data analysis--MOLAP
engines provide a more robust analysis environment than ROLAP tools.
MOLAP engines support budgeting and forecasting functions and tend
to have a much more advanced statistical toolkit than their ROLAP
cousins. ROLAP, on the other hand, has the ability to provide ad hoc
groupings while MOLAP cannot aggregate on the fly.

* Ease of use--MOLAP engines are very
easy for end users to configure and use to set up scenarios for
decision support systems. Because the data is pre-summarized and
stored in the multidimensional database, all an end user needs to do
is specify the dimensions and groupings within dimensions. ROLAP, on
the other hand, requires an end user with knowledge of the mapping
of the operational databases, and it is much more difficult to
configure.

Relational OLAP (ROLAP)

The advent of the multidimensional database
led to an effort by tools vendors to create a method where data
could be extracted from a relational database and presented to end
users as if it were from a multidimensional database (see Figure
5.17). There are several methods for accessing a relational database
and presenting aggregated data as if it were from a multidimensional
database. These alternatives include ROLAP middleware tools and
downloading pre-aggregated data to local pivot tables. Another
common approach is to insert a metadata server between the OLTP
relational database and the query tool.

Figure 5.17 Overview of a ROLAP system.

In order to be considered a ROLAP product, a
tool must extract runtime data from a relational database, present
summarized data in cross-tabular format, and possess a mechanism for
translating the relational design into a multidimensional format.
Examples of popular ROLAP products include:

* DSS Agent by Microstrategies

* Metacube by Stanford Technology Group

* Holos by Holistic Systems

* AXSYS Suite by Information Advantage

* Red Brick Warehouse by Red Brick
Systems

* Prodea Beacon by Platinum Technology

ROLAP systems provide extremely flexible
query engines by making any number of operational data stores
available to end users. These back-end databases are usually
relational databases, but ROLAP tools can also extract from a
variety of different relational database.

ROLAP tools require the definition of the
mapping between the OLAP model and the relational database, and
generate SQL to extract the required data from the operational
databases in a very similar fashion to the SQL generators described
in Chapter 4.

Because the entire enterprise can be made
available to a ROLAP tool, it is not surprising to acknowledge that
ROLAP is far more flexible than its MOLAP cousin. Any data, on any
platform or database, can be mapped into a multidimensional format;
the ROLAP engine will obediently extract and summarize the data
according to the extraction specifications. Because ROLAP is far
more robust in this sense, it is the OLAP tool of choice for data
warehouses that support the following features:

* Data changes frequently--In a data
warehouse where data is very dynamic and end users require
up-to-the-minute summarizations, ROLAP is the only choice. MOLAP
tools must extract and summarize data offline for loading into their
multidimensional databases. To make matters worse, most
multidimensional databases require recalculation of the entire
database when a new dimension is added, an aggregation scheme
changes, or data is added. These overhead factors make MOLAP
inappropriate for decision support systems with highly volatile data
sources. Examples of these types of applications include stock
market DSS and weather forecasting tools.

* Large data volumes--For very large
database warehouses in the terabyte range, the cost of supporting
MOLAP tools can be exorbitant. The pre-summarization of data can
require hundreds of gigabytes of disk storage, and many companies
cannot afford the millions of extra dollars required to provide
sub-second response times for OLAP queries. ROLAP tools allow
companies to leverage their existing investment in OLTP databases
without having to buy a multidimensional engine.

* Unpredictable types of
queries--Because ROLAP engines can allow virtually any operational
data source to be queried and summarized, ROLAP has a clear
advantage for the decision support application that cannot predefine
its query requirements. Of course, this flexibility comes at the
cost of ease-of-use, because the IS department must often get
involved to assist end users in creating the mappings to the
operational databases.

Today, many developers are using relational
databases to build their data warehouses and simulate multiple
dimensions, and specific design techniques are being used for this.
The push toward STAR schema design has been somewhat successful,
especially because designers do not have to buy multidimensional
databases or invest in expensive front end tools.

Several methods can be used to aggregate
data within OLAP servers. As you can see in Figure 5.18, this method
extracts data from the relational engine and summarizes the data for
display. Another popular method pre-aggregates the data and keeps
the summarized data ready for retrieval.

You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail: