OLTP Vs OLAP

Enterprise Systems and IT Application can be categories in two types OLTP and OLAP. In General Terms OLTP is day to day Record Keeping of any organization and OLAP is the Historical Data of Warehouse..  

OLTP (On-line Transaction Processing) is characterized by a large number of transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF)

OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations(DISTINCT,GROUP BY,SUM,COUNT ..). For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

Major differences between OLTP and OLAP system is below :-

	OLTP	OLAP
	(Operational System)	(Data Warehouse)
Time Scale	This stores current data	This stores History data for analysis
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method
Examples for OLTP Queries:	What is the Salary of Mr.John?	How is the profit changing over the years across different regions ?
Examples for OLTP Queries:	What is the address and email id of the person who is the head of maths department?	Is it financially viable continue the production unit at location X?
Homogeneity	Scattered among different databases or DBMS and using different value coding schemes	Centralized in data warehouse. Or in a collection of subject oriented data marts
Indexing	Optimizes update performance by minimizing the number of indexes	Optimizes adhoc queries by including lots of indexes
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Normalization	This is fully normalized	Possibly partially denormalized for performance reasons. As this is used for reporting
Organization	Data stored revolves around business functions	Data stored revolves around information topics
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Stored Values	Stores typically coded data	Stores descriptive data
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities

Reference :- Wikipedia, MSDN.

Thanks for reading.....

https://twitter.com/#!/SumnJha