| ||
Type of system | OLTP System Online Transaction Processing (Operational System) | OLAP System Online Analytical Processing (Data Warehouse / Data Mart) |
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 denormalized 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 |