Difference between Operational Database and Data Warehouse
The Operational Database is the source of information for the data warehouse. It includes detailed information used to run the day to day operations of the business. The data frequently changes as updates are made and reflect the current value of the last transactions.
Operational Database Management Systems also called as OLTP (Online Transactions Processing Databases), are used to manage dynamic data in real-time.
Data Warehouse Systems serve users or knowledge workers in the purpose of data analysis and decision-making. Such systems can organize and present information in specific formats to accommodate the diverse needs of various users. These systems are called as Online-Analytical Processing (OLAP) Systems.
Data Warehouse and the OLTP database are both relational databases. However, the goals of both these databases are different.
| Operational Database | Data Warehouse |
|---|---|
| Operational systems are designed to support high-volume transaction processing. | Data warehousing systems are typically designed to support high-volume analytical processing (i.e., OLAP). |
| Operational systems are usually concerned with current data. | Data warehousing systems are usually concerned with historical data. |
| Data within operational systems are mainly updated regularly according to need. | Non-volatile, new data may be added regularly. Once Added rarely changed. |
| It is designed for real-time business dealing and processes. | It is designed for analysis of business measures by subject area, categories, and attributes. |
| It is optimized for a simple set of transactions, generally adding or retrieving a single row at a time per table. | It is optimized for extent loads and high, complex, unpredictable queries that access many rows per table. |
| It is optimized for validation of incoming information during transactions, uses validation data tables. | Loaded with consistent, valid information, requires no real-time validation. |
| It supports thousands of concurrent clients. | It supports a few concurrent clients relative to OLTP. |
| Operational systems are widely process-oriented. | Data warehousing systems are widely subject-oriented |
| Operational systems are usually optimized to perform fast inserts and updates of associatively small volumes of data. | Data warehousing systems are usually optimized to perform fast retrievals of relatively high volumes of data. |
| Data In | Data Out |
| Less Number of data accessed. | Large Number of data accessed. |
| Relational databases are created for on-line transactional Processing (OLTP) | Data Warehouse designed for on-line Analytical Processing (OLAP) |
Difference between OLTP and OLAP
OLTP System
OLTP System handle with operational data. Operational data are those data contained in the operation of a particular system. Example, ATM transactions and Bank transactions, etc.
OLAP System
OLAP handle with Historical Data or Archival Data. Historical data are those data that are achieved over a long period. For example, if we collect the last 10 years information about flight reservation, the data can give us much meaningful data such as the trends in the reservation. This may provide useful information like peak time of travel, what kind of people are traveling in various classes (Economy/Business) etc.
The major difference between an OLTP and OLAP system is the amount of data analyzed in a single transaction. Whereas an OLTP manage many concurrent customers and queries touching only an individual record or limited groups of files at a time. An OLAP system must have the capability to operate on millions of files to answer a single query.
| Feature | OLTP | OLAP |
|---|---|---|
| Characteristic | It is a system which is used to manage operational Data. | It is a system which is used to manage informational Data. |
| Users | Clerks, clients, and information technology professionals. | Knowledge workers, including managers, executives, and analysts. |
| System orientation | OLTP system is a customer-oriented, transaction, and query processing are done by clerks, clients, and information technology professionals. | OLAP system is market-oriented, knowledge workers including managers, do data analysts executive and analysts. |
| Data contents | OLTP system manages current data that too detailed and are used for decision making. | OLAP system manages a large amount of historical data, provides facilitates for summarization and aggregation, and stores and manages data at different levels of granularity. This information makes the data more comfortable to use in informed decision making. |
| Database Size | 100 MB-GB | 100 GB-TB |
| Database design | OLTP system usually uses an entity-relationship (ER) data model and application-oriented database design. | OLAP system typically uses either a star or snowflake model and subject-oriented database design. |
| View | OLTP system focuses primarily on the current data within an enterprise or department, without referring to historical information or data in different organizations. | OLAP system often spans multiple versions of a database schema, due to the evolutionary process of an organization. OLAP systems also deal with data that originates from various organizations, integrating information from many data stores. |
| Volume of data | Not very large | Because of their large volume, OLAP data are stored on multiple storage media. |
| Access patterns | The access patterns of an OLTP system subsist mainly of short, atomic transactions. Such a system requires concurrency control and recovery techniques. | Accesses to OLAP systems are mostly read-only methods because of these data warehouses stores historical data. |
| Access mode | Read/write | Mostly write |
| Insert and Updates | Short and fast inserts and updates proposed by end-users. | Periodic long-running batch jobs refresh the data. |
| Number of records accessed | Tens | Millions |
| Normalization | Fully Normalized | Partially Normalized |
| Processing Speed | Very Fast | It depends on the amount of files contained, batch data refresh, and complex query may take many hours, and query speed can be upgraded by creating indexes. |