Difference between OLTP and OLAP

 

• Generally speaking, data in an OLTP database is generally organized into relational tables to reduce redundant information and to increase the speed of updates. SQL Server enables a large number of users to perform transactions and simultaneously change real-time data in OLTP databases. Examples of OLTP databases include airline ticketing and banking transaction systems.
• OLAP technology organizes and summarizes large amounts of data so that an analyst can evaluate data quickly and in real time. SQL Server 2000 Analysis Services organizes this data to support a wide array of enterprise solutions, from corporate reporting and analysis to data modeling and decision support. 
• The following table summarizes the major differences between OLTP and OLAP system design.

 

	OLTP System  Online Transaction Processing  (Operational System)	OLAP System  Online Analytical Processing  (Data Warehouse)
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Normalization	fully normalized	Possibly partially denormalized for performance reasons
Organization	Data stored revolves around business functions	Data stored revolves around information topics.
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
Index	Optimizes update performance by minimizing the number of indexes	Optimizes adhoc queries by including lots of indexes

 

Furthermore,  Data Warehouse and OLAP

Although sometimes used interchangeably, the terms data warehousing and online analytical processing (OLAP) apply to different components of systems often referred to as decision support systems or business intelligence systems. Components of these types of systems include databases and applications that provide the tools analysts need to support organizational decision-making.

A data warehouse is a database containing data that usually represents the business history of an organization. This historical data is used for analysis that supports business decisions at many levels, from strategic planning to performance evaluation of a discrete organizational unit. Data in a data warehouse is organized to support analysis rather than to process real-time transactions as in online transaction processing systems (OLTP).

OLAP technology enables data warehouses to be used effectively for online analysis, providing rapid responses to iterative complex analytical queries. OLAP's multidimensional data model and data aggregation techniques organize and summarize large amounts of data so it can be evaluated quickly using online analysis and graphical tools. The answer to a query into historical data often leads to subsequent queries as the analyst searches for answers or explores possibilities. OLAP systems provide the speed and flexibility to support the analyst in real time.