OLTP vs. OLAP: Why have different systems been used for transaction processing and analytical processing in the past?
There are several reasons for this:
The different availability and disaster recovery requirements of transaction processing and analytical processing, with the requirements typically being significantly higher for transaction processing than for analytical processing. One could of course say that the requirements are standardized at the level of transaction processing - i.e. at the higher level. The disadvantage of this belize telegram screening approach, however, is that it also entails significantly higher costs, since analytical systems are also significantly larger than transaction processing systems due to the history they keep.
Queries also have different priorities: long-running analytical queries often have a lower priority than short OLTP queries, for which users also expect a completely different response time behavior.
These systems are also used by different users with different requirements. While transaction processing users usually use fixed static applications with fixed queries and typically do not have any in-depth database knowledge themselves, analytical processing often involves users creating their own ad hoc queries.
The requirements regarding security in general and data security in particular are also very different: While it is particularly important for operational transaction processing systems that all data, such as credit card numbers, are complete and correct, such data in analytical systems can often only be kept in pseudonymized or anonymized form.
Therefore, in recent decades, data has been extracted from operational transaction processing systems and stored in separate analytical systems - typically so-called data warehouses. In this way, it was possible to develop the optimal system for each of the two requirements.