Online Analytical Processing And
Dr. E. F. (Ted) Codd coined the phrase
online analytical processing (OLAP) in a 1993 white paper called
Providing OLAP (Online Analytical Processing) to User-Analysts: An
IT Mandate. Soon after the publication of this paper, OLAP became
the latest buzzword in the database arena and every IS professional
struggled to understand OLAP and how it fit into the paradigm of
decision support systems (DSS) applications. In addition to defining
OLAP, Dr. Codd also went on to create 12 rules for OLAP which are
similar in form to Codd?s 12 rules for relational databases. Given
the recent popularity of OLAP, it is very easy to view OLAP as a
nascent technology. However, OLAP, as defined by Codd, is not a new
technology, and some products, such as the IRI Express OLAP engine,
have been available for more than 20 years.
As more companies embrace the concept of
creating a historical data repository for OLAP and DSS applications,
new client/server issues are emerging as developers struggle to
create Oracle-based client/server applications. This chapter reviews
OLAP with a focus on the various techniques used to interface OLAP
applications with Oracle databases.
A great deal of interest has surfaced in the
application of data warehousing and multidimensional databases for
advanced systems. Advanced systems, such as expert systems and
decision support systems have been used for decades to solve
semi-structured and even unstructured problems. Traditionally, these
types of systems combine inference engines and relational databases
in order to store the knowledge processing components, and they have
all done these tasks without the benefit of having a huge data
The explosive interest in OLAP has fueled
the creation of a popular new Internet newsgroup[MM89] dedicated to
OLAP issues. You can find the newsgroup at comp.database.olap.
Essentially, an OLAP system is any system
that captures summarized information and allows the summaries to be
displayed as cross-tabulations between two variables. In the
following example, we explore OLAP using the Excel spreadsheet pivot
The terms OLAP and multidimensional database
have become synonymous, thus adding to the confusion surrounding the
two terms. Essentially, a multidimensional database is a database
architecture that stores summarized information such that all
salient data items (called dimensions) are cross-referenced with
each other (see Figure 5.1). For example, a multidimensional
database might store sales totals cross-referenced by month, product
line, territory, and salesperson. The multidimensional database
could then display the sales totals according to the possible
combinations of cross-referenced tables shown in Table 5.1.
Total Sales Month by product line
Total Sales Month by territory
Total Sales Month by salesperson
Total Sales Product line by territory
Total Sales Product line by salesperson
Table 5.1 Possible plots of facts and
Figure 5.1 A sample OLAP display.
So, if this is a multidimensional database,
then what is OLAP? OLAP is a presentation front end that allows end
users to choose the dimensions and facts that will be
cross-referenced. The data does not necessarily have to come
directly from a multidimensional database. The possible data sources
for an OLAP application include:
databases (via ROLAP tools)
* Spreadsheet data
(extracted from any database architecture)
To illustrate, let?s take a close look at a
simple OLAP application. Note that while the following example may
seem overly simplistic, many Oracle shops are using this very
technique to deliver OLAP applications to their end users. Excel
spreadsheets can hold very large datasets, and in many cases,
managers can get their data loaded entirely into their spreadsheet,
thereby alleviating the need to create a client/server Oracle
application. Instead of using a client/server application, the
summary data for a spreadsheet is refreshed from Oracle each day,
ensuring that the manager has current data for the OLAP application.
Using Excel Pivot Tables For OLAP
The Microsoft Excel spreadsheet product is
an excellent way to demonstrate the capabilities of an OLAP
application. Many companies extract de-normalized information from
their Oracle databases, FTP the data to a PC LAN, and load the file
into a very large Excel spreadsheet on a client PC (see Figure 5.2).
The manager can then use Excel?s Pivot table wizard to analyze the
data, just as if they were using a multidimensional database. Of
course, this approach requires a very powerful PC, but it is a very
fast, simple, and straightforward approach to simulating a
multidimensional database with an OLAP application.
Figure 5.2 Extraction of summarized
data for PC-based OLAP.
The Oracle data extraction is usually
invoked from a Unix crontab task, which invokes an SQL script to
extract the summarized data from Oracle, format the data, and pipe
it to a flat file. Using NFS, a mount on a PC LAN can be created so
that the extracted data is transferred directly to the PC disk. The
data can then be easily loaded into a spreadsheet, where the wizard
will allow the manager to perform complex cross-tabulations and
decision support queries.
Note: To get the maximum benefit from
this exercise, I recommend that you follow along on your PC. Simply
start Microsoft Excel, and load the file called pivot.xls from the
CD-ROM in the back of this book. This short exercise will only
take about ten minutes, and it has proven of be the best method for
understanding multidimensional databases.
Step 1: Load The Sample Spreadsheet
The first step of this exercise is to load
the sample spreadsheet named pivot.xls. You can find this
spreadsheet on the CD-ROM included with this book.
Please note that the input data that is
provided within pivot.xls is highly de-normalized and ready to use.
Each row represents a line item within an order, and there is a high
degree of duplicated data. Also, note that every field is a
potential key except for qty-sold and total-cost. Getting this file
onto a PC platform involves writing an SQL statement that will
extract the required columns while joining the salient Oracle tables
(see Figure 5.3). The SQL is generally executed by invoking Oracle?s
SQL*Plus from a cron task on Unix. The resulting flat file will then
be transferred to a PC LAN by using FTP, where it will be loaded
into the Excel spreadsheet. Figure 5.3 shows what the spreadsheet
should look like.
Figure 5.3 De-normalized Oracle data
after transfer into Excel spreadsheet.
Step 2: Invoke The Pivot Table Wizard
The second step in this exercise is to
invoke the pivot table wizard. To accomplish this, choose Data|Pivot
Table. The wizard should appear, and it will enable you to define
the fact and dimension columns for the pivot table. Perform the
1. In the first wizard
screen, choose Data From Excel List, and press Next.
2. Verify the data range
(a1:l47), then press Next.
3. Finally, specify the
axis names by highlighting your choice, and then drag and drop them
onto the pivot table axis, as shown in Figure 5.4, then press
Figure 5.4 Specifying the axis for a
Now, you should have a pivot table defined
and displayed on your screen which looks similar to the one shown
earlier in Figure 5.1.
Because the entire table is in addressable
memory, it is very simple to change the axis of the pivot table and
completely recompute the table. To accomplish this, simply press the
Pivot Table Wizard button, which appears immediately above the pivot
table. You can now redefine the dimensions of the pivot table and
create a new representation of your data, as shown in Figures 5.6
Figure 5.6 Quantity with year-quarter
Figure 5.7 Quantity with region
Now, note that we can nest dimensions to
create a hierarchy of dimensions. This is a very powerful feature of
multidimensional databases and relates to our discussion of
attribute hierarchies presented in Chapter 4, Oracle Data Warehouse
Design. Figure 5.8 shows an example of nested dimensions, with the
state dimension nested within the region dimension. It is also
possible to quickly change a displayed fact. For example, in Figure
5.9, the fact is changed from total sales to total cost.
Figure 5.8 Quantity with region-state
Figure 5.9 Total cost with region
Figures 5.10 and 5.11 show how you can
quickly rotate dimensions to display total sales against the
customer and item number dimensions. It should be obvious that there
is incredible power in this ability to change dimensions and quickly
Figure 5.10 Total sales with month
Figure 5.11 Total sales with month
against item number.
As you can see, a pivot table gives a
manager the ability to analyze information in any conceivable
Even data that does not appear directly can
be displayed. For example, let?s say that normally we only display
the sum of cost or the sum of sales, but today, we want to drag a
dimension into the fact area of the pivot table. Dragging a
non-numeric data attribute into the computation area of a pivot
changes the fact from a sum to a count. As you can see in Figure
5.12, the number of orders that have been placed within each city
are now counted and display by month. In this fashion,
and data attribute can be counted, and it is not always necessary to
include only numerical items in the body of the pivot table.
Figure 5.13 shows the resulting pivot table, showing the counts of
the number of orders within each region by month. We could
count the number of any data attributes including the number of
salespersons and customers. For example, we might want to
count the number of customers, plotting this count by region and by
Figure 5.12 Counting within a pivot
Figure 5.13 The resulting pivot table
of counts of orders.
Now that you understand how multidimensional
database front ends function for decision support applications,
let?s take a look at how data warehouses fit into multidimensional
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.