OLTP is expanded as Online Transactional Processing, and OLAP is expanded as Online Analytical Processing. As the name says, OLTP is the process of managing and updating the transactions in the databases, whereas OLAP is the process of retrieving the required data from the database for the purpose of using it for Analytical actions. OLTP is usually simple and involves effortless querying in the system, while OLAP is a complex system with larger volumes of data and hence requires complicated querying.
Online Transactional Processing (OLTP)
A Data Warehouse contains the OLTP system data in de-normalized form typically in a Star or Snowflake schema structure. It stores a subset of the data in the system that is later going to be analyzed. A Data Mart is sometimes used synonymously with Data-Warehouse. Specifically, when it comes to an Enterprise, a DWH (Data-Warehouse) is the encompassing structure, whereas Data Marts then refer to self-contained parts that sum up to the DWH but can as well be managed and analyzed independently and provide a restricted view on a certain area of the enterprise, for example on a certain business process like Purchase, IT, HR etc.
So a couple of examples of the OLTP system are:
- ATM
- Banks
- Malls
- Booking trains and flights online
- E-commerce
Characteristics of OLTP Model:
- Online connectivity
- LAN,WAN
- Availability – Available 24 hours a day
- Response rate: – Rapid response rate and Load balancing by prioritizing the transactions
- Cost :– Cost of transactions is less
- Update facility :– Less lock periods ,Instant updates and Use the full potential of hardware and software
Online Analytical Processing (OLAP)
An OLAP system on the other hand refers to carrying out analysis, on historical data transactions that have occurred over time. It is mainly used for read-only querying, and therefore is optimized to serve that purpose. The system should achieve a response time within seconds for any activity, for example: expanding from a product’s sales information to the products categories sales hierarchical information or comparing numbers with previous year’s sales numbers. OLAP systems are used for their heavy read-access on contiguous blocks of data and you need good in-memory and disk performance. Typically they are ideal for SSD (Solid State Drive) based fast storage for high random and sequential reads.
Example
An E-commerce company would like to compare the figure of its sales for the month of February and March and would also like to see the sales region wise, then state wise, time wise and finally country wise.n order to achieve this, a system should be in place which will insert the data from different OLTP Databases to Data warehouse and apply the ETL process. Then OLAP developers will fetch data from the OLAP system and create different types of reports and charts based on the business requirement. Examples of OLAP software is – SAP BI/BO/BOBJ, Power BI from Microsoft, Tableau, Spotify, SAS, Python and R, Excel, Apache Spark, Splunk, Google Analytics.
Characteristics of OLAP Model:-
- Multidimensional analysis
- Support for complex queries
- Advanced database support :– Support large databases, Access different data sources, Access aggregated data and detailed data
- Easy-to-use End-user interface :– Easy to use graphical interfaces, Familiar interfaces with previous data analysis tools
- Client-Server Architecture :– Provides flexibility, Can be used on different computers, More machines can be added
OLAP vs OLTP
Category for comparison | OLAP system (Online Analytical Processing) | OLTP system (Online Transactional Processing) |
Purpose | To analyze the business: helps in decision-making, forecasting, planning, problem solving | To run the business: helps in business tasks management |
Data Source | Historical / archive data | Operational data |
Data Function | Multidimensional views of various business activities help planning, decision support | Running controlled business tasks, presenting an overview of ongoing business processes |
Inserts / Updates | Periodic long-running batch processes refresh the data | Short and fast inserts and updates carried out by end users |
Queries | Queries of any complexity | Simple queries |
Transaction speed | Depends on the amount of data | Very fast |
Space requirements | Vast because of aggregation structure | Relatively small |
OLTP vs OLAP Database design | Typically denormalized with fewer tables / star and snowflake schemas | Highly normalized with numerous tables |
Backup and Recovery | As a substitute for regular backups like in OLTP, some environments can offer to reload the data as a recovery method | Obligatory. The intelligence is crucial to run the business. Operational data loss can result in serious financial loss and legal liability |
Normalization | Tables are not normalized | Tables in the database are normalized in 3NF |
Integrity | Due to rare modifications, integrity is not affected | Maintaining data integrity constraint |
Users | Executives, CEO, data scientists | Programmers, database professionals, clerks |
Audience | Customer oriented | Market oriented |
Advantages and disadvantages of OLAP and OLTP
The system | Pros | Cons |
OLAP |
|
|
OLTP |
|
|
Conclusion
OLTP is a system to modify data online whilst OLAP is an online multidimensional data retrieval system, which extracts the data that can help in business to analyze the performance, develop strategies and make decisions. OLTP system is a provider of data for OLAP environment. The systems are functional to different objectives but both of them help deal with a big amount of data. OLAP is up to let you figure out places, time and reasons your clients buy, foresee market tendencies and future requirements thanks to data mining and analysis, whereas OLTP can grant your clients with personalized loyalty programs and provide them with a credible service.
Hopefully that gives you a good insight of what the difference is between OLTP and OLAP systems.