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!
