What is the difference between OLTP and OLAP?
If we keep all the data together of any organization then the size of data will be very big. Most of the data are old and historical data and these are not operational on current data. But, the most commonly used data is the current data and the size of this data is small than the historical data. So, if we keep these two types of data together the time for accessing the data, will increase and the database will be slow. Therefore, we divide the database into types of historical data(Online Analytical Processing) and day-to-day data(Online Transaction Processing) and store them separately. So, let's dive deeper and learn more about the differences among them.
OLAP stands for Online Analytical Processing. The database here, stores the historical data and historical data processing are done. It allows users to analyze the database information from multiple databases at the same time. This analysis is then used for decision making and planning for companies and organizations.
Example 1: If any organization wants to launch any new policy then it can use past information about how the customers have responded to any similar policy. This information will help them to make their policy more user-friendly and successful among their customers.
Example 2: Shopping websites like Amazon use past purchases by the customers and come up with the personalized homepages for their customers.
OLTP stands for Online Transaction Processing. This system captures and maintains transaction in the database. This mainly focuses on the fast processing of the database as the read and write operations are performed here frequently.
Example 1: The ATM machines are an OLTP application. The online transactions that we perform are stored and updated here.
Example 2: The online Airline Ticket Booking System is also an example of OLTP where they can book tickets.
- Characteristics: OLTP handles small transaction systems whereas the OLAP is a historical data retrieval and processing system.
- Users: The OLTP systems are mainly used by hundreds of users like clerks, Database Admin, online shoppers, etc, whereas OLAP systems are used by data analysts, business analysts, and executives.
- Functions: The OLTP systems provide day to day operations for organizations. These operations are simple and repetitive. The bank clients mainly perform operations like money deposit and withdrawal on a repetitive basis. OLAP systems help organizations in making decisions, planning, and problem-solving for them.
- Operations allowed: OLTP allows both read and write operations as the user performs insert, update and delete tasks. But, OLAP allows only read operations and rarely write operation as the main task here is to extract the data for analysis.
- Source: Transactions are the source of OLTP systems. OLTP databases become the source for OLAP systems.
- Amount of Data: OLAP systems stores a large amount of data usually in TB and PB (PetaByte)whereas OLTP systems stores relatively less amount of data usually in MB and GB.
- Backup: OLTP systems backups the complete data regularly. OLAP needs to back up the data from time to time and is not as important as backups in OLTP systems.
- Response Time: OLTP systems respond within milliseconds whereas OLAP systems may take from seconds to minutes to respond depending on the amount of data being processed.
- Tables: Normalized tables(3NF) are used in OLTP whereas the tables may not be normalized in OLAP.
This was about the differences between OLAP and OLTP. Hope you learned something new today.
Keep Learning :)