14 - Online Analytical Processing

Summary

Chapter Objectives

✓ To understand the need for online analytical processing

✓ To distinguish OLAP from OLTP and data mining

✓ To comprehend the representation of multi-dimensional view of data

✓ To understand and apply the concept of data cube in real world applications

✓ To implement multi-dimensional view of data in Oracle

✓ To understand different types of OLAP Servers such as ROLAP and MOLAP

✓ To able to perform different OLAP Operations on the database

Introduction to Online Analytical Processing

As there is a huge growth in data warehousing in recent past, the demand for more powerful access tools that help in advanced analytical processing from historical data has increased. Online Analytical Processing (OLAP) and data mining are two types of access tools that have been developed to meet the demands of management users. OLAP and data mining are distinct in what they offer the user and due to this they are complementary technologies.

OLAP is a design paradigm that provides a method to extract useful information from a physical data store. It aggregates information from multiple systems and provides summarized information/view to the management, while data mining is used to find hidden patterns within the data.

OLAP summarizes data and makes forecasts. For example, it answers operational questions like ‘What are the average sales of cycles, by region and by year?’ Data mining discovers hidden patterns in data and operates at a detailed level instead of a summary level. For instance, in a telecom industry where customer churn is a key factor, data mining would answer questions like, ‘Who is likely to shift service providers and what are the reasons for that?’ In conclusion, OLAP answers questions such as ‘Is this true?’ while data mining answers questions as ‘Why is this happening? And what might happen if …?’

Thus, OLAP and data mining complement each other and help the management in better decision taking. An environment that consists of a data warehouse/data marts together including tools such as OLAP and/or data mining are collectively known as Business Intelligence (BI) technologies.

Defining OLAP

Dr E.F. Codd (1993) has defined OLAP as ‘the dynamic synthesis, analysis, and consolidation of large volumes of multi-dimensional data’.

From this definition, it is clear that OLAP deals with multi-dimensional view of data as compared to a simple relational database view.