Online Analytical Processing Vs Online Transaction Processing
Foundations of Data Engineering
What are the main distinctions between these expressions, and how do you decide which one is appropriate for your circumstances?
In today’s data-driven world, winning companies are more likely to be those who use data to make better decisions and react quickly to shifting customer demands.
Online analytical processing (OLAP) and online transaction processing (OLTP) are the two types of data processing systems used in the data industry . The primary distinction between the two is that one uses data to uncover insightful knowledge while the other is just operational. Both systems can be used in useful ways to address data issues, though.
What is Online Analytical Processing (OLAP)?
People sought to extract analytics from their internet data even in the early days of computers, and they created tools to make that possible.
These systems advanced until they formed a separate computer category, much like everything else in computing.
In 1993, Ed Codd, who developed the first relational database, gave this group the name online analytical processing (OLAP). Although OLAP software had been in use for at least twenty years under the designation Decision Support Systems .
What are The Main Features of OLAP Systems?
Data warehouse in OLAP systems designed for reading, which allows you to quickly query, report on and analyze data.
A single query involving hundreds or millions of rows is processed by OLAP systems. The queries in OLAP systems, which are often written in the widely used query language known as SQL, may be fairly complex and incorporate data from several databases and tables.
A typical relational database stores its data in a row form. A single row for a transaction, for instance, would contain the fields product, price, customer. A columnar database, however, stores each of these fields in separate columns. Data warehouses used in these systems are column-oriented.
These are the main benefits and drawbacks of storing your data in columns:
- Columnar databases compress more efficiently than row-based relational databases. It appears that putting comparable bits of data together allows you to compress it significantly better than storing extremely diverse pieces of data.
- Columnar databases are more efficient to read.
- Compression and dense-packing in columnar databases free up space, which may then be utilized to sort and index data inside the columns. In other words, columnar databases offer better sorting and indexing performance as a result of having some extra space after severe compression.
- However, if there’s a drawback, it’s that update speed in a columnar database is really high (the query has to visit every column to change one “row”).
The term in the past was “big data,” and modern OLAP systems leverage distributed computing to scale to handle complicated analytical processing across massive datasets.
OLAP systems go beyond a data warehouse. They gather data from various sources, load it into the data warehouse using a data pipeline like Talend, Fivetran, Custom ingestions, transform and manipulate the data using transformation tools like dbt, and then create the deliverables — the reports and dashboards — using analytics tools like QuickSight, Qlik, Tableau, Looker, Microsoft Power BI, and numerous others.
Typically, a star schema or snowflake schema is used to store this historical, aggregated data for OLAP:
The star schema and snowflake schema are two ways to structure a data ware- house.
The Star Schema:
Has a centralized data repository, stored in a fact table. The schema splits the fact table into a series of denormalized dimension tables. The fact table contains aggregated data to be used for reporting purposes while the dimension table describes the stored data. Denormalized designs are less complex because the data is grouped. The fact table uses only one link to join to each dimension table. The star schema’s simpler design makes it much easier to write complex queries, the query complexity of the star schema is low, is optimized for querying large data sets. Simple DB Design. High level of data redundancy star schema uses more space. It takes less time for the execution of queries. In star schema, normalization is not used. It has less number of foreign keys.
The Snowflake Schema:
Is different because it normalizes the data. Normalization means efficiently organizing the data so that all data dependencies are defined, and each table contains minimal redundancy. Single dimension tables thus branch out into separate dimension tables. The snowflake schema uses less disk space and better preserves data integrity. The main disadvantage is the complexity of queries required to access data; each query must dig deep to get to the relevant data because there are multiple joins. The query complexity of the schema is high. Complex DB Design. Low level of data redundancy. Snowflake schema uses less space. It takes more time for the execution of queries, while in this, both normalization and denormalization are used. It has more number of foreign keys.
Data Providers of OLAP Systems:
Columnar data stores like Amazon Redshift, Snowflake, Azure Synapse Analytics, IBM Cognos, Oracle Database OLAP Option, SAS OLAP Server, and Google BigQuery are examples of OLAP systems. Distributed systems like Hadoop and Spark are other examples of OLAP systems.
Uses Cases of OLAP:
- Deezer analyzed songs by users to come up with a personalized homepage of their songs and playlist.
- Netflix movie recommendation system.
- The total sales of NVIDIA GEFORCE RTX 3090 in the UK for the past 12 months?
- How many graphic cards sold last two weeks?
- How many NVIDIA graphic cards sold in UK this year in comparison to the COVID year?
What is Online Transaction Processing (OLTP)?
Online transactional processing (OLTP) makes it possible for several users to execute numerous database transactions in real time, usually over the Internet.
Many of our daily transactions, including those involving ATMs, in-store sales, and hotel reservations, are powered by OLTP systems. OLTP may also power non-financial transactions, such as text messages and password changes.
What are The Main Features of OLTP Systems?
Accessibility: OLTP systems handle so many concurrent transactions, any data loss or outage may have a big impact and be very expensive. Every instant must have access to a full data backup. OLTP systems need ongoing incremental backups as well as frequent regular backups.
Requires very quick processing with millisecond-level reaction times: The total number of transactions that can be processed per second serves as a benchmark for the efficiency of an OLTP system.
Indexed data sets: these are employed for quick retrieval, searching, and querying.
Process a huge number of transactions that are rather basic, often insertions, updates, and removals of data, as well as straightforward data queries (for example, a balance check at an ATM).
OLTP systems rely on concurrency algorithms to make sure that no two users may update the same data at the same time and that all transactions are completed in the right sequence. This allows for multi-user access to the same data while maintaining data integrity. This stops users of online reservation services from making duplicate reservations for the same accommodation and safeguards joint account holders from unintentional overdrafts.
OLTP uses a fully normalized schema for database consistency.
OLTP systems use row-oriented databases. Row-oriented databases maintain all the information related to a record adjacent to one another in memory and organize data by record. The old method of data organization, row-oriented databases, nevertheless offer some important advantages for swiftly storing data. They are designed to effectively read and write rows.
Data Providers of OLTP Systems:
Row data stores like Amazon Aurora, MariaDB, PostgreSQL, Microsoft Sql Server, MySQL, Google Cloud SQL.
Uses Cases of OLTP:
- ATM center.
- Online banking.
- Adding items to cart in ecommerce website.
- Booking a flight ticket.
- Send a text message.
What is The Best System for Your Business?
OLAP generates and validates insights from data that accumulates over time. OLTP monitors current business activity in real time. Your goals will decide the best system for your scenario.
Do you need a consolidated business intelligence platform? OLAP may help you extract value from massive volumes of data.
Is it necessary for you to keep track of everyday transactions? An OLTP database is one that is designed to process a large number of transactions per second.
Traditional OLAP systems involve understanding of data modelling and, in many situations, coordination across many business divisions. OLTP systems, on the other hand, are business-specific, with any outage resulting in disrupted transactions and revenue and brand damage.
Thanks for reading. If you loved this article, feel free to hit that follow button so we can stay in touch.
