OLTP & OLAP in layman terms

I am not going to get into the technical details of OLTP or OLAP but just try to explain the concept in the words layman would understand.

Now, continuing with the example of Mz. X buying grocery from Big Bazaar which was mentioned in Business Intelligence 101, when receipt was printed for Mz. X, well, that wasn’t the only the task happened there, if we were to list down the tasks –

1. Invoice or receipt number was generated and stored in the database.
2. The items which Mz. X purchased, were inserted in the database against the invoice number.
3. If she had a loyalty card, points were automatically credited against the card number.
4. Probably, the stock for those 15 items was automatically reduced from the inventory.
5. If the stock became below the desired level, it was probably auto-ordered to the vendor or from the warehouse.
6. If so, there was an order number that would be generated automatically.
7. The ordered items would be tagged against the order number.
8. Etc etc.

And this might not be the exhaustive list of tasks those were triggered automatically at the back end, there might be number of other tasks as well.

These tasks are called transactions and system that is used for processing these is called OLTP or OnLine Transaction Processing.

Let’s assume that only the first 4 tasks from the above listed are common for all the customers those buy from Big Bazaar. Hence, the number of transactions –  

Invoice or receipt number was generated and stored in the database	1	Total = 18 Transactions
The items which Mz. X purchased, were inserted in the database against the invoice number	1
If she had a loyalty card, points were automatically credited against the card number	1	(With our assumption of 10 items per customer, the number of transactions become 13)
Probably, the stock for those 15 items was automatically reduced from the inventory	15

Continuing with assumptions from Business Intelligence101, there would be 13 transactions for each customer and total would be 500*13 = 6500 transactions daily for one store.

Thus at regional level 6500*10 = 65000 daily.

At national level, assuming just 4 regions East, West, North, South, 65000*4 = 2,60,000 daily.

And monthly: 2,60,000*30 = 78,00,000 transactions..!!

Now, that’s quite a number for a transactional system for being able to carry out analysis on!!

That’s why there is OLAP or OnLine Analytical Processing.

The data from transactional system (OLTP) is loaded into the data warehouse through ETL process, OLAP or OnLine Analytical Processing is used to analyze the data stored in the data warehouse. The example of the analysis carried out would be the product wise sales analysis, product wise revenue analysis, brand wise sales analysis, brand wise revenue analysis so on and so forth.

This completes an attempt to explain OLTP vs OLAP in layman terms.

Open for discussion in comments section..!!