| |
OLTP SystemOnline Transaction Processing(Operational System) |
OLAP SystemOnline Analytical Processing(Data Warehouse) |
| Source of data |
Operational data; OLTPs are the original sourceof the data. |
Consolidation data; OLAP data comes from thevarious OLTP Databases |
| Purpose of data |
To control and run fundamental business tasks |
To help with planning, problem solving, anddecision support |
| What the dataReveals |
A snapshot of ongoing business processes |
Multi-dimensional views of various kinds ofbusiness activities |
| Inserts andUpdates |
Short and fast inserts and updates initiated byend users |
Periodic long-running batch jobs refresh the data |
| Queries |
Relatively standardized and simple queriesReturning relatively few records |
Often complex queries involving aggregations |
| ProcessingSpeed |
Typically very fast |
Depends on the amount of data involved; batchdata refreshes and complex queries may takemany hours; query speed can be improved bycreating indexes |
| SpaceRequirements |
Can be relatively small if historical data isarchived |
Larger due to the existence of aggregationstructures and history data; requires more indexesthan OLTP |
| DatabaseDesign |
Highly normalized with many tables |
Typically de-normalized with fewer tables; use ofstar and/or snowflake schemas |
| Backup andRecovery |
Backup religiously; operational data is critical to run the business,data loss is likely to entailsignificant monetary loss and legal liability |
Instead of regular backups, some environmentsmay consider simply reloading the OLTP data as arecovery method |