OLAP and Business Intelligence

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.

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.

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.


Oracle OLAP CUBE does not relate to the GROUP BY CUBE clause that groups the selected rows, based on the values of all possible combinations, and produces a single aggregate row for each group (cross-tabulation).


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.


See Chapter 16 for more information on programming interfaces.

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.