Got it

Difference Between OLTP and OLAP

Latest reply: Dec 15, 2021 00:51:04 1326 23 19 0 0

Hello, everyone!

The post will share with you the OLTP and OLAP.

With the wide application of database technology, a large amount of data is generated in enterprise information systems. How to extract useful information for enterprise decision-making analysis from a large amount of data becomes an important problem for enterprise decision-making managers.

On-Line Transaction Processing (OLTP), a traditional enterprise database system (management information system), is mainly used for transaction processing, but its support for analytical processing is not satisfactory.

Therefore, people try to reprocess the data in the OLTP database to form a comprehensive, analysis-oriented, better decision support system (DSS).

The data of an enterprise's information system is generally managed by DBMS, but the decision database and operation database have different characteristics and requirements in the data source, data content, data mode, service object, access mode, transaction management, and physical storage. Therefore, it is not appropriate to set up a DSS directly on the database where the operation is running. Data Warehouse (Data Warehouse) technology is developed under such background.

What is OLAP?

As a multi-dimensional query and analysis tool based on data warehouse, Online Analytical Processing (OLAP) is a natural extension of data warehouse functions and an important guarantee for effective utilization of a large number of data resources in the data warehouse.

OLAP technology is based on dimension-based conceptual hierarchical modeling, and transactional data is aggregated into different layers, and these aggregates are pre-calculated and stored, thereby improving the efficiency of querying and analyzing massive information. Enables decision-makers to analyze data from multiple angles and layers based on decision-making topics.

OLAP application is to move data into multidimensional view to organize, then aggregate and merge the data, and finally use some statistical techniques to realize complex and specific queries.

The use of concept layering in OLAP mainly lies in that in a multidimensional data model, data organization is called multidimensional, and each dimension contains multiple abstract layers defined by concept layering. This organization gives users the flexibility to view the data from different angles.


Typical OLAP operations include roll-up, drill-down, and slicing and slicing.

Roll-up: A roll-up operation is performed on a data cube by hierarchically ascending the concept of a dimension or by dimension reduction. One or more dimensions are deleted by the given data cube when the dimension specification is used for roll-up.

Drill-down: The reverse operation of scrolling up during drill-down, which changes from less detailed data to more detailed data. Drill-down can be done by layering down the concept of a dimension or by introducing new dimensions. Because the drill-down operation adds more detail to a given data, it can also be done by adding new dimensions to the data cube.

Slice and slice: The slice operation makes a selection on one dimension of a given data cube, resulting in a child. Children can be understood as small "squares" that make up a data cube.

Pivot: A pivot is a visual operation that rotates the angle of view of the data to provide an alternative representation of the data.

Other OLAP operations: Some OLAP also provide other drill-through operations.

For example, drill_cross executes a query involving multiple fact tables; The drill_through operation uses the relational SQL mechanism to drill down to the bottom of the data cube to the backend relational table.

OLAP provides analytical modeling mechanisms, including calculation engines for deriving ratios, variances, etc., and calculation metrics across multiple dimensions. It produces aggregation, aggregation, and stratification at each granularity level and across all dimensions.

OLAP also supports functional models for forecasting, trend analysis, and statistical analysis.

In this sense, OLAP is a powerful data analysis tool.

What is OLTP?

OLTP is the main application mode of traditional relational databases. It mainly involves basic and routine transaction processing (insertion, modification, query, and deletion operations), such as bank transactions and ticket online sales systems. The system emphasizes the internal efficiency of databases, the hit ratio of memory indicators, and bound variables. Emphasize concurrent operations.

There are a large number of concurrent users, but only a small number of operations are performed on the database. The database focuses on fast response to user operations, which is the most important performance requirement for the database.

For OLTP, the design of the database memory is very important. If the database can be processed in the memory, the performance of the database will be greatly improved.

The OLTP system is a system in which data blocks change frequently and SQL statements are submitted frequently.

For data blocks, the data blocks should be stored in the memory as much as possible. For SQL statements, the variable binding technology is used to reuse SQL statements, reducing physical I/Os and repeated SQL parsing, and greatly improving database performance.

Difference between OLTP and OLAP

Today's data processing can be divided into two categories: online transaction processing (OLTP) and online analytical processing (OLAP). OLTP is the main application of traditional relational databases. It mainly involves basic and routine transactions, such as bank transactions. OLAP is the main application of a data warehouse system. It supports complex analysis operations, focuses on decision support, and provides intuitive and easy-to-understand query results.

The OLTP system emphasizes the database memory efficiency, the command rate of various memory indicators, the bound variables, and the concurrent operations.

OLAP systems emphasize data analysis, SQL execution market, disk I/O, and partitioning.

1. User and system orientation: OLTP is customer-oriented and is used for transaction and query processing by clerks, customers, and information technology professionals.

OLAP is intended for managers and is used for data analysis of knowledge workers (including managers, supervisors, and analysts).

2. Data content: current data of the OLTP system management. Often, this data is too trivial to be used for decision-making. OLAP manages large amounts of historical data, provides aggregation and aggregation mechanisms, and stores and manages information at different granular levels. These characteristics make the data easy to use for informed decision making.

3. Data organization model: Generally, OLTP systems adopt an entity-relationship (ER) model and application-oriented database design. OLAP-oriented systems usually use a star or snowflake models and topic-oriented database designs.

4. View: The OLTP system focuses on current data within an enterprise or department, but does not involve historical data or data of different organizations.

In contrast, OLAP systems often span multiple versions of database schemas due to organizational changes. OLAP systems also process information from different organizations, integrated by multiple data stores. OLAP data is stored on multiple storage media due to a large amount of data.

5. Access mode: The access of the OLTP system consists of short atomic transactions. Such systems require parallel control and recovery mechanisms. However, access to OLAP systems is mostly read-only.(Since most data warehouses store historical data rather than current data), although many can be complex queries.

Other differences between OLTP and OLAP include dataset size, operation frequency, and performance metrics, which are not described here.


That's all, thanks!

  • x
  • convention:

Admin Created Oct 12, 2021 02:47:50

Thank you.
View more
  • x
  • convention:

Ayeshaali Created Nov 2, 2021 16:56:14 (0) (0)
MVE Created Oct 12, 2021 05:14:25

OLTP and OLAP: The two terms look similar but refer to different kinds of systems. Online transaction processing (OLTP) captures, stores, and processes data from transactions in real time. Online analytical processing (OLAP) uses complex queries to analyze aggregated historical data from OLTP systems.
View more
  • x
  • convention:

MVE Created Oct 12, 2021 05:15:59

Learning together, every day!
View more
  • x
  • convention:

MVE Created Oct 12, 2021 08:14:31


Since OLAP is multidimensional, will it replace OLTP in coming days?
View more
  • x
  • convention:

olive.zhao Created Oct 14, 2021 08:48:57 (0) (0)
I don't think it will replace  
Moderator Author Created Oct 19, 2021 07:56:02

nice one
View more
  • x
  • convention:

MVE Created Oct 19, 2021 13:09:49

View more
  • x
  • convention:

Created Oct 25, 2021 06:13:22

View more
  • x
  • convention:

Moderator Author Created Nov 2, 2021 05:13:10

nice sharing
View more
  • x
  • convention:

Created Nov 2, 2021 14:30:08

Great share
View more
  • x
  • convention:

olive.zhao Created Nov 2, 2021 14:32:14 (0) (0)
Back to list


You need to log in to comment to the post Login | Register

Notice: To protect the legitimate rights and interests of you, the community, and third parties, do not release content that may bring legal risks to all parties, including but are not limited to the following:
  • Politically sensitive content
  • Content concerning pornography, gambling, and drug abuse
  • Content that may disclose or infringe upon others ' commercial secrets, intellectual properties, including trade marks, copyrights, and patents, and personal privacy
Do not share your account and password with others. All operations performed using your account will be regarded as your own actions and all consequences arising therefrom will be borne by you. For details, see " User Agreement."

My Followers

Login and enjoy all the member benefits


Are you sure to block this user?
Users on your blacklist cannot comment on your post,cannot mention you, cannot send you private messages.
Please bind your phone number to obtain invitation bonus.
Information Protection Guide
Thanks for using Huawei Enterprise Support Community! We will help you learn how we collect, use, store and share your personal information and the rights you have in accordance with Privacy Policy and User Agreement.