DW - OLTP vs OLAP

DW - OLTP vs OLAP

OLTP stands for On-line Transaction Processing. OLTP based systems (account, ticket booking, banking systems, money transfer system) are used to perform a large number of short transactions. Almost all of the database queries in OLTP system consist of commands insert, update, delete. Select queries are mainly designed to enable users to select data from different entities. The majority of the query is known in advance at the design stage of the system. Thus, critical for the OLTP system is the speed and reliability of the performance of short operations updating data.

We know that OLTP has also been used to refer to processing in which the system responds immediately to user requests. In OLTP databases, there are detailed and current data, and schema used to store transnational databases is the entity model.

OLAP stands for On-line Analytical Processing which was described by Edgar Codd in 1993. OLAP based systems are used for management decisions and these system known as Decision Support System (DSS). OLAP-applications operate with large amounts of data accumulated in the operational databases OLTP. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas.

As we know that OLAP is the most important component of business intelligence, which also encompasses relational database, report writing, and data mining. 

The collaboration of OLTP and OLAP systems

Following the workflow structure is capable to show the importance of each system based application and how are they link with each -

Major differences between OLTP and  OLAP

Contents	OLTP System	OLAP System
Source of data	Operational data; OLTP databases are meant to be used to do many small transactions, and usually serve as a "Single Source Of Truth"	Consolidation data; OLAP data comes from the various OLTP Databases which is used for analytics, data mining, less queries but they are usually bigger (they operate on more data).
Purpose of data	To control and run fundamental business tasks, facilitate and manage transaction-oriented applications	To help with planning, problem-solving, and decision support, report writing and data mining.
What the data	Reveals a snapshot of ongoing business processes, used to refer to processing in which the system responds immediately to user requests.	Multi-dimensional views of various kinds of business activities to help out the better decision making.
Inserts and Updates	Short and fast inserts and updates initiated by end users.	Periodic long-running batch jobs refresh the data by the help of ETLs processes.
Queries	Relatively standardized and simple queries Returning relatively few records which are not more than 2 years	Often complex queries involving aggregations which are based on the large volume of the data for more than 2 years.
Processing Speed	Typically very fast and always hit the main databases. Provides primitive and highly detailed data.	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. Provides summarized and consolidated data.
Space Requirements	Can be relatively small if historical data is archived. Mostly Database size is from 100 MB to 100 GB.	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP. Database size is from 100 GB to 100 TB
Database Design	Highly normalized with many tables and provides high performance.	Highly flexible. Typically de-normalized with fewer tables; use of star and/or snowflake schemas.
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