Online Analytical Processing (OLAP)

Online analytical processing falls in the category of software technology which helps analysts, managers & executives to gain insights from data with the help of regular, immersive access to a variety of information that has been transformed from raw data.

OLAP characteristics:

The characteristics are as follows:

Enables business users to understand the multidimensional and logical view of the data which the data warehouse contains.
Enables users to drill down for more details or perform roll up for aggregation of metrics.
Enable users to perform complicated calculations and comparisons
Demonstrate results using some meaningful ways, including charts and graphs.

Basic virtues of OLAP

It helps analysts, executives, and managers to gain meaningful insights from the demonstration of data.
Multidimensional analysis can be performed easily.
Provides the facility to drill down and roll up within each dimension
Fast response
It can be implemented on the web
Designed for highly interactive analysis.
Allows data to be viewed from different perspectives

OLAP models

ROLAP(Relational online analytical processing)

IT Acts as an alternative to ROLAP technology
It does not require pre-computation and storage information.
ROLAP tools access data present in the relational database and produce SQL queries to compute information at specific levels when end-user requests.
With ROLAP, there is a possibility to create additional database tables at any combinations of dimensions.

Working of OLAP

The user presents a query.
Results obtained in the query are saved in a small, local, and multidimensional database.
The user against the local database performs the analysis
If the user needs additional data to carry out analysis, then another query is requested by the user, and the analysis continues.

MOLAP(Multidimensional online analytical processing)

Data is stored in a multidimensional cube.
Data is not stored in a relational database, but it is stored in proprietary formats.

Advantages of MOLAP

Excellent performance ? The primary purpose behind designing MOLAP cubes is for faster retrieval of data and optimal slicing and dicing operations.
Ability to perform complex calculations ? while creating the cube, all calculations are pre-generated. Therefore, all complex calculations can be performed easily.

Disadvantages of MOLAP

Can handle only limited data ? In MOLAP, all calculations are carried out when the cube is designed; hence it is not feasible to include large amounts of data in the cube itself. Hence only summary-level information is included in the cube.
Additional investment required ? cube technology is often restrictive, and they do not exist readily in an organization. Therefore to adopt MOLAP technology, investments in human and capital resources are required.

HOLAP(Hybrid online analytical processing)

It is a hybrid model which consists of 2 different types of OLAP, namely Relational Online Analytical Processing (ROLAP) and Multidimensional Online Analytical Processing (MOLAP). OLAP can be defined as a method created for business intelligence activities that consist of managing and analyzing the data in the data warehouse systems. HOLAP consists of tables and databases, which are formed using relational and multidimensional database management systems. The ability to pile up data in relational and multidimensional types of databases is permitted by HOLAP.

Usability

The HOLAP model can be designed according to the project requirements, which provides more creative options for database system architects.
Stores data from 2 databases, namely relational database and multidimensional database.
When data are accessed from the combination of 2 databases, it results in fetching more accurate data, thereby reducing data redundancy.
Lesser processing time for the applications.

Advantages of HOLAP

Good accessibility is provided when compared with ROLAP & MOLAP models.
The query executes faster because of the caching feature.
The performance of the query is moderate; it is faster than ROLAP and slower than MOLAP.
Cubes that are present in HOLAP are smaller in size because only necessary data are fetched for processing.
It has a higher processing ability when compared with ROLAP and MOLAP.
It can accommodate massive data because a relational database is present inside HOLAP.
Its caching feature plays a vital role in minimizing the complexity of this model.
In HOLAP, MOLAP & ROLAP are converted into a single hub; hence HOLAP provides more efficiency and less processing time needed for the application.
With the help of HOLAP, all databases can be integrated to form a singular model.

Disadvantages of HOLAP

A lot of storage space is occupied as data from both relational databases and multidimensional databases are present.
Processing can be slow when your query involves data fetched from relational databases as the ROLAP model consists of categorical data only.
Processing of the system needs to be done each time the data is updated, inserted, or deleted from the database.
Maintenance is a complex task since a lot of updates occur at frequent intervals.
Difficult to strike a balance in storage and performance while designing the system.

DOLAP(Database online analytical processing)

DOLAP is a desktop-based, single-tier architecture technology.

Advantages of DOLAP

User friendly.
Good query performance.
Low cost for maintenance.
Easy to deploy.
Very beneficial for mobile users.

Disadvantages of DOLAP

Provides limited functionality.
Limite data capacity.

Database OLAP? It refers to relational database management systems. Its main job is to support OLAP structures and perform OLAP calculations.

Web OLAP ? It refers to online analytical processing where data for OLAP models are accessible through a web browser.

Enterprise OLAP

First-generation

It acted as a decision support system for small-scale decision-making for some analysts.
It is included with powerful analytical capabilities.
Complex to use.

Second generation

Complex mainframe computing was replaced with an easy-to-use graphical user interface and click interfaces.
It runs on client-server architecture, and hence it was able to provide support to OLAP for firing queries and reporting.
OLAP capabilities were still limited.

Third generation

The third generation consisted of a web-enabled data warehouse that supported a large group of users.

OLAP engine design

RDBMS dependent ? OLAP engine is entirely dependent on RDBMS for multidimensional processing to access summary data

All joins, aggregations, and calculations are carried out within the database itself, which causes severe problems for web-enabled systems. Due to this, there is a need for many temporary tables. Only five concurrent users will bring the OLAP system to its knees. There is a lot of overhead caused in creating, inserting, and allocating disk space, and performing modification in system tables for each calculation is vast.

Dependence on the engine? The summary data is accessed by generating SQL, and all processing is carried out in the middle layer. Problems faced in this approach is as follows:
Large network traffic.
Hefty memory requirements.

Intelligent OLAP engine ? The intelligent OLAP engine posses the intelligence to determine what type of request it is and where it can be carried out using optimal resources. Furthermore, due to its intelligence, the engine can distribute joins, aggregations among the engine component and the RDBMS.separation of presentation, logical and data layers are possible in this model, which helps balance system processing and optimization of network traffic.

Difference between OLTP system and Data warehouse

Sr no	Characteristics	OLTP systems	Data warehouse
1	Analytical capabilities	Very low	moderate
2	Data available for a single session	limited	Small to medium size
3	Result set size	small	large
4	Granularity of data	detail	Detail & summary
5	Data currency	current	Current & historical
6	Access method	predefined	Predefined and Adhoc
7	Basic motivation	Collect and input data	Provide information
8	Data model	Design for data updates	Design for queries
9	Frequency for performing updation	Very frequent	read-only
10	Scope of user interactions	Single transactions	Throughout data content