OLTP: (ERP, TX system, Client Server Architecture, Desktop application)
OLAP: (Data warehouse application -- MOLAP, ROLAP, HOLAP)
Difference:
OLTP
Current data
Short database transactions
Online update/insert/delete
Normalization is promoted
High volume transactions
Transaction recovery is necessary
Less Index
More Joins
Adopts an entity relationship(ER)
Customer-oriented, used for data analysis and querying by clerks, clients and IT professionals
OLAP
Current and historical data
Long database transactions
Batch update/insert/delete
Denormalization is promoted
Low volume transactions
Transaction recovery is not necessary
More Index
Less Joins
Adopts star, snowflake or fact constellation model and a subject-oriented database design
Market-oriented, used for data analysis by knowledge workers( managers, executives, analysis)
--OR--
Online transactional processing (OLTP) is designed to efficiently process high volumes of transactions, instantly recording business events (such as a sales invoice payment) and reflecting changes as they occur.
Online analytical processing (OLAP) is designed for analysis and decision support, allowing exploration of often hidden relationships in large amounts of data by providing unlimited views of multiple relationships at any cross-section of defined business dimensions.
--OR--
OLTP System (Operational System):
Operational data; OLTPs are the original source of the data.
To control and run fundamental business tasks
Reveals a snapshot of ongoing business processes
Short and fast inserts and updates initiated by end users
Relatively standardized and simple queries Returning relatively few records
Typically very fast
Can be relatively small if historical data is archived
Highly normalized with many tables
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability
OLAP System (Data Warehouse):
Consolidation data; OLAP data comes from the various OLTP Databases
To help with planning, problem solving, and decision support
Multi-dimensional views of various kinds of business activities
Periodic long-running batch jobs refresh the data
Often complex queries involving aggregations
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
Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method
--OR--
OLTP:
Current data
Short database transactions
Online update/insert/delete
Normalization is promoted
High volume transactions
Transaction recovery is necessary
OLAP:
Current and historical data
Long database transactions
Batch update/insert/delete
Denormalization is promoted
Low volume transactions
Transaction recovery is not necessary
