SQL Bible [Electronic resources] نسخه متنی

OLAP and Business Intelligence

Every deployed database system could be
arbitrarily divided into two broad categories — OLTP databases and OLAP
databases; some deployed systems could represent a mix of both.

An online transaction
processing (OLTP) system is optimized to support transactions: order
processing, inventory tracking, recording employee data, and so on. Such
systems are designed to process large volumes of concurrent transactions as
quickly as possible. In short, the main purpose of such a system is to
accumulate structured information.

The term OLAP was
introduced in 1993 by Dr. E.F. Codd, who also was the first to propose the
relational data model about 20 years earlier. With its various flavors — ROLAP
(relational OLAP), MOLAP (multidimensional OLAP), and HOLAP (hybrid OLAP) — it
is taking data analysis from a manual, tedious combination of art and science
into a computer-aided, exact science. (OLAP does not remove need to program for
data analysis; yet it is a major improvement over just about any other way of
analyzing large amount of data.) Dr. Codd established 12 OLAP rules to follow,
and most OLAP products conform to these in one way or another.

Multidimensional conceptual view.
OLAP operates with CUBEs of data that represent multidimensional construct of
data. Event though the name implies three dimensional data, the number of
possible dimensions is practically unlimited.

Transparency. OLAP systems should
be part of an open system that supports heterogeneous data sources.

Accessibility. The OLAP should
present the user with a single logical schema of the data.

Consistent reporting performance.
Performance should not degrade as the number of dimensions in the model
increases.

Client/server architecture. Should
be based on open, modular systems.

Generic dimensionality. Not
limited to 3-D and not biased toward any particular dimension. A function
applied to one dimension should also be able to be applied to another.

Dynamic sparse-matrix handling.
Related both to the idea of nulls in relational databases and to the notion of
compressing large files, a sparse matrix is one in which not every cell
contains data. OLAP systems should accommodate varying storage and
data-handling options.

Multiuser support. OLAP systems
should support more than one user at the time.

Unrestricted cross-dimensional
operations. Similar to rule of generic dimensionality; all dimensions
are created equal, and operations across data dimensions should not restrict
relationships between cells.

Intuitive data manipulation.
Ideally, users shouldn't have to use menus or perform complex multiple-step
operations when an intuitive drag-and-drop action will do.

Flexible reporting. Save a tree.
Users should be able to print just what they need, and any changes to the
underlying financial model should be automatically reflected in reports.

Unlimited dimensional and aggregation
levels. The OLAP cube can be built with unlimited dimensions, and
aggregation of the contained data also does not have practical limits.

Most OLAP tools — either integrated or
stand-alone — generally conform to these rules. There are many more rules
defined by theorists, as well as de-facto ones, established by the heavyweight
database market players; please refer to OLAP-specific literature and vendor's
documentation for more information.

An online analytical
processing (OLAP) system is designed to make sense out of the
accumulated data. These systems are used to discover trends and analyze
critical factors, perform statistical analysis, and so on. While important,
speed is not the main feature of such systems, as OLAP queries typically
process large amounts of data. Normally, OLAP databases extract information
from several OLTP databases called data
warehouses.

What is OLAP used for? Decision support,
sales analysis, marketing, data consolidation — the list goes on. Once data is
accumulated, OLAP steps in to make actual sense out of it. OLAP provides
multidimensional representation of data contained in OLTP data warehouses
through the
CUBE structure, which allows for creating
views of data according to different sets of criteria, and manipulate those
using sophisticated analytic functions.

Oracle, IBM, Microsoft, Hyperion, Cognos
are among the leading OLAP vendors. While an RDBMS can be a base for OLAP
services, the tools are usually not integrated or tied to a particular
database. Oracle's Data Mining utility and Oracle Express, for example, can
utilize a "native" Oracle database or Hyperion Essbase or IBM DB2 UDB.

All these acronyms refer to the way data
for the
CUBE — the primary operational unit for
the OLAP queries — is stored. The functionality, methods, and principles of
OLAP remain identical across all three.

Multidimensional OLAP (MOLAP) refers to
the situation when relational data for a CUBE, along with aggregation data, are
stored in the CUBE itself. It provides for the fastest response, and is most
appropriate for frequent use (like on-demand OLAP, without the need for
real-time data).

Relational OLAP (ROLAP) refers to the
situation when relational data for a CUBE, along with aggregation data, are
stored in the relational database. This provides for real-time querying, though
response might be slower than MOLAP as all the data need to be assembled from
scratch.

Hybrid OLAP (HOLAP) refers to the
situation when relational data for a CUBE is stored in a relational database,
while the aggregation data are stored in the CUBE itself. It was designed to
get best of both worlds: it is somewhat faster than ROLAP, and CUBE structure
is much smaller than in MOLAP case.

Oracle
9i

Of course,
Oracle also has incorporated business intelligence capability directly into
Oracle 9i Database. It allows OLAP queries to be executed
directly against an OLTP database without transferring it into a specialized
OLAP database. This approach has its pluses and minuses; one plus would be that
there is no need for a time-consuming and expensive data transfer (and
transformation) process; on the minus side is the fact that running an ad-hoc
OLAP query against your production database may slow down your operation with a
resource intensive process.

Oracle 9i Database
provides the foundation for the Oracle OLAP, providing data storage and
management capabilities, analytic functions, security, and so on, whereas the
OLAP services themselves support multidimensional calculations, forecast
functions, models, and the like. A number of wizards are provided to guide
users through the maze of choices.

Oracle provides a set of Java OLAP APIs
to program additional functionality, which enables building cross-platform
solutions using Java applications, applets, Java Server Pages, and so on. It
could be installed separately, on middle tier hardware, or integrated with a
RDBMS.

IBM DB2 UDB 8.1

IBM DB2 UDB provides OLAP capabilities
through DB2 OLAP Server and OLAP Server Analyzer. Both are add-ons developed in
collaboration with Hyperion (and its Essbase product).

The product is Java-based and uses JAPI
from Essbase. IBM supports only ROLAP and MOLAP functionality IBM DB2 UDB also
features OLAP Miner — branded by IBM as an "opportunity-discovery" component of
the IBM OLAP Server. It applies data mining algorithms to the OLAP CUBEs to
pinpoint the "surprise" areas and present them to an analyst for further
investigation.

Microsoft SQL Server 2000

Microsoft provides OLAP capabilities
through Microsoft Analysis Services, which are bundled with SQL Server 2000
(OLAP Services Components in SQL Server 7.0).

The Multidimensional Expressions (MDX)
language is used to manipulate the base unit of any OLAP analysis —
CUBE. The language is similar to SQL in
many respects, and enables the manipulation of data stored in OLAP CUBEs
Microsoft also provides external access interfaces like OLEDB,Active Data
Objects and SQL-DMO (Data Management Objects) to access OLAP functionality
within SQL Server 2000.

In addition to its predefined functions,
MDX permits the creation of custom functions. While having somewhat similar
syntax to SQL, MDX is not an SQL extension; it is a different language,
designed specifically for OLAP.