DATA WAREHOUSE – OLTP/OLAP

OLTP (On-line Transaction Processing)

By the mid-1970s, online transaction processing (OLTP) made even faster access to data possible, opening whole new vistas for business and processing. Shortly after the advent of massive OLTP systems, an innocuous program for “extract” processing began to appear.

Since OLTP data is on the operational side, it includes transactional data. Operations such as INSERT , UPDATE , DELETE are customized in OLTP. Details and instant data are kept in the OLTP database. These data are normalized to the 3NF model.

The size of the data in the OLTP system is quite large. It is not recommended to carry out business intelligence operations in OLTP systems because live instant data flows. If there is a database performance problem, we may have put the live application of the process in danger. Response time in the DSS environment is quite different from response time in the OLTP environment. In the OLTP environment, response time is almost always mission critical. The business starts to suffer immediately when response time turns bad in OLTP.

OLAP (On-line Analytical Processing)

OLAP (On-line Analytical Processing) is a system that contains information data. It can be customized more easily than OLTP systems. We transfer the source data in OLTP to OLAP systems according to a certain working system. For example, in OLTP, we clean dirty and noisy data and transfer it to the OLAP system. We can create data mining, artificial intelligence, analysis or decision support mechanisms in OLAP systems. In this way, we do not endanger the transactional environment.

OLAP systems may have a lower number of transactions than OLTP. Queries are more complex, but data can be more aggregated and summarized. In this case, it is convenient to use data mining techniques.

Historical data can be kept in OLAP systems. It will be very useful in OLAP systems when we want to do retrospective data analysis. E.g; To give an example from our own work, there is an OLAP cube in which the customer is archived as of today. In the coming days, months or years, we can identify the change in customers from the past to the present in the dashboard and present them to the decision makers.

As a result, as we will see in the example, we avoid doing such data operations on OLTP systems. OLAP systems are more useful, reliable and convenient.

We have a nice table of differences between OLTP and OLAP.

Source : www.rainmakerworks.com

If you want to ask me any question. Please send me mail ([email protected])