What is OLTP? Difference between OLTP and OLAP.

What is OLTP?

Online Transaction Processing (OLTP) is a processing tool that manages the transaction data. For instance, a customer uses a credit card for various transactions and this data of transaction is maintained by the banks. The database has multiple fields for every transaction. Therefore, Organisations use OLTP to maintain the database for transaction data.

In other words, OLTP is used in transaction-oriented applications. Many organizations like banking, retail use OLTP software. The main advantage of OLTP is that it handles many transactions in a single time.

Advantages of OLTP

• It is a user-friendly application. Anyone can use this application.
• OLTP can quickly perform actions like reading, modifying, and deleting the data.
• It can process the query faster. Hence, it responds to user action quickly.
• OLTP generates the data.
• OLTP applications are built to perform business tasks.

Challenges of OLTP

• Multiple users can use the OLTP applications simultaneously. The data processed from different systems should not overlap with each other. Therefore, concurrency control is necessary.
• If the systems fail to operate new system should run the application at the same point without corrupting the data.
• OLTP is used only for the transaction of data. It cannot analyze the data. In other words, it does not have the ability to make decisions.

Characteristics of OLTP

• It transacts a small amount of data.
• OLTP indexes the data so that it can be accessed easily.
• Many users can use the OLTP application simultaneously.
• It performs quick actions on the queries.
• It only performs predefined functions on the data.
• OLTP stores the data in the database.
• It is user-friendly.

Example of OLTP

When we go to an ATM to withdraw money a simple transaction process is done by the bank. But in the case of a joint account if both the members try to withdraw the total amount of money at the same time. Then, the person who first completes the process will get the amount. Here OLTP makes sure that the amount inserted by the person should not be more than the total amount.

There are many examples of OLTP such as:

• Making an online transaction by using online banking services.
• Booking of Railway tickets online.
• Online shopping on e-commerce websites.
• Sending mails on mailing tools.

OLTP and the types of queries

OLTP supports databases queries. It is an online database management system. It can update, insert and delete the data with the help of database queries. But OLTP does not support all queries. It does not support queries that have a decision-making task.

Queries that OLTP can process.

• It can send the complete information of the product to the user.
• It can apply filters related to different categories. For example, If you want to see the product from a particular supplier, OLTP can process that query.
• OLTP can give the details of a particular customer.
• It can also run a query that can list the products according to their amount.

Queries that OLTP does not support.

• OLTP cannot decide the amount of discount it can offer for a particular item.
• It does not support the query of which product must be highlighted to the customers.

What is OLAP?

Online Analytical Processing (OLAP) is a software tool that allows organizations in analyzing the data from various databases at the same time. OLAP works on a hypercube system which means the databases are split into multiple cubes. The cube is a multidimensional database model.

OLAP cube collects and stores the data. Normally the data is stored in a two-dimensional database. It is stored in a database table in rows and columns format. But OLAP stores the data in multi-dimensional databases. It stores the data in an orderly manner.

OLAP Operations

The data is stored in a cube also known as a hypercube. To perform actions on these cubes or databases there are different operations. The 5 OLAP operations are as follows.

• Roll-up: It is completely the opposite of the Drill down operation. This operation creates the collection of data in OLAP cube. There are two different ways to perform this operation.
  • Moving up the concept-hierarchy. For instance, If the data is stored in a City dimension, then moving it to the Country dimension the details change.
  • Removing one of the existing dimensions.
• Drill Down: Drill Down operation is the opposite of Roll-up. It shows the detailed information of the cube. This can be done by applying two processes.
  • Moving the concept-hierarchy down. For instance, If the data is stored in a quarter dimension, then moving it to the month dimension the details of data change.
  • The second process is to add another new dimension to the cube.
  • For example, fewer details will appear if we access the data in a quarterly period. But if we access the data in a monthly period, it will show you more details of the data.
• Slice: In this operation, a sub-cube is created by selecting one dimension. It slices the dimensions to create sub cubes.
  • For instance, the Country dimension is Sliced with C1.
  • A new sub-cube is created.
• Dice: It is similar to the Slice operation. In this Operation, a sub-cube is created by selecting two or more dimensions. For instance, location “Sydney” and “New Jersey” is selected and diced.
• Pivot: In Pivot, we change the axes of the data to provide a different view of the database. It rotates the data axes.

Different Types of OLAP Systems

The OLAP is distributed into three main types.

ROLAP

The ROLAP systems work with Relational databases. It stores the data is in relational tables. ROLAP has the capability to analyze the data of the multidimensional database.

Advantages of ROLAP

• While working with multidimensional data analysis, ROLAP has a faster query performance and optimized access language. Therefore, data efficiency is higher while using ROLAP systems.
• ROLAP is a Scalable system as it can manage large amounts of data. It also works seamlessly even when the data is increasing.

Disadvantages of ROLAP

• ROLAP system requires higher software and infrastructures and also large manpower to handle these resources.
• ROLAP query performance is low as compared to MOLAP.

MOLAP

The MOLAP systems work with multidimensional databases. These systems use array-based storage engines. In other words, MOLAP uses the OLAP hypercube concept.

Advantages of MOLAP

• MOLAP can analyze a large amount of multidimensional data.
• The size of the data is smaller as compared to the data of the relational databases.
• It performs faster query performance.
• MOLAP can manage a large amount of less-defined data.
• Organizations perform Slicing and Dicing operations on these systems.

Disadvantages of MOLAP

• MOLAP manages limited data. Therefore, it is less scalable.
• This system is cannot contain detailed data. The cube cannot store a large amount of data.

HOLAP

HOLAP or Hybrid OLAP is the mixture of both MOLAP and ROLAP systems. This gives HOLAP the important features of both systems. It takes the scalable feature of ROLAP and the faster processing speed of MOLAP. Hybrid OLAP uses two databases. Organizations use a multidimensional database to store computed data. Whereas, they use a relational databases to store detailed large data.

Advantages of HOLAP

• It is convenient to use as it is compact and saves disk space.
• It uses OLAP hypercube concept. Hence, it can process all types of data faster.

Disadvantages of HOLAP

• It uses both the systems ROLAP and MOLAP. Therefore, the system becomes more complex.
• Both these systems have their own functions. Therefore, they might overlap with each other.

Difference Between OLAP and OLTP

Conclusion

Both OLAP and OLTP are online processing systems. OLTP processes transactional data in real-time. Whereas OLAP provides a faster speed of analysis on a large number of data. OLAP performs complex queries on a large amount of aggregated data. OLAP is a data analytical system that is also used for the smart decision-making process. OLTP is a transaction processing system that is used by online banking, e-commerce websites, etc.