If we examine how data analysis methods have evolved over the years, we note how from surveys carried out directly on operational sources, starting in the 1990s, we moved on to using a specifically created database: the data warehouse. With the data warehouse and multidimensional databases it is possible to analyze data not only at the highest level of detail, but also at different levels of aggregation; moreover, by performing drill-down or slicing and dicing operations, different perspectives of the data are obtained dynamically.

Within this field, there are two types of data processing systems: OLAP and OLTP.

In the next lines we’ll try to explain what they are, how to make the best use of both according to the need of the firm and why Snowflake represents a unique tool on the market to deal with processing systems.

OLAP

The acronym OLAP stands for On-Line Analytical Processing and identifies all those software procedures aimed at the interactive and rapid analysis of information. It is therefore a system for analysis-oriented databases, which allows to quickly extrapolate insights from masses of aggregated data.

OLAP is also the technological component that ideally lies between the Data Warehouse and the reporting and visualization tools. It is used by organizations to analyze sales results and marketing campaigns, to evaluate cost trends, to organize surveys and other operations essential to the daily activities of a company. It is therefore a Business Intelligence tool that focus on how to support strategic business decisions starting from information extracted from large databases.

Consequently, OLAP system are used typically by figures like managers, the decision-makers and analysts: the same figures who normally use reporting and visualization tools.

OLTP

The acronym OLTP can be translated instead as On-Line Transaction Processing. This system includes a category of software used to manage applications that deal with transactions: its use is therefore particularly widespread in financial transactions, in corporate CRMs, in ERP management and in retail operations.

In practical terms, OLTP systems are the basis of activities that are carried out on a daily basis, such as online ticket reservations, home banking channels, withdrawals or deposits at a traditional ATM, but also the purchase of products or services on common digital shopping platforms. The role of OLTP is either to record insertions, to update or to eliminate data during a transaction through simple and short queries, characterized by archiving and lean processing.

Typical OLTP user types are IT managers and database specialists.
In a sense, therefore, OLTP represents a source of operational data that could later feed into OLAP for analytical purposes.

Comparison

So what are the main differences between these two systems?

The first and most important is that, as we have indicated, OLAP is a database oriented towards data recovery and analysis while in OLTP it is aimed at online transactions.

Similarly, if online transactional data is a source of information for OLTP, the OLTP database can itself become a source of data for OLAP. Basically, therefore, OLAP was created to perform analyses while OLTP is used to perform processing and to guarantee the success of operations.

Frequency and length of transactions are also very different for these two systems: long and infrequent for OLAP, very fast and frequent for OLTP; consequently, queries are generally more complex for the former and simpler for the latter.

It should also be remembered that the tables in the OLTP database must be normalized to third normal form while the tables in the OLAP database do not have this requirement, and are instead often “de-normalized”.

The risk of damage to data integrity also substantially differentiates these two tools: the frequent database transactions that are typical of OLTP increase the risk of failure during the transaction itself (therefore the possibility of damage to data integrity and the need of introducing a recovery mechanism), while the problem almost does not concern OLAP, in which the transaction is by its nature less frequent, being an analysis-oriented system.

Finally, and this is the most noteworthy difference, they differ in the design: OLTP is designed for the control and execution of business operations and processes crucial to the performance of the activity, while OLAP was born with the aim of supporting the planning and decision-making.

Conclusions

In conclusion, OLTP is a system whose focus is placed on operational efficiency and transaction integrity while OLAP is a system aimed at maximizing the speed of data extraction for analytical purposes in the context of decision-making.

It goes without saying that these two tools have very different functions and objectives, and that the use of one and the other has extremely different requirements and purposes. Many could also say that they are complementary systems that act at different stages of data-handling: one to correctly store data and transactions, the other to later consult them quickly.

Although their use should take place in synergy in order to truly help firms, currently the only cloud data warehousing solution that supports both OLTP and OLAP workloads in a single system is Snowflake, which makes it a real special tool for a holistic approach on data!

That’s it for this small panoramic on OLAP and OLTP systems.

Have questions about data? Be sure to check our blog.