Difference between operational systems and data warehouse in Tabular Form
Contents
Operational systems maintain records of daily business transactions whereas a Data Warehouse is a special database that serves as the integrated repository of company data, for reporting and decision support purpose
In an operational system users takes orders, sign up new customers, and log complaints. They almost always deal with one record at a time in an operational system. They repeatedly perform the same operational tasks over and over. On the other hand, the users of a data warehouse watch the wheels of the organization turn. They count the new orders and compare them with last week’s orders and ask why the new customers signed up and what the customers complained about. Users of a data warehouse almost never deal with one row at a time.
Typical relational databases are designed for on-line transactional processing (OLTP) and do not meet the requirements for effective on-line analytical processing (OLAP). As a result, data warehouses are designed differently than traditional relational databases.
Comparison Chart
| Operational Systems | Data Warehouse |
|---|---|
| Application-oriented | Subject oriented |
| Used to run business | Used to analyze business |
| Detailed data | Summarized and refined |
| Current up to date | Snapshot data |
| Isolated data | Integrated data |
| Repetitive access | Ad-hoc access |
| Clerical user | Knowledge user (manager) |
| Performance sensitive | Performance relaxed |
| Few records accessed at a time (tens) | Large volumes accessed at a time (million) |
| Read/update access | Mostly read (batch update) |
| No data redundancy | Redundancy present |
| Database size 100 MB-100 GB | Database size 100GB – few terabytes |
Operational Systems
- Operational systems are tuned for known transactions and workloads, while the workload is not known a priori in a data warehouse.
- Special data organization, access methods, and implementation methods are needed to support data warehouse queries (typically multidimensional queries)
- E.g., the average amount spent on phone calls between 9 AM-5 PM in Pune during the month of December.
Data Warehouse Defined
- The term Data Warehouse was defined by Bill Inmon in 1990, in the following way: “A warehouse is a subject-oriented, integrated; time-variant and non-volatile collection of data in support of management’s decision-making process”.
- Ralph Kimball provided a much simpler definition of a data warehouse i.e.” data warehouse is a copy of transaction data specifically structured for query and analysis”.
- This is a functional view of a data warehouse. Kimball did not address how the data warehouse is built like Inmondid, rather he focused on the functionality of a data warehouse.
Advantages of Data Warehousing
- Potential high returns on investment and delivers enhanced business intelligence: Implementation of a data warehouse requires a huge investment in lakh, of RS. But it helps the organization to take strategic decisions based on past historical data and the organization can improve the results of various processes like marketing segmentation, inventory management, and sales.
- Competitive advantage: As previously unknown and unavailable data is available in a data warehouse, decision-makers can access that data to make decisions to gain a competitive advantage.
- Saves Time: As the data from multiple sources is available in an integrated form, business users can access data from one place. There is no need to retrieve data from multiple sources.
- Better enterprise intelligence: It improves customer service and productivity.
- High-quality data: Data in a data warehouse is cleaned and transferred into the desired format. So data quality.