OLAP vs OLTP: what makes the difference

The following table summarized main differences between OLTP and OLAP

~	OLAP	OLTP
Application	Operational: ERP, CRM, legacy apps, …	Management Information System, Decision Support System
Typical users	Staff	Managers, Executives
Horizon		Weeks, Months
Refresh	Immediate	Periodic
Data model	Entity-relationship	Multi-dimensional
Schema		Normalized
Emphasis		Update

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
OLTP 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:

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).

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.