Data warehousing – OLTP systems and Datawarehousing

We shall compare the ordinary OLTP systems & the Data warehouse systems w.rt the type of data that they handle and shall point out how both are related in a data warehouse application.

Operational Vs Informational data

Operational data is the data we use to run the business. This data is what is typically stored, retrieved and updated by the routine OLTP system. An OLTP system may be, for example, a Railway reservation system or a simple Accounting application. Operational data is stored in the OLTP database or in hierarchical or flat files in legacy systems.

Informational data is the data that is created from the wealth of the operational data that exists in the business from the operational OLTP systems. Informational data is what makes up the data warehouse. Informational data is usually stored in the data warehouse database and is stored in format that makes data analysis much easier. Analysis can be in the form of decision support queries, report generation, ad hoc queries, data mining and in depth statistical analysis.

The fundamental goals and requirements of the operational systems such as OLTP systems and the OLAP (Online analytical processing) systems (sach as the Data warehousing system) are thus different operational OLTP systems supports routine transaction tasks and low level decision making while the OLAP systems such as the Data warehousing system supports and integrates routine transaction processing tasks of OLTP systems as well as store & handle massive current and historical data and allow means for centralized data analysis & flexible-high level decision making via high performance queries. Below table summarizes difference between OLTP and data warehouse data –

Relating operational OLTP systems and Data warehousing

Creating the informational data, ie. the data warehouse data, from operational OLTP systems is a key part of the data warehousing solution. In a da warehousing system we will have several operational CLTPs that users interact w frequently and we use various techniques & tools to connect these OLTP databa to the data warehouse database and thus load the operational data from the OLTP the data warehouse and store the same as informational data. Thus we see that operational OLTP connected to the data warehouse is also an integral part o overall data warehousing solution.