The term OLTP covers applications that work with transactional or atomic data i.e., the individual records contained within a database. OLTP applications usually just retrieve groups of records and present them to the end-user, for example, the list of computer software sold at a particular store during one day. These applications typically use relational databases, with a fact or data table containing individual transactions linked to meta tables that store data about customers and product details.
1. Limitations of OLTP
The following are the major limitations of OLTP:
- Increasing Data Storage: The companies are storing more and more data about their business and retrieving many thousands of records for immediate analysis is a time and resource consuming process, particularly when many users are using an application at the same Database engines that can quickly retrieve thousands of records for 5–7 users have to struggle when they have to return the results of large queries to thousands of users that are accessing simultaneously.
Caching frequently requested data in temporary tables and data stores can help a lot, but solves the problem partly, particularly if each user requires a slightly different set of data.
In current data warehouses where the required data might be spread across multiple tables, the complexity of the query may also cause time delays and require more system resources which means more money must be spent on database servers in order to keep up with user demands.
- Data versus Information: Business users need both data and information. Users who make business decisions based on events that are happening need the information contained within their company’s Database engines were not primarily designed to retrieve groups of records and then sum them together mathematically and they tend not to perform well when asked to do so. An OLTP application would always be able to provide the answers, but not in the typical few-seconds response times demanded by users.
Caching results doesnot help here either, because in order to be effective, every possible aggregation must be cached, or the benefit won’t always be realized. Caching on this scale would require enormous sets of temporary tables and enormous amounts of disk space to store them.
- Data Layout: The relational database model was designed for transactional processing and is not always the best way to store data when attempting to answer business questions such as “Sales of Mobile phones by region” or “Volume of credit-card transactions by month”. These types of queries require vast amounts of data to be retrieved and aggregated on-demand, something that will require time and system resources to achieve.
The answer to the limitations of OLTP is to use a different approach altogether to the problem and that approach is OLAP. OLAP applications store data in a different way from the traditional relational model, allowing them to work with data sets designed to serve greater numbers of users in parallel. OLAP data stores are designed to work with aggregated data, allowing them to quickly answer high-level questions about a company’s data and still allowing users to access the original transactional data when required.
OLAP applications differ from OLTP applications in the way that they store data, the way that they analyze data and the way that they present data to the end-user. It is these fundamental differences that allow OLAP applications to answer more sophisticated business questions.
Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)