OLTP systems are heavily write-focused on fast inserts and updates of records.
OLAP systems are read-focused.
Transactional workloads are those that require fast inserts and updates of records.
The reason why we treat these use cases differently is that transactional systems are heavily write-focused.
Example:
For example, take a banking system. Deposit your salary from your company to your account — that's a transaction. It updates your balance field.
The bank is using an online transaction processing (OLTP) system.
Analytical workloads, on the other hand, tend to read the entire dataset and are often used for planning or decision support.
Analytical systems can be periodically populated from the entirety of the operational systems.
Example:
For example, a bank regulator might require a report of every customer who transferred more than $10,000 to an overseas account. They might also ask the bank to include customers who tried to transfer $10,000 in smaller chunks over a week.
Such a report requires scanning a large dataset and complex queries that include aggregations over moving time windows in SQL. This is an example of an online analytical processing (OLAP) workload.
| Transactional | Analytical | |
|---|---|---|
| Source of data: | Operational data; OLTPs are the original source of the data | Consolidated data; OLAP data comes from various OLTP databases |
| Purpose of data: | Control and run fundamental business tasks | Help with planning, problem solving, and decision support |
| What the data shows: | Snapshot of ongoing business processes | Multidimensional views of various 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 amount of data involved; improved by indexes |
| Space requirements: | Relatively small if historical data is archived | Larger, with more indexes than OLTP |