Data Warehousing – OLTP vs. OLAP



We can divide database systems into Transactional(OLTP) and analytical(OLAP).  In general we can say OLTP systems run the day to day business for an organization whereas OLAP system is useful to analyze the trends.

OLTP(Online Transaction Processing):-

It stands for Online Transaction Processing system. As the name suggests, this is a stack of application and database which primary support the transactional nature of an organization’s business. This might include, order creation, logging service request, user creation, lead creation, opportunity creation, recording interactions with end users etc., to name a few. These systems not only are the tools to record data into system, they are also highly effective in retrieval of small chunk of data. For the sake of explaining, let’s assume OLTP as any state run bank’s ATM. You can deposit (INSERT), withdraw(UPDATE) certain sum of money and view (SELECT) your account balance. If you have noticed, these actions in an ATM are extremely fast – this in turn means that the system supporting the ATM function is built for fast insert & updates. This drives all the necessary technical features of OLTP. Like comparatively low database size, highly normalized database design, lack of historical data, absence of indexes on tables etc.

 

OLAP(Online Analytical Processing):-

Stands for Online Analytical Processing. On the other hand, OLAP systems are purely built to support an organization’s decision support system. Organizations need large pile of data at their disposal before they take any decision which would improve the revenue over a period of time (for example). The analytical system again consist of suite of applications, database (read data warehouse) and data integration layer for integration data from different source system. The analytic application should have a scalable and robust engine which can handle large sums of data and calculate aggregations, generates various cutting edge charts, delivers scheduled reports to end users concurrently, provide flexibility to users to download reports and charts to PPT/PDF/Excel etc. The database supporting this also needs to stand up to the rigors of the analysis process that business users fire. This drives the features of the OLAP database such as – ability to store historical data, highly denormalized database design, presence of indexes on columns for faster data retrieval, building pre built aggregates for analytics application to consume, ability to refresh the data warehouse with incremental transactional data in a pre defined window etc. To draw parallels with the ATM example for the OLTP system, imagine a national manager of a sales team trying to generate reports which given an aggregated view of the quarterly national sales of a product. The report will have ability to slice the national sales data further down to region (east, west, north south), region to state, state to territories and to the individual sales representative. The entire user journey through the UI, fetching of data from database upon drilling down from a higher level sales report to the lower level and how the application and database work together to give the end user the data he/she wants – all are part of suite of OLAP tools.

 

 

OLTP System
Online Transaction Processing
(Operational System)

OLAP System
Online Analytical System
(Data Warehouse)

Source of data

Operational data; OLTPs are the original source of the data.

Consolidation data; OLAP data comes from the various OLTP Databases

Purpose of data

To control and run fundamental business tasks

To help with planning, problem solving, and decision support

What the data

Reveals a snapshot of ongoing business processes

Multi-dimensional views of various kinds of business activities

Inserts and Updates

Short and fast inserts and updates initiated by end users

Periodic long-running batch jobs refresh the data

Queries

Relatively standardized and simple queries Returning relatively few records

Often complex queries involving aggregations

Processing Speed

Typically very fast

Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes

Space Requirements

Can be relatively small if historical data is archived

Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP         

Database Design

Highly normalized with many tables

Typically de-normalized with fewer tables; use of star and/or snowflake schemas

Backup and Recovery

Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability

Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method

source: www.rainmakerworks.com