OLAP vs OLTP: what makes the difference

OLAP vs OLTP: what makes the difference

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

This post explores keydifferences between two technologies.

OLTPstands for On LineTransaction Processing and is a datamodeling approach typically used to facilitate and manage usual business applications. Most of applications you see and use areOLTP based.

OLAPstands for On LineAnalytic Processing and is an approach to answer multi-dimensional queries. OLAP was conceived forManagement Information Systems and Decision Support Systems but isstillwidely underused: every day I see too much people makingout business intelligence from OLTP data!

With the constant growth of dataanalysis and business intelligence applications (now even in smallbusiness) understanding OLAP nuances and benefits is a must if youwant provide valid and useful analytics to management.

The following table summarized maindifferences 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, ispossibly moved to some sort of backup store, maybe upon period closing. On the other side5-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 indexedand online for years, you would surely face compatibility problems.It is quite improbable that your current invoice fields andreferences are the same of 10 years ago!

But neither performance norcompatibility are the biggest concern under large horizon. Realproblem is business dynamics. Today business constantly change andthe traditional entity-relationship approach is too vulnerable tochanges. I will better explore this point in next post with apractical example.

Refresh

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

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

More time and more resources would alsoallow better indexing to address huge tables covering the extendedhorizon.

Data Model & Schema

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

 

product » price

invoice » amount

client » name

Such functions can be implementedthough classic tables, one row per instance, whereeach attribute ismapped to one column.

Now, if you listen to typical businessquestions 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 multiplearguments (left side):

Product category × Region » Grossmargin

Product × Warehouse » Inventory

Supplier × Time × Product » Returnrate

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

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

The solution is use an hybridapproach based sitting on conventional relational technology. Thismodel employs so calledstar-schema instead of traditionalnormalization.

Emphasis

OLPT emphasis is on update. Transactionlevel isolation assures that database is always in a consistentstate. This can imply in some overhead to coordinate concurrentupdates but is necessary even in small applications.

On the other side OLAP can be updatedby periodic (daily) processes that work in standalone mode thusconsistency can be assured through update process.

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

OLTP emphasis is on retrieval andit organizes data to return result of ad hoc inquiries in a reasonableamount of time.

Two worlds, two obstacles

So, in practice you need two differentdatabases, one for OLAP and another one for OLTP. The second one isusually called a Data Warehouse and is a must if you want to makeserious business intelligence.

But, if this is best solution why itisn't widely adopted? Why so many people are still trying to use BItools 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 tosnowflaking (I'll explain this term in next posts).

2. 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 isactually not so difficult, neither exclusively applicable tomulti-billion corporations or terabytes of data and, infuture posts,I pretend to show a pragmatic and agile approach.

For further detail on OLAP technology Isuggest to read: OlapSolutions - 2^nded. By Erik Tomsen, also available at Amazon.

原文地址：http://www.cbsolution.net/techniques/ontarget/olap_vs_oltp_what_makes

下图引自另外一个网页，点击图片跳转