OLTP vs. OLAP
We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.
OLTP (On-line Transaction Processing) System deals with operational data. Operational data are those data involved in the operation of a particular system.
Example: In a banking System, you withdraw amount through an ATM. Then account Number,ATM PIN Number,Amount you are withdrawing, Balance amount in account etc are operational data elements.
OLAP (On-line Analytical Processing) System deals with Historical Data or Archival Data. Historical data are those data that are archived over a long period of time.
OLAP is also referred to as DSS (Decision Support System).
Example: If we collect last 10 years data about flight reservation, The data can give us many meaningful information such as the trends in reservation. This may give useful information like peak time of travel, what kinds of people are traveling in various classes (Economy/Business)etc.
The biggest difference between an OLTP and an OLAP system is the amount of data analyzed in a single transaction. Whereas an OLTP handles many concurrent users and queries touching only a single record or limited groups of records at a time, an OLAP system must have the capability to operate on millions of records to answer a single query.The following table summarizes the differences between OLPT and OLAP:
CHARACTERISTIC of OLTP
System scope/view Single business process (Operational: ERP, CRM)
Data sources One
Data model Static
Dominant query type Insert/update
Data volume per transaction Small
Data volume Small/medium
Referesh Immediate
Bulk load/insert/update No
History data Not available
Response times < 1 second
System availability High “24/7″
Typical user Front office
Number of users Large
Example:
What is the Salary of Mr.Shyam?
What is the address and email id of the person who is the head of science department?
CHARACTERISTIC of OLAP
System scope/view Multiple business subjects (Decision Support System)
Data sources Many
Data model Dynamic
Dominant query type Read
Data volume per transaction Big
Data volume Large
Referesh Periodic
Bulk load/insert/update Yes
History data Available
Response times Can be in minutes
System availability Relaxed “8/5″
Typical user Managers/ Executive
Number of users Small/medium
- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF).
- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).
The following table summarizes the major differences between OLTP and OLAP system design.
OLTP System
|
OLAP System
|
|
Source of data |
Operational data; OLTPs are the original source of the data. |
Consolidation data; OLAP data comes from the various OLTP Databases |
Purpose of data |
To control and run fundamental business tasks |
To help with planning, problem solving, and decision support |
What the data |
Reveals a snapshot of ongoing business processes |
Multi-dimensional views of various kinds of business activities |
Inserts and Updates |
Short and fast inserts and updates initiated by end users |
Periodic long-running batch jobs refresh the data |
Queries |
Relatively standardized and simple queries Returning relatively few records |
Often complex queries involving aggregations |
Processing Speed |
Typically very fast |
Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes |
Space Requirements |
Can be relatively small if historical data is archived |
Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP |
Database Design |
Highly normalized with many tables |
Typically de-normalized with fewer tables; use of star and/or snowflake schemas |
Backup and Recovery |
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability |
Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method |
相关推荐
简明介绍OLTP和OLAP的含义及差异对比。
6-5+腾讯广告OLTP_OLAP实践
OLTP和OLAP技术融合架构实践.pptx
基于Oracle的OLTP与OLAP数据库内存设计和优化.pdf
本文是关于OLTP与OLAP的区别精简总结。
本文简介OLTP和OLAP的工作原理,描述了OLAP和OLTP的工作场景、技术选型、特点及对比,希望大家喜欢! 在系统软件出现的初期,数据通常被保存在单个文件中进行管理,但随着业务的不断发展与 系统数据处理逻辑的不断...
OLTP和OLAP技术融合的探索实践_梁福坤@饿了么 百度外卖.pdf
面向OLTP、OLAP、批处理、流处理场景的大一统SQL引擎
OLTP与OLAP系统数据库建模思考与实践
OLTP和OLAP技术融合的探索实践_
基于Oracle的OLTP与OLAP数据库设计及实现.pdf
OLTP与OLAP数据处理大致可以分成两大类:联机事务处理OLTP(on-line transaction processing)联机分析处理OLAP(On-L
绍了OLAP和0I.TP处理系统的概念,根据这两种数据库应用在实时性、并发性及数据量大小等方面的 不同,数据库在设计方面侧重的技术各有不同,阐述了数据库设计技术,即内存设计、变量绑定、sQL并行执行、 表分区存储...
PG专场:数据在PG中的升华 -流计算,OLTP,OLAP(德歌).pdf
OLTP与OLAP业务系统的Oracle优化思路[归类].pdf
当今的数据处理大致可以分成两大类:联机事务处理OLTP(on-line transaction processing)、联机分析处理OLAP(On-Line Analytical Processing)
蝙蝠面向OLTP,OLAP,批处理,流处理场景的大一统SQL引擎开发环境JDK 1.8以上Maven的3.3+需要先执行mvn eclipse:eclipse,否则项目里用到的一些java源文件会发现,这些java源文件是通过模板生成的。模块依赖关系...
OLAP (Online Analytical Processing) 和OLTP (Online Transaction Processing)是两种不同的数据库处理方式。OLTP用于管理数据中心的业务处理,OLAP则用于数据库分析和信息提取。这篇文章将介绍OLAP、OLTP的概念、优...
真相与和解委员会 高效支持 OLTP 和 OLAP 工作负载并发执行的事务行列存储 MengsiLou, Zhipeng Luo, Qihang Chen
Oracle决策支持系统下的性能调整和优化原则