About
One major difference between the types of system is that data warehouses are not usually in third normal form (3NF), a type of data normalization common in Online Transaction Processing (OLTP) environments.
Example of OLTP Software :
- an ERP (Entreprise Ressource Planning) Software as SAP,
- a trade website
Data Warehouse System is also known as (of used in) :
Articles Related
Differences in requirements
Data warehouses and OLTP systems have very different requirements. Here are some examples of differences between typical data warehouses and OLTP systems:
- Workload
Data warehouses are designed to accommodate ad hoc queries. You might not know the workload of your data warehouse in advance, so a data warehouse should be optimized to perform well for a wide variety of possible query operations. OLTP systems support only predefined operations. Your applications might be specifically tuned or designed to support only these operations.
- Data modifications
A data warehouse is updated on a regular basis by the ETL process (run every 15 minutes, nightly, weekly). The end users of a data warehouse do not directly update the data warehouse. In OLTP systems, end users routinely issue individual data modification statements to the database. The OLTP database is always up to date, and reflects the current state of each business transaction.
- Schema design
Data warehouses often use denormalized or partially denormalized schemas (such as a star schema) to optimize query performance by avoiding join and using techniques as star_transformation. OLTP systems often use fully normalized schemas to optimize update/insert/delete performance, and to guarantee data consistency.
- Typical operations
A typical data warehouse query scans thousands or millions of rows. For example, “Find the total sales for all customers last month.” A typical OLTP operation accesses only a handful of records. For example, “Retrieve the current order for this customer.”
- Historical data
Data warehouses usually store many months or years of data. This is to support historical analysis. OLTP systems usually store data from only a few weeks or months. The OLTP system stores only historical data as needed to successfully meet the requirements of the current transaction.
Access path
In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements with high selectivity, often are characterized by the use of index access paths.
Decision support systems, on the other hand, tend to use partitioned tables and perform full scans of the relevant partitions.