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 | 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 |
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.