jump to navigation

What is the difference b/w OLAP & OLTP, why do we keep warehouse on different server then of OLAP? April 24, 2007

Posted by Muhammad Habib in Dataware Housing.

OLTP databases provide real time accesses to its data which is being updated by other transactions. OLTP databases contrasts earlier databases that allowed updation in batch modes as all updations were provided in one go.(database Replication can be considered so).

In case of OLTP there is no such boundation and all updations can be done anytime and other users will automatically see your changes.

OLAP provides analytical processing capabilities on an OLTP database. This provides a real time analytical view of the database. Historically OLAP suffered since it would slow down OLTP database considerably. However with faster h/w and better algorithms, it’s now practical.

Data Warehouses contain historic & non operational data that is not required for day to day working of the operations. For example you purchased a pair of Shoes from a branded store 10 years ago. Chances that you will come back reclaiming your money are very less ;-).

Such data is transferred to special database known as Warehouse. If it would have being in the OLTP database then it would have consumed storage and WORSE will make transaction processing very very slow. Since best database algo’s are log(n) where n is the amount of data. As ‘n’ grows so is the time complexity.

Hence it makes sense to keep such historic data separate for analysis purposes.



1. Mario - November 8, 2007

Hi all!
great article.

2. DiamondAgel - December 10, 2007

Build Your Own Residual Income Business
Products to Make You Feel Great, a Strong Support Team, and a Revolutionary New, Lucrative Compensation Plan! Agel is a new company and is uniquely positioned to be the next giant in this area. The company has developed an entirely new category of products. Imagine being part of the next industry-changing innovation.

Video information http://www.biz.go-agel.biz/index.php?newlang=english&name=videoclips&op=CatView&cat=2
This video may change your life forever.

Click here to get more information http://www.biz.go-agel.biz/index.php?newlang=russian&newlang=english

3. sanjay - March 15, 2008


4. Kevin Wilson - September 13, 2009

I found a great book OLAP which provides great comparison of OLAP with data mining The Multidimensional Data Modeling Toolkit: Making your Business Intelligence Applications Smart with Oracle OLAP. You can find this book at http://www.learn-oracle-olap.com/

5. fraymond - March 16, 2010

Habib, I found this blog from the suggested link from my blog http://geekatwork.wordpress.com/2010/03/04/oltp-or-olap/. It’s a good blog and I’ll come back to visit you often.

However, I disagree on two thing you mentioned here.

1. OLAP does not provide a “real time” analytical view of the database. Only the OLTP database has a real time view. Data is loaded to OLAP databases from OLTP databases on a regular basis, so before the next ETL job that loads data, you will not see any changes made to the OLTP databases.

2. The purpose for OLAP database is for reporting only. In your shoe buying example, if the application even allow you to change a transaction from 10 years ago, it should keep the transaction in OLTP database for 10 years.

6. Mihir - December 8, 2010

Thanks , was helpful !!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: