Transactional vs Analytical systems

Transactional vs Analytical systems

What Is A Transactional Database?

Transactional data is information captured from day-to-day business activities such as sales, discounts, payment methods, supplier purchase orders, customer support receipts, email confirmations, payment of employees’ salaries, etc. These activities are referred to as transactions and are stored in an OLTP (On-Line Transaction Processing) database that can be accessed by many users for fast, simple queries.

In essence, data is stored on disks as rows rather than columns, which is great when you need to retrieve information about one user, order, patient, or similar object at a time. Systems that perform transactional workloads can handle large volumes of CRUD operations (transactions) per second. Transactional databases also excel at ensuring data integrity, making them critical in business environments where a high level of integrity is required – banking being the canonical example.

While transactional databases aren’t specifically built for analytics, they’re often a great place to start though you will eventually run into limitations.

Transactional databases are usually user-oriented and are engineered to handle a large number of queries. In contrast, analytic systems are not used by end-users and, as such, handle considerably fewer requests. However, each query’s requirements are usually high since a significant number of records have to be scanned in a relatively short time.

What Is An Analytical Database?

Data analysis involves processing large amounts of information to glean insights from summary statistics. Analytical data comes into being from analyses or calculations run on transactional data and is used in managerial analysis and decision-making. Analytical workloads involve complex queries that start with aggregating, filtering and processing individual transactional data to produce strategic business insights.

An analytical database, also known as OLAP (On-Line Analytical Processing), integrates data from transactional sources and presents it in a format that allows for reporting and analytics. In other words, an OLAP provides a multi-dimensional view of data rather than a transactional-level view.

Analytical databases are critical when you need a strategic view of business data. They’re optimized for quick query response times and typically, more scalable than traditional databases.

The key distinctive features of analytical databases include:

Columnar data storage – This gives users the ability to search data through multiple attributes. It also allows for the storage of massive amounts of data.

Efficient data compression – Since data is stored in columns, analytic databases also excel in data compression. As a result, data takes up much less space, reducing disk seek time since data can be moved around much faster.

Distributed workloads – Data is stored in nodes across different parallel servers, enabling efficient processing of vast volumes of data.

Difference Between Analytical Databases and Transactional Databases?

Transactional Databases (OLTP)

Analytical Databases (OLAP)

Function

Manage and control critical business operations

Provide a consolidated view of business data for reporting and planning.

Source of data

Enterprise data is recorded in real-time as transactions take place

Data is collated from different transactional systems

Data presentation

Presents a day-to-day view of business operations

Presents a multi-dimensional view of business data

Query type

Simple, standardized queries based on row items

Large, complex queries that require aggregation of data from multiple OLTPs

Query form

Centered on CRUD (create, read, update, delete) commands

Centered in SELECT orders since data is already recorded in OLTP systems

Type of Users

Used by employees on the frontline like Point-of-Sale cashiers to record and view transactions

Used by knowledge workers such as data analysts to present strategic business decisions

Tractional DatabasesAnalytical Databases
Analyzes individual entries.Analyzeslarge batches of data.
Access to recent data - from last few hours/days.Access to older data - months/years.
Fast real-time access.Long-running jobs.
Read/write/updates data - schema on write.only reads data - optimized for read operations - Schema on read
Usually a single data source.Multiple data sources.