OLAP vs OLTP: what makes the difference

OLPT and OLAP are complementing technologies. You can't live without OLTP: it runs your business day by day. So, using getting strategic information from OLTP is usually first “quick and dirty” approach, but can become limiting later.

This post explores key differences between two technologies.

OLTP stands for On Line Transaction Processing and is a data modeling approach typically used to facilitate and manage usual business applications. Most of applications you see and use are OLTP based.

OLAP stands for On Line Analytic Processing and is an approach to answer multi-dimensional queries. OLAP was conceived for Management Information Systems and Decision Support Systems but is still widely underused: every day I see too much people making out business intelligence from OLTP data!

With the constant growth of data analysis and business intelligence applications (now even in small business) understanding OLAP nuances and benefits is a must if you want provide valid and useful analytics to management.

The following table summarized main differences between OLPT and OLAP:



OLTP

OLAP

Application

Operational: ERP, CRM, legacy apps, ...

Management Information System, Decision Support System

Typical users

Staff

Managers, Executives

Horizon

Weeks, Months

Years

Refresh

Immediate

Periodic

Data model

Entity-relationship

Multi-dimensional

Schema

Normalized

Star

Emphasis

Update

Retrieval


Let's go straight to each key points.

 

Horizon

OLTP databases store “live” operational information. An invoice, for example, once paid, is possibly moved to some sort of backup store, maybe upon period closing. On the other side 5-10 strategic analysis are usual to identify trends. Extending life of operational data, would not be enough (in addition to possibly impacting performance).

Even keeping that data indexed and online for years, you would surely face compatibility problems. It is quite improbable that your current invoice fields and references are the same of 10 years ago!

But neither performance nor compatibility are the biggest concern under large horizon. Real problem is business dynamics. Today business constantly change and the traditional entity-relationship approach is too vulnerable to changes. I will better explore this point in next post with a practical example.


Refresh

OLPT requires instant update. When you cash some money from an ATM you balance shall be immediately updated. OLAP has not such requirement. Nobody needs instant information to make strategic business decision.

This allows OLAP data to be refreshed daily. This means extra timing and resources for cleansing and accruing data. If, for example, an invoice was canceled, we wouldn't like to see its value first inflating sales figures and later reverted.

More time and more resources would also allow better indexing to address huge tables covering the extended horizon.


Data Model & Schema

This is possibly the most evident difference between two approaches. OLTP perfectly fits traditional entity-relationship or object-oriented models. We usually refer to information as attributes related to entities, objects or classes, like product price, invoice amount or client name. Mapping can be with a simple, one argument function:

 

product » price

invoice » amount

client » name


Such functions can be implemented though classic tables, one row per instance, where each attribute is mapped to one column.

Now, if you listen to typical business questions you perceive a different requirement:

  • What is gross margin by product category in Europe and Asia?

  • What's our current inventory by product and warehouse?

  • Which was the evolution of return rate of different products acquired by different suppliers?


Are mapped as functions of multiple arguments (left side):

Product category × Region » Gross margin

Product × Warehouse » Inventory

Supplier × Time × Product » Return rate

Mapping attributes to columns do not work any more in this case: a multi-dimensional approach is required.

Tables do not naturally support multi-dimensional approach but relational databases are still the most widely used, proven and reliable approach today available. Reliability and performance is a must if we think in storing terabytes of data along years.

The solution is use an hybrid approach based sitting on conventional relational technology. This model employs so called star-schema instead of traditional normalization.


Emphasis

OLPT emphasis is on update. Transaction level isolation assures that database is always in a consistent state. This can imply in some overhead to coordinate concurrent updates but is necessary even in small applications.

On the other side OLAP can be updated by periodic (daily) processes that work in standalone mode thus consistency can be assured through update process.

But OLAP faces another challenge: retrieval. Suppose a telecom executive asking how much was billed last year in communications from USA to Japan. Can you figure how much time would it take to go ever each individual call to get the result?

OLTP emphasis is on retrieval and it organizes data to return result of ad hoc inquiries in a reasonable amount of time.


Two worlds, two obstacles

So, in practice you need two different databases, one for OLAP and another one for OLTP. The second one is usually called a Data Warehouse and is a must if you want to make serious business intelligence.

But, if this is best solution why it isn't widely adopted? Why so many people are still trying to use BI tools on traditional OLTP database? These are the most common reasons I have seen in practice:

  1. Doctrine. For years data modelers have been educated to normalize data and for years they have been told that data redundancy is first deadly sin. Habit is worst enemy of OLAP approach. Even when a star schema was officially adopted for BI applications, I have seen an irresistible attraction to snowflaking (I'll explain this term in next posts).

  1. Ingenuity. “Let's buy a good tool that will do the magic with little effort!”. This seems quite a better alternative to creating and feeding a second database. It doesn't work, still can be a valid solution if, as IT manager, you have just opened your second envelope. In next post I will illustrate with practical example what will probably go wrong.


Building a relational data warehouse is actually not so difficult, neither exclusively applicable to multi-billion corporations or terabytes of data and, in future posts, I pretend to show a pragmatic and agile approach.

For further detail on OLAP technology I suggest to read: Olap Solutions - 2nd ed. By Erik Tomsen, also available at Amazon.

 

Last modified on 2011-05-23 by Administrator