OLAP – Online Analytical Processing

Due to the increase in data volumes and the recognition of the business value of analytics, OLAP (Online Analytical Processing) has become very popular . OLAP allows users to analyze database information from multiple database systems at one time. So in this article i will tell you about the different aspects of OLAP -Online Analytical Processing.

Difference between OLAP and Data Warehouse :

The terms data warehousing and OLAP are sometimes used interchangeably. However, it is important to understand their differences because each represents a unique set of technologies, administrative issues, and user implications.

A data warehouse serves as a repository to store historical data that can be used for analysis. Whereas OLAP is Online Analytical processing that can be used to analyze and evaluate data in a warehouse. The warehouse has data coming from varied sources. OLAP tool helps to organize data in the warehouse using multidimensional models.

For more information on definition and features of Data Warehouse , Please read –
Data Warehouse – Definition and Features

What is OLAP ?

As soon as I say OLAP , you will be curious to know What is OLAP ?

OLAP stands for Online Analytical Processing. OLAP is the technology that enables client applications to efficiently access the data.OLAP allows business users to slice and dice data at will.

 Benefits of OLAP :

OLAP provides following benefits to analytical users :

• Pre-aggregation of frequently queried data, enabling a very fast response time to ad hoc queries.
• An intuitive multidimensional data model that makes it easy to select, navigate, and explore the data.
• A powerful tool for creating new views of data based upon a rich array of ad hoc calculation functions.
• Technology to manage security, client/server query management and data caching, and facilities to optimize system performance based upon user needs.

Types Of OLAP :

The classification of the OLAP Products is done on the basis of physical storage method only.Physical storage options affect the performance and storage requirements for cubes.Based on storage type OLAP are of three types :

1. MOLAP
2. ROLAP
3. HOLAP

1. MOLAP : 

MOLAP storage uses a multidimensional structure to contain aggregations and a copy of the base data.
                    MOLAP storage provides the potential for the most rapid query response times, depending only on the percentage and design of the cube’s aggregations. In general, MOLAP is more appropriate for cubes with frequent use and the necessity for rapid query response.

2. ROLAP :

ROLAP storage uses tables in the data warehouse relational database to store a cube’s aggregations. In contrast to MOLAP storage, ROLAP does not store a copy of the base data, accessing the original fact table when necessary to answer queries.
ROLAP query response is generally slower than that available with the other two storage strategies. A typical use of ROLAP is for large data sets that are infrequently queried.

3. HOLAP :

HOLAP storage combines attributes of both MOLAP and ROLAP. Aggregation data is stored in MOLAP structures and the base data is left in the data warehouse’s relational database. For queries that access summary data, HOLAP is the equivalent of MOLAP.
Queries that access base data, such as a drill-down to a single fact, must retrieve data from the relational database and will not be as fast as if the base data were stored in the MOLAP structure. Cubes stored as HOLAP are smaller than equivalent MOLAP cubes and respond faster than ROLAP cubes for queries involving summary data.

Terms In OLAP :

1. Measures :

Measures or Facts are the quantitative values in the database that you want to analyze. Typical measures are sales, cost and budget data. Measures are analyzed against the different dimension categories of a cube.

2. Dimensions :

Dimensions are descriptive categories by which the numeric data (measures) in a cube are separated for analysis. For example, if a cube’s measure is Production Count, and its dimensions are Time, Factory Location, and Product, users of the cube can separate Production Count into various categories of Time, Factory Location, and Product.

3. Hierarchy :

Dimensions are typically organized into hierarchies of information that map to columns in a relational database. Dimension hierarchies are grouped into levels consisting of dimension members. Each level in a dimension can be rolled together to form the values for the next highest level. For example, in a time dimension, days roll into months, and months roll into quarters.

4. Cube :

A subset of data, usually constructed from a data warehouse, organized and summarized into a multidimensional structure defined by a set of dimensions and measures.

5. Fact Table :

A central table in a data warehouse that contains numerical measures and keys relating facts to dimension tables. Fact tables contain data that describes a specific event within a business, such as a bank transaction or product sale.

Hope your doubts about OLAP would have been cleared after reading this article. I have tried my level best to cover all the topics related to OLAP – Online Analytical Processsing.
Please provide your comments and useful thoughts. Thanks for reading.