Problem solve Get help with specific problems with your technologies, process and projects.

Why OLAP deserves more attention

This tip offers an overview of OLAP technology and explains why more organizations should be using it.

The first commercial multidimensional (OLAP) products appeared approximately 30 years ago (Express). When Edgar Codd introduced the OLAP definition in his 1993 white paper, there were already dozens of OLAP products for client/server and desktop/file server environments. Usually those products were expensive, proprietary, standalone systems afforded only by large corporations, and performed only OLAP functions.

After Codd's research appeared, the software industry began appreciating OLAP functionality and many companies have integrated OLAP features into their products (RDBMS, integrated business intelligence suites, reporting tools, portals, etc.). In addition, for the last decade, pure OLAP tools have considerably improved and become cheaper and more user-friendly.

These developments brought OLAP functionality to a much broader range of users and organizations. Now OLAP is used not only for strategic decision-making in large corporations, but also to make daily tactical decisions about how to better streamline business operations in organizations of all sizes and shapes.

However, the acceptance of OLAP is far from maximized. For example, one year ago, The OLAP Survey 2 found that only thirty percent of its participants actually used OLAP.


General purpose tools with OLAP capabilities

Organizations do not want to use pure OLAP tools or integrated business intelligence suites for different reasons. But many of organizations may want to use OLAP capabilities integrated into popular general purpose applications development tools which they already use. In this case, the organizations do not need to buy and deploy new software products, train staff to use them or hire new people.

There is another argument for creating general purpose tools with OLAP capabilities. End users work with the information they need via applications. The effectiveness of this work depends very much on the number of applications (and the interfaces, data formats, etc. associated with them). So it is very desirable to reduce the number of applications (ideally to one application). General purpose tools with OLAP capabilities allow us to reach the goal. In other words, there's no need to use separate applications based on pure OLAP tools.

The advantages of such an approach to developers and end users are clear, and Microsoft and Oracle have recognized this. Both corporations have steadily integrated OLAP into their RDBMSs and general purpose database application development tools.

Microsoft provides SQL Server to handle a relational view of data and the Analysis Services OLAP engine to handle a multidimensional cube view of data. Analysis Services provides the OLE DB for OLAP API and the MDX language for processing multidimensional cubes, which can be physically stored in relational tables or a multidimensional store. Microsoft Excel and Microsoft Office both provide access to Analysis Services data.

Oracle has finally incorporated the Express OLAP engine into the Oracle9i Database Enterprise Edition Release 2 (OLAP Option). Multidimensional cubes are stored in analytical workspaces, which are managed in an Oracle database using an abstract data type. The existing Oracle tools such as PL/SQL, Oracle Reports, Oracle Discoverer and Oracle BI Beans can query and analyze analytical workspaces. The OLAP API is Java-based and supports a rich OLAP manipulation language, which can be considered to be the multidimensional equivalent of Oracle PL/SQL.


Granulated OLAP

There is yet another type of OLAP tool, different from pure OLAP tools and general purpose tools with OLAP capabilities: OLAP components. It seems that this sort of OLAP is not as appreciated.

The OLAP component is the minimal and elementary tool (granula) for developers to embed OLAP functionality in applications. So we can say that OLAP components are granulated OLAP.

Each OLAP component is used within some application development environment. At present, almost all known OLAP components are ActiveX or VCL ones.

All OLAP components are divided into two classes: OLAP components without an OLAP engine (MOLAP components) and OLAP components with this engine (ROLAP components).

The OLAP components without OLAP engines allow an application to access existing multidimensional cubes on MOLAP server that performs a required operation and returns results to the application. At present all the OLAP components of this kind are designed for access to MS Analytical Services (more precisely, they use OLE DB for OLAP) and were developed by Microsoft partners (Knosys, Matrix, etc.).

The ROLAP components (with OLAP engine) are of much greater interest than the MOLAP ones and general purpose tools with OLAP capabilities because they allow you to create flexible and efficient applications that cannot be developed with other tools.

The ROLAP component with an OLAP engine has three interfaces:

  • A data access mechanism interface through which it gets access to data sources
  • APIs through which a developer uses a language like MDX to define data access and processing to build a multidimensional table (cube). The developer manages properties and behavior of the component so it fully conforms to the application in which the component is embedded
  • End-user GUI that can pivot, filter, drill down and drill up data and generate numbers of views from a multidimensional table (cube).

As a rule, ROLAP components are used in client-side applications, so the OLAP engine functions on a client PC. Data access mechanisms like BDE (Borland Database Engines) or ADO.NET allow you to get source data from relational tables and flat files of an enterprise. PC performance nowadays allows the best ROLAP components to quickly process hundreds of thousands or even millions of records from these data sources, and dynamically build multidimensional cubes and perform operations with them. So very effective ROLAP and DOLAP are realized -- and in many cases, they are more preferable than MOLAP.

For example, the low price and simplicity of using a ROLAP component is the obvious (and possibly the only) choice for developers to create mass-deployed small and cheap applications, especially single-user DOLAP applications. Another field in which these components may be preferable is real-time analytical applications (no need to create and maintain MOLAP server, load cubes).

The most widely-known OLAP component is the Microsoft Pivot Table, which has an OLAP engine and access to MS Analytical Services so the component is both MOLAP and ROLAP. Another well-known OLAP (ROLAP) component is DesicionCube of Borland corporation.

Some ROLAP components have the ability to store dynamically-built multidimensional cubes which are usually named microcubes. This feature deserves attention from applications architects and designers because it allows them to develop flexible and cheap applications like enterprise-wide distributed corporate reporting system or Web-based applications.

For example, the ContourCube component stores a microcube with all associated metadata (in fact, it is a container of an analytical application like an Excel workbook) in compressed form (from 10 up to 100 times). So this microcube is optimized for use on the Internet and can be transferred through HTTP and FTP protocols, and via e-mail. An end user with ContourCube is able to fully analyze the microcube. InterSoft Lab, developer of ContourCube component, has also developed several additional tools to facilitate the development, deployment and use of such distributed applications.

At present, there is a broad range of pure OLAP tools, general purpose tools with OLAP capabilities and OLAP components. To make the best choice, developers must understand the benefits and disadvantages of all sorts of OLAP.

About the author

Alexey Reznichenko had been working as database administrator and database applications developer for 15 years. Recently, he has been the editor of Mir Oracle (journal of Russian Oracle users), corporate editions of Oracle and Informix. Alexey was the first editor of He can be reached at

Dig Deeper on Oracle data warehousing

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.