operational systems vs. data warehousing
The fundamental difference between operational systems and data warehousing systems is that operational systems are designed to support transaction processing whereas data warehousing systems are designed to support online analytical processing (or OLAP, for short).
Based on this fundamental difference, data usage patterns associated with operational systems are significantly different than usage patterns associated with data warehousing systems. As a result, data warehousing systems are designed and optimized using methodologies that drastically differ from that of operational systems.
The table below summarizes many of the differences between operational systems and data warehousing systems.
a comparison of operational systems and data warehousing systems
|operational systems||data warehousing systems|
|Operational systems are generally designed to support high-volume transaction processing with minimal back-end reporting.||Data warehousing systems are generally designed to support high-volume analytical processing (i.e. OLAP) and subsequent, often elaborate report generation.|
|Operational systems are generally process-oriented or process-driven, meaning that they are focused on specific business processes or tasks. Example tasks include billing, registration, etc.||Data warehousing systems are generally subject-oriented, organized around business areas that the organization needs information about. Such subject areas are usually populated with data from one or more operational systems. As an example, revenue may be a subject area of a data warehouse that incorporates data from operational systems that contain student tuition data, alumni gift data, financial aid data, etc.|
|Operational systems are generally concerned with current data.||Data warehousing systems are generally concerned with historical data.|
|Data within operational systems are generally updated regularly according to need.||Data within a data warehouse is generally non-volatile, meaning that new data may be added regularly, but once loaded, the data is rarely changed, thus preserving an ever-growing history of information. In short, data within a data warehouse is generally read-only.|
|Operational systems are generally optimized to perform fast inserts and updates of relatively small volumes of data.||Data warehousing systems are generally optimized to perform fast retrievals of relatively large volumes of data.|
|Operational systems are generally application-specific, resulting in a multitude of partially or non-integrated systems and redundant data (e.g. billing data is not integrated with payroll data).||Data warehousing systems are generally integrated at a layer above the application layer, avoiding data redundancy problems.|
|Operational systems generally require a non-trivial level of computing skills amongst the end-user community.||Data warehousing systems generally appeal to an end-user community with a wide range of computing skills, from novice to expert users.|