Are you looking for a good technology for data discovery? This article explains the software tool that helps to analyze the data. That is Online Analytical Processing. Read out the article to get more information about online analytical processing.
Online analytical processing (OLAP) is a category of software tools that provides an analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data.
Generally, this tool is used for budget forecasting, sales forecasting, financial reporting, and other planning & forecasting needs of the organization.
How does it work?
For example, it provides time series and trend analysis views. To analyze data, it collects data from multiple data sources, stores data in data warehouses, and again organizes data in the form of an OLAP cube.
The chief component of online analytical processing is the OLAP server, which sits between a client and a database management system (DBMS), and which understands how data is organized in the database and has special functions for analyzing the data.
There are OLAP servers available for nearly all the major database systems.
It is more important to know about the Online analytical processing cube. The OLAP cube is a data structure that allows you to analyze data very quickly.
In the OLAP cube, numeric facts (measures) are categorized by dimensions. OLAP holds multidimensional data. The OLAP cube helps to store and analyze this multidimensional data.
Advantages of Online Analytical Processing
- Helps to get all the data together to create accurate and quick information about the business.
- Helps to analyze the time series
- Provides platform for all type of business including planning, budgeting, forecasting, financial reporting, data warehouse reporting
- Allows users to do compatible calculations
- Allows users to divide big cube into dice cube data by several dimensions, measures, and filters
- Helps the end-users to analyze data in multiple dimensions so that they make better decisions in business
Disadvantages of Online Analytical Processing
- It is difficult to have a large number of dimensions in a single OLPA cube
- Snowflake schema required for organizing data is complex to implement
- Modification of an OLAP cube requires a full update of the cube that consumes more time
Analytical operations in Online analytical processing
Generally, OLAP has four basic analytical operations.
Roll-up operation: It is also called ‘aggregation’. We can perform this operation in two ways
- Reduction of dimension: It is the system in which the cube reduces its dimension.
- Climbing up concept hierarchy. It is the system of grouping things based on their level.
Above image shows the roll up operation.
- Here cities New York and Washington rolled up into USA
- The sales figures of the cities were 400 and 550 and became 950 after rolled up
Drill-down operation: It is the opposite process of roll-up. It performs in 2 ways
- Increasing of dimension
- Climbing down the concept hierarchy
This image is showing drill down operation
- Quarter 1 is divided into months January, February, and March
- Months dimension is added
Slice and dice operation: In slice operation, one dimension is selected and a subcube is created. In dice operation, two or more dimensions are selected and subcubes are created.
Pivot operation: In this operation, to provide a substitute presentation of data, you need to rotate the data axes.
When do you use online analytical processing?
You can use OLAP in the following situations.
- When you are required to perform complex analytical and ad hoc quickly without interrupting and affecting the OLTP system.
- When you need to issue reports using your data to the business users in an easy way.
- When you want to deliver several aggregations to help the user with consistent and quick results.
What are the main types of Online Analytical Processing?
Three main types of Online analytical processing are
- Relational OLAP (ROLAP): In this type, data is stored in a relational database. It allows us to analyze multidimensional data. In relational OLAP, data accuracy is very high. It offers expandability. That means it manages a large amount of data even when the data is increasing. Some disadvantages are also there with this type. It requires more manpower, hardware, and software. It has the lowest query performance system.
- Multidimensional OLAP (MOLAP): It is cube-based, multidimensional array structured data storage. In this system, computation is very fast.
- Hybrid OLAP (HOLAP): This is the combination of relational OLAP and multidimensional OLAP. Hence in this system expandability is more and computation is fast. It stores aggregated data in a multidimensional cube and detailed information in a relational database.
Apart from these three main types, some other types are given below.
Web OLAP (WOLAP): This is based on a web browser. In Web OLAP, OLAP application is available by the web browser. It is a three-tier architecture that includes a database server, client, and an interface. This application does not require deployment in the client’s system. It requires only a web browser and a network connection.
Desktop OLAP (DOLAP): In this system, the user can use his desktop and can download the data from the source.
Mobile OLAP (MOLAP): User can access data through mobiles.
Spatial OLAP (SOLAP): It allows users to explore the data that resides in the spatial database easily and quickly.
Difference between OLAP and OLTP
What is OLTP?
OLTP means online transaction processing. It is an operational system used for handling recent operational data.
Following are the differences between OLAP and OLTP.
- OLAP is the system used for the analysis of data, OLTP is the system used for the transaction of data.
- OLAP is identified by a large amount of data, whereas OLTP is identified by a large number of small amounts of data.
- OLAP is large in size basically ranging from 1Tb to 100Pb, OLTP is small in size ranging from 1Mb to 10 Gb.
- OLAP operates with a data warehouse, OLTP operates with a traditional database management system.
- Processing speed is less in OLAP, but OLTP has a faster processing speed.
- OLAP reply time is more, usually takes seconds to minute to respond, OLTP responds fastly, takes milliseconds.
- OLTP needs both read and write operations, but OLAP needs only read operations.
- The objective of OLAP is to make decisions with the help of large data sources, the objective of OLTP is day-to-day operations.
- Queries are complex in OLAP, queries are simple in OLTP.
- User strength is low in OLAP. Its database allows only hundreds of users, whereas the OLTP database allows thousands of users.
- OLAP helps to improve the productivity of business analyst, OLTP helps to improve the productivity and self-service of users
- OLAP is created for business analysis whereas OLTP is created for real-time business operations
Get more definitions about online analytical processing (OLAP) and other ERP related terms here.