OLAP Versus OLTP

In this age of Information Technology, organizations have different kinds of Computer Systems in place to manage their day to day activities as well as to define their long term goals and strategies to maximize their profits, in the process reducing the Administrative and Operational overheads. Classifying broadly, there are two major types of systems: the Online Transaction Processing Systems (OLTP) and the Online Analytical Processing Systems (OLAP).

OLTP systems have more to do with the day-to-day activities of an organization viz. the Transactions that take place on a daily basis. As an example, we can think of the Online Ticketing system of a Theme Park where, customers visit a website of the Theme Park, and after browsing the various options for availability of tickets they purchase tickets for themselves. Now, a single purchase of tickets constitutes a Transaction and involves the following number of activities – getting the current stock and the rates of tickets, displaying the current stock and rates to the customer, reducing the stock of tickets based on the quantity bought by the customer; and then there is the financial part to the transaction of processing the payment. Now for a popular theme park like Disneyland or Universal Studios, there may be a million or more number of customers visiting their websites and an equal number doing purchases. An OLTP system’s job is to enable these kinds of Transactions, and a good one should be capable of handling a large number of transactions simultaneously, wherein, the response time of the website should not vary even with large number of customers accessing it simultaneously, and performing transactions. Think of it, if the first customer can complete his purchase in say, 5 minutes, and the ten thousandth or the millionth customer takes more than 30 minutes, how much frustrating would it be to these two customers? Wouldn’t they just abandon the thought of purchasing tickets online? Would this not result in a loss of revenue for the theme parks (many customers do not want to stand in line to buy tickets)? No wonder then, not just theme parks, even other organizations like those that trade in various kinds of products, Financial Companies, Banks have spent huge amounts of money in developing and maintaining an efficient Online Transaction Processing system.

In India, companies and organizations have caught up with the trend of going online and managing online Transactions just recently, a trend that had gained popularity world over much before. The world over, United States in particular, that always has the leading edge in technology, has taken to use the Data collected in an OLTP system in making informed decisions to formulate strategies that would attract more customers and maximize their profits. This brings in to the picture an Online Analytical Processing System (OLAP for short). The major and only concern of an OLAP system is handling complex Mathematical and Statistical formulas within a fraction of a second, and that too in large numbers. These calculations throw up results that would be used by Analysts and Decision Makers within an organization to analyze the results, and come up with suggestions to the Management in taking decisions that would help them in doing away with schemes and plans that are non-revenue generating, and at the same time investing in schemes that would increase profits and customer patronage. Consider our previous example of Theme Parks. Say, Disneyland has different schemes for different groups of customers based on various criteria like Age, the Region a customer belongs to, and various such factors. To analyze the sales of tickets to each such group and target specific groups for promotions, the Analysts and Decision Makers would need Aggregated and Summarized Data wherein, the Aggregations and Summary would be done at different levels in a hierarchy, say for example, the first level of aggregations would be Age, and on top of that, Region would be another level meaning, for a particular Age Group, say Teenagers, which Region fares better in terms of sales. This is just one set of information that may be useful. There may be a need for other such sets of Data. If we try to get this Data from an OLTP system, it would be a real test of our patience. The structure of OLTP systems do not support faster processing of data to generate Aggregations and Summaries, the reason being they are purely Relational in nature. For Analytical Data, we need a technology that would support calculations and retrieval of data at a blazing speed, and that’s where the OLAP tools come in.

The market today has a number of popular OLAP tools like Business Objects, Cognos, Hyperion and Microsoft SQL Server Analysis Services to name a few with rich features. The reason there are a number of tools catering to the OLAP market is because of the specific needs of the industry in needing a faster processing time for queries to process millions of rows and perform complex calculations. The Relational Database Systems available in the market do little justice to this requirement. With different vendors in the market for Relational Databases, there exists different RDBMS systems as well such as are Oracle, Microsoft SQL Server, Sybase, DB2, and Informix to name a few of the popular RDBMS systems. Of course, every vendor that supplies a Relational Database Management System implements its own set of technologies for storing the Relational Data and managing its Performance, but the major factor in the performance of OLTP systems is the Design of the Databases rather than the technology.



Source by Amit V Chaudhary