Friday, July 9, 2010


Distinction Between OLTP And OLAP Databases

Another important distinction is that between online transaction processing (OLTP) and online analytical processing (OLAP) databases. The distinction is not as clear-cut as that the standard distinction between file-server and client-server. In fact, most databases will be used as both OLTP and OLAP products during their lifetime.

OLTP refers to a usage pattern involving rapid insertion, deletion, and updating of data. This is typical of many applications. For example, suppose you’re running a travel agency and have 20 agents all updating a database of customer trip information.

This would be a typical OLTP application. The ability to quickly locate and change data is of paramount importance to avoid the database becoming a bottleneck for the entire operation.

On the other hand, suppose you’re the manager of the travel agency. You might be interested in seeing summary information from many bookings. Perhaps there’s a pattern where women travel more to Greece and men more to Spain; knowing this could enable you to better target your advertising to appropriate periodicals. Such analysis, involving summaries of all or most of the data in a database, is the hallmark of OLAP applications.

It’s very difficult for a server to be efficient for both OLTP and OLAP applications.

The data structures that are appropriate for fast updating are suboptimal for aggregate querying. Microsoft solves this problem by shipping two servers together. The first, Microsoft SQL Server, is mainly an OLTP server. It can perform summary queries, but it’s not optimized for them. That’s the job of the second program, Microsoft SQL Server  Analysis Services. This second program ships with every copy of SQL Server and is designed to build efficient structures for OLAP applications to use.


About bench3 -

Haja Peer Mohamed H, Software Engineer by profession, Author, Founder and CEO of "bench3" you can connect with me on Twitter , Facebook and also onGoogle+

Subscribe to this Blog via Email :