OLAP and Business Intelligence

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.

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.