Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP)

OLTP

  • OLTP stands for Online Transaction Processing.
  • OLTP is a software program that supports high transaction – oriented applications.
  • It is used for processing the system that responds immediately to the user requests.
  • OLTP system is used for data entry, financial transactions, customer relationship management (CRM) and retail sales.
  • It is technically difficult to build.
  • This system is characterized by a large number of short online transactions like Insert, Update, Delete.
  • It provides fast query processing, maintaining data integrity in multi-access environments.
  • It includes detailed information about current data and schema used to store transactional databases in the entity model.
  • It provides a flexible platform for many applications like from ATM networks to computerized shop floor automation.

Benefits of OLTP

  • OLTP reduces paper work.
  • It is faster and more accurate forecast for revenues and expenses.
  • It makes things simpler for businesses.
  • It handles large data, user volumes, more complex calculations and higher peak loads.

OLAP

  • OLAP stands for Online Analytical Processing.
  • It is a powerful technology for data discovery.
  • It performs multidimensional analysis of business data.
  • It provides the capability for complex calculations, trend analysis and sophisticated data modeling.
  • It has the ability to achieve fast access to shared multidimensional information.
  • It has the ability to create very fast aggregations and calculations of data sets.
OLAP can be divided into following types
1. MOLAP
2. ROLAP
3. HOLAP

1. MOLAP

  • MOLAP stands for Multidimensional Online Analytical Processing.
  • It is the classical form of OLAP and stores the data in an optimized multi-dimensional array storage.
Advantages of MOLAP
  • MOLAP is very compact for low dimension data sets.
  • It has an effective data extraction achieved through the pre-structuring of aggregated data.
  • It performs automated computation of higher level aggregation of the data.
  • It performs fast query operation due to optimized storage, multidimensional indexing and caching.
Disadvantages of MOLAP
  • MOLAP comes with data redundancy.
  • Sometimes the processing step can be lengthy, especially on large data.

2. ROLAP

  • ROLAP stands for Relational Online Analytical Processing.
  • It works with relational databases.
  • ROLAP depends on specialized schema design.
  • It has the ability to drill down to the lowest level in the database.

3. HOLAP

  • HOLAP stands for Hybrid Online Analytical Processing.
  • It uses relational tables to hold the larger quantities of detailed data.
  • It uses specialized storage for some aspects of the smaller quantities of more-aggregate or less-detailed data.
  • It can utilize both pre-calculated cubes and relational data sources.

Advantages of OLAP

  • OLAP increases the productivity of business managers, developers and the whole organization.
  • It enables managers to solve the problems.
  • It controls the access to strategic information for more effective decision making.
  • It reduces the application backlog because of faster delivery of applications.
  • It enables the organization to respond more quickly to market demands.
  • It enables user to analyze multidimensional data interactively from multiple perspectives.
  • It does not require large data warehouse.
  • OLAP makes easy to produce analytic measures, including time-series calculations, financial models, forecasts, allocations, regressions, etc.

Difference between OLTP and OLAP

oltp and olap

OLTPOLAP
Stands for Online Transaction Processing.Stands for Online Analytical Processing.
It is operational data.It is historical / consolidation data.
It is used to control and run fundamental business tasks.It is used to help with planning, problem solving and decision support.
It is the original source of the data.The OLAP data comes from the various OLTP databases.
Processing speed is very fast.Processing speed is slow.
The database design is highly normalized with many tables.The database design is denormalized with fewer tables and mostly uses star or snowflake schema.
It is reporting engine.It is the business process engine.
It processes simple queries.It processes complex queries.
It focuses on updating data.It focuses on reporting data.
It is characterized by a large number of short online transactions.It is characterized by low volume of transactions.