OLTP vs OLAP

One of the most important subjects regarding information systems and databases is the difference between OLAP and OLTP.

In order to approach the issue, we’ve built this very complete and comprehensive article to explain further on these ideas and solidify your knowledge of them.

Aiming to fully understand and compare these two types of systems, you have to know what they are, the specificities of each and how they provide functionality individually.

So, we prepared a lot of information about OLAP and OLTP systems, concluding this resource with a comparative analysis between them.

In the next chapters, we’ll be describing each topic in a complete, yet simple way. Before going any further on these subjects, take a lot on the thorought infographic which aims to compare the two approaches:

OLTP vs OLAP

What is OLAP?

Online Analytical Processing is a computer technology term referring to systems focused on analysing data of one or multiple databases. This kind of systems are characterized for their analytical capabilities, addressing multi-dimensional or one dimension data, processing all the information and creating the means to report that information. The standard applications of OLAP are bussiness intelligence, data writing and reporting, predictive analytics, throught data mining processes.

OLAP operations and databases

On the database level, the operation of these systems is defined by a low number of transactions, dealing with archived and historical information. The data is seldom updated, identifying the SELECT database operation as the key feature of the system. Therefore, this kind of databases are based on READ operations, aggregating all available information, and providing the tools to analyse large groups of information.

Databases that work as data warehouses apply to this methodology, optimizing reading and aggregation operations of its multidimensional data model. Thus providing a great support for data analysis and reporting operations, critical in these kind of databases.

Data cube

The main component of these systems is a OLAP cube. A cube consists in combining data warehouse’s structures like facts and dimensions. Those are organized as schemas: star schema, snowflake schema and fact constellation. The merging of all the cubes creates a multidimensional data warehouse. Some more detailed information o these specifics data architectures on this article star schema and snowflake schema, which compares the distinct approaches.

System types

There are many types of OLAP systems, depending on it’s structure and specificity. The most common ones are: MOLAP, ROLAP and HOLAP.

The most important real world applications of these kind of systems are: business management and reporting, financial reporting, marketing, research, predictive analytics and another information related issues. These processes are growing faster on these days, making them absolutely critical in a world which, as we know, is becoming extremely dependent on data. In the next paragraph we will provide a real world example of what we have described before.

Real World Example: In a hospital there are 20 years of very complete patient information stored. Someone on the administration wants a detailed report of the most common diseases, success rate of treatment, internship days and a lot of relevant data. For this, we apply OLAP operations to our data warehouse with historical information, and through complex queries we get these results. Then they can be reported to the administration for further analysis.

What is OLTP?

Online Transaction Processing is a information system type that prioritizes transaction processing, dealing with operational data. This kind of computer systems are identified by a large number of transactions, making them the best to address online application. The main uses of this method are all kind of transactional systems like databases, commercial, hospital and banking applications.

In a simple way, these systems gather input information and manage it on a database in a large scale. Most of today’s applications are based on this interaction methodology, with implementations of centralized or decentralized systems.

OLTP database and operations

On the database level, these transactional systems focus their operation on multi-access, fast and effective querys to the database. The most used operations are INSERT, UPDATE and DELETE. This is logical since they are directly modifying the data, providing new information on new transactions. So, in these systems, data is frequently updated requiring an effective write and update operations support.

One special characteristic of those databases is the normalization of it’s data. This happens because data normalization provides a faster and more effective way to perform database writes. The main concern is the atomicity of the transactions. It is also very important to ensure that concurrent accesses don’t damage data and don’t allow degradation issues on the system performance.

Other systems

OLTP is not only about databases, but also other types of interaction mechanisms. All client-server architectures are based on these processes, taking benefit of the fast transaction and concurrent models. Decentralized systems are also online transaction processing, as all broker programs and web services are transaction oriented.

Real World Example: A banking transaction system is a classic example. There are many users executing operations into their accounts and the system must guarantee the completeness of the actions. In this case there are several concurrent transactions at the same time, becoming data coherence and efficient operations the main goal.

Comparing OLTP vs OLAP

OLTP, also known as Online Transaction Processing, and OLAP which stands for Online Analytical Processing, are two distinct kinds of information systems technologies.

Both are related to information databases, which provide the means and functionalities for these two types of processing.

Each one of the methods creates a different branch on data management system, with it’s own specialties and processes, although they complement themselves.

Since those are very distinct methods, comparing and describing the differences between them is an interesting task.

Basically, OLAP and OLTP are very different approaches to the use of databases, but not only. In one hand online analytical processing is more focused on data analysis and reporting. On the other hand online transaction processing target an operation optimized system, with a lot of data changes.

For someone learning about data sciences, related to IT methods, it is important to know the difference between these two subjects. This is the fundamental idea behind systems like business intelligence, data mining, data warehousing, data modelling, etl processes and big data.

Regarding the previous descriptions of the systems, let’s start comparing them feature by feature.

OLTP and OLAP differences

This infographic details every item of the comparison between OLTP and OLAP systems.

Focus

The first obvious difference is the focus of each. OLTP is best suited to update existing data, becoming the logical choice to operational systems which work with every day actions.

On opposite, OLAP provides great analysis processes for the stored information, making this kind of systems perfect for reporting purposes. They usually base their work in historical data, which is best to analyze, getting more accurate reports.

Schema

To achieve the goals of these two distinct kind of systems there are some architectures that suit best their needs. So, one focus on operational actions, and other boost reporting and analysis.

So OLTP database schemas are usually on a normalized form, providing a better performance for the necessary queries. On the other hand, OLAP databases have specific data warehouse organization schemas.

Queries

In OLTP, where the common work is operational, queries on the system should be much more simpler. In opposition in OLAP, queries are much more sophisticated and complex digging on data is performed.

Therefore, OLTP systems tend to be much faster than OLAP ones.

Changes

OLTP systems data is constantly changing, so the refresh rate of information is very high.

On the other hand, on OLAP systems, the changes are predefined. Usually there are processing jobs that gather and store large sets of data simultaneously. This actions seldom take a long time because of the quantity of information that is being exchanged and the process of normalizing this data.

Backup

Since data on OLTP systems is absolutely critical, it needs a complex backup system. Full backups of the data, combined with incremental backups are required to guarantee the safeguard of the information in catastrophic scenarios.

OLAP only needs a backup from time to time, since data is not critical and doesn’t keep the system running. In some systems this kind of backup is replaced by running the gathering information jobs on all data sources.

Space used

Space requirements on both sides is, obviously dependent on the quantity of information stored. However is safe to assume that historical data will need more storage space, since a lot more information is stored.

Therefor, OLAP systems tend to need significantly more space than OLTP ones.