Difference between OLAP and OLTP in DBMS
Online Analytical Processing (OLAP): Online Analytical Processing consists of a type of software tools that are used for data analysis for business decisions. OLAP provides an environment to get insights from the database retrieved from multiple database systems at one time. Examples – Any type of Data warehouse system is an OLAP system. The uses of OLAP are as follows:
- Spotify analyzed songs by users to come up with a personalized homepage of their songs and playlist.
- Netflix movie recommendation system.
Online transaction processing (OLTP): Online transaction processing provides transaction-oriented applications in a 3-tier architecture. OLTP administers the day-to-day transactions of an organization.
Examples: Uses of OLTP are as follows:
- ATM center is an OLTP application.
- OLTP handles the ACID properties during data transactions via the application.
- It’s also used for Online banking, Online airline ticket booking, sending a text message, add a book to the shopping cart.
Comparisons of OLAP vs OLTP :
| Sr. No. | Category | OLAP (Online analytical processing) | OLTP (Online transaction processing) |
|---|---|---|---|
| 1. | Definition | It is well-known as an online database query management system. | It is well-known as an online database modifying system. |
| 2. | Data source | Consists of historical data from various Databases. In other words, different OLTP databases are used as data sources for OLAP. | Consists of only of operational current data. In other words, the original data source is OLTP and its transactions. |
| 3. | Method used | It makes use of a data warehouse. | It makes use of a standard database management system (DBMS). |
| 4. | Application | It is subject-oriented. Used for Data Mining, Analytics, Decisions making, etc. | It is application-oriented. Used for business tasks. |
| 5. | Normalized | In an OLAP database, tables are not normalized. | In an OLTP database, tables are normalized (3NF). |
| 6. | Usage of data | The data is used in planning, problem-solving, and decision-making. | The data is used to perform day-to-day fundamental operations. |
| 7. | Task | It reveals a snapshot of present business tasks. | It provides a multi-dimensional view of different business tasks. |
| 8. | Purpose | It serves the purpose to extract information for analysis and decision-making. | It serves the purpose to Insert, Update, and Delete information from the database. |
| 9. | Volume of data | A large amount of data is stored typically in TB, PB | The size of the data is relatively small as the historical data is archived. For ex MB, GB |
| 10. | Queries | Relatively slow as the amount of data involved is large. Queries may take hours. | Very Fast as the queries operate on 5% of the data. |
| 11. | Update | The OLAP database is not often updated. As a result, data integrity is unaffected. | The data integrity constraint must be maintained in an OLTP database. |
| 12. | Backup and Recovery | It only need backup from time to time as compared to OLTP. | Backup and recovery process is maintained rigorously |
| 13. | Processing time | The processing of complex queries can take a lengthy time. | It is comparatively fast in processing because of simple and straightforward queries. |
| 14. | Types of users | This data is generally managed by CEO, MD, GM. | This data is managed by clerks, managers. |
| 15. | Operations | Only read and rarely write operation. | Both read and write operations. |
| 16. | Updates | With lengthy, scheduled batch operations, data is refreshed on a regular basis. | The user initiates data updates, which are brief and quick. |
| 17. | Nature of audience | Process that is focused on the customer. | Process that is focused on the market. |
| 18. | Database Design | Design with a focus on the subject. | Design that is focused on the application. |
| 19. | Productivity | Improves the efficiency of business analysts. | Enhances the user’s productivity. |
.png)
