Compare and contrast oltp and olap systems give examples
Answers
Answered by
0
There are two major camps in the relational database development world: OLTP and OLAP. In this post, we consider the differences and similarities of these two systems.
First of all, both OLTP (on-line transactional processing) and OLAP (on-line analytical processing) are used in business applications, especially — although not exclusively — in data warehousing and analytics. Together, they form the two different sides of the analytics/warehousing coin: storing and manipulating the data on one hand and analyzing it on the other.
Entering the database world, it is important that you have a clear grasp of the different flavors of database usage. No matter which direction you choose to take, understanding the basic differences between OLAP and OLTP will serve you well. The building blocks are the same — SQL and database development. In this article I’ll explain what each type of system does and how each is used.
What Is OLTP?
On-line transactional processing (OLTP) is a mouthful to say, but the concept is not hard to grasp. OLTP systems are “classical” systems that process data transactions. They are all around you. In the bank, the ATM or the computer system used by the bank teller to record a transaction is an OLTP system, usually a database. If you text someone from your smartphone, you are working with another OLTP system. The cash register at your local supermarket runs off another OLTP system, and on it goes.
In short, most business applications are OLTP systems. It is hard to imagine a strata of computer engineering that doesn’t have some form of OLTP. In fact, a whole branch of computer science grew up around the need to handle transactions in an orderly manner.
Thus we come to the critical part of the OLTP acronym: the T, which stands for transactional. Transactions are hugely important, and keeping them organized is also hugely important.
What does it take to keep transactions organized? It means that database transactions have to be stable (or durable, not easily changed), isolated, consistent, and atomic. According to Wikipedia, atomicity is an “indivisible and irreducible series of database operations […] that either all occur, or nothing occurs”. In computer science, these are known as ACID transactions (atomic, consistent, isolated, durable). Explaining them in detail is outside the scope of this article, but you can easily learn more by Googling “ACID transactions”.
In simple words, this type of transaction ensures that operations performed by different users do not interfere with each other. For example, if a husband and wife each make a withdrawal from their joint bank account, atomic transactions make sure that they do not withdraw more than their account holds.
An OLTP system needs each of these properties to ensure that everything is working in an orderly manner. When you bank, you are confident that the money you deposit is credited to your account, not mixed up with your other transactions or, heaven forbid, lost.
Clearly, transactional processing is very important for our society as a whole. But what about OLAP?
What Is OLAP?
OLAP is short for on-line analytical processing. The key letter here is A for analytical, which also tells us what the OLAP system does. An OLAP system analyzes data effectively and efficiently.
Unlike OLTP, OLAP systems work with very large amounts of data. Preserving the accuracy and integrity of transactions is not their purpose; this is up to OLTP. OLAP is here to allow us to find trends, crunch numbers, and get the big picture. These systems have a smaller group of users than OLTP systems. For example, you will not interact with your bank’s OLAP system, since it is not concerned with recording your account transactions.
First of all, both OLTP (on-line transactional processing) and OLAP (on-line analytical processing) are used in business applications, especially — although not exclusively — in data warehousing and analytics. Together, they form the two different sides of the analytics/warehousing coin: storing and manipulating the data on one hand and analyzing it on the other.
Entering the database world, it is important that you have a clear grasp of the different flavors of database usage. No matter which direction you choose to take, understanding the basic differences between OLAP and OLTP will serve you well. The building blocks are the same — SQL and database development. In this article I’ll explain what each type of system does and how each is used.
What Is OLTP?
On-line transactional processing (OLTP) is a mouthful to say, but the concept is not hard to grasp. OLTP systems are “classical” systems that process data transactions. They are all around you. In the bank, the ATM or the computer system used by the bank teller to record a transaction is an OLTP system, usually a database. If you text someone from your smartphone, you are working with another OLTP system. The cash register at your local supermarket runs off another OLTP system, and on it goes.
In short, most business applications are OLTP systems. It is hard to imagine a strata of computer engineering that doesn’t have some form of OLTP. In fact, a whole branch of computer science grew up around the need to handle transactions in an orderly manner.
Thus we come to the critical part of the OLTP acronym: the T, which stands for transactional. Transactions are hugely important, and keeping them organized is also hugely important.
What does it take to keep transactions organized? It means that database transactions have to be stable (or durable, not easily changed), isolated, consistent, and atomic. According to Wikipedia, atomicity is an “indivisible and irreducible series of database operations […] that either all occur, or nothing occurs”. In computer science, these are known as ACID transactions (atomic, consistent, isolated, durable). Explaining them in detail is outside the scope of this article, but you can easily learn more by Googling “ACID transactions”.
In simple words, this type of transaction ensures that operations performed by different users do not interfere with each other. For example, if a husband and wife each make a withdrawal from their joint bank account, atomic transactions make sure that they do not withdraw more than their account holds.
An OLTP system needs each of these properties to ensure that everything is working in an orderly manner. When you bank, you are confident that the money you deposit is credited to your account, not mixed up with your other transactions or, heaven forbid, lost.
Clearly, transactional processing is very important for our society as a whole. But what about OLAP?
What Is OLAP?
OLAP is short for on-line analytical processing. The key letter here is A for analytical, which also tells us what the OLAP system does. An OLAP system analyzes data effectively and efficiently.
Unlike OLTP, OLAP systems work with very large amounts of data. Preserving the accuracy and integrity of transactions is not their purpose; this is up to OLTP. OLAP is here to allow us to find trends, crunch numbers, and get the big picture. These systems have a smaller group of users than OLTP systems. For example, you will not interact with your bank’s OLAP system, since it is not concerned with recording your account transactions.
Similar questions