If you are into databases and middleware and ever wodners what is the difference between OLTP and OLAP systems.

OLTP – On-Line Transactional Processing

• Operational data that is the ongoing transactional data related to business operations like production data, sales data for the online transactional processing systems.
• OLTP is the source of the data origin. Every data of the business is stored in the OLTP system.
• OLTP purpose of existence is to control and run the business tasks.
• Insert and Update queries are very fast on the OLTP database, it has to be fast to support the process of update to the database.
• The data retrieval would be done with smaller queries and expecting to return only few rows of data.
• Database design is highly normalized in nature
• Follows Entity Relationship diagram and uses data modeling.
• Involves RDBMS relational database management system.
• Tables form relations between each other.
• Typically follows a master – details table structure like Order Header, Order Detail or Customer Master, Customer Detail.
• Database can be relatively small due to the continuous and periodical backup process.
• The Data reveals a typical snapshot of the ongoing business activity.

OLAP – On-Line Analytical Processing

• Data warehouse data, the data is consolidated for a period of time to form history of data.
• OLAP is the database formed from the various data stores or discrete databases which are OLTP in nature.
• OLAP purpose of existence is to provide decision making information, build analysis, problem solving activities over the business data.
• In OLAP, long running periodic batch jobs refresh the data
• The data retrieval would involve complex queries involving complex and aggregated result sets.
• Database design is typically de normalized with limited tables.
• Follows Star or Snow flake schema and uses Dimension Modeling.
• Involves Relational Online Analytical Processing (ROLAP) or Multi Dimensional OLAP (MOLAP)or Hybrid of relational or multidimensional OLAP (HOLAP)
• Tables are dimensional in nature.
• A single fact and multiple dimensional tables exist in OLAP system. Ex: Sales Fact table with Customer Dimension, Sales Dimension, Location Dimension and Time Dimension.
• Database can be very large due to aggregation structures and historical data. Requires more hashing indexes than the OLTP system.
• The Data reveals multi dimensional views of various kinds of business activities.

Related posts:

1. Dimensional Modeling Dimensional Modeling is the data model design technique for data...
2. De Normalisation De Normalisation (or De Normalization) of data allows table to...
3. Data Warehouse Data Warehouse the name was given by Bill Inmon in...
4. Relational Database Relational Database, the data or information are gathered and arranged...
5. Data Mining Data Mining is the process of gathering the information from...