Editing Data warehouse (section)

=== Operational databases ===
Operational databases are optimized for the preservation of [[data integrity]] and speed of recording of business transactions through use of [[database normalization]] and an [[entity–relationship model]]. Operational system designers generally follow [[Codd's 12 rules]] of [[database normalization]] to ensure data integrity. Fully normalized database designs (that is, those satisfying all Codd rules) often result in information from a business transaction being stored in dozens to hundreds of tables. [[Relational database]]s are efficient at managing the relationships between these tables. The databases have very fast insert/update performance because only a small amount of data in those tables is affected by each transaction. To improve performance, older data are periodically purged.

Data warehouses are optimized for analytic access patterns, which usually involve selecting specific fields rather than all fields as is common in operational databases. Because of these differences in access, operational databases (loosely, OLTP) benefit from the use of a row-oriented database management system (DBMS), whereas analytics databases (loosely, OLAP) benefit from the use of a [[column-oriented DBMS]]. Operational systems maintain a snapshot of the business, while warehouses maintain historic data through ETL processes that periodically migrate data from the operational systems to the warehouse.

[[Online analytical processing]] (OLAP) is characterized by a low rate of transactions and complex queries that involve aggregations. Response time is an effective performance measure of OLAP systems. OLAP applications are widely used for [[Data Mining|data mining]]. OLAP databases store aggregated, historical data in multi-dimensional schemas (usually [[star schema]]s). OLAP systems typically have a data latency of a few hours, while data mart  latency is closer to one day. The OLAP approach is used to analyze multidimensional data from multiple sources and perspectives. The three basic operations in OLAP are roll-up (consolidation), drill-down, and slicing & dicing.

[[Online transaction processing]] (OLTP) is characterized by a large numbers of short online transactions (INSERT, UPDATE, DELETE). OLTP systems emphasize fast query processing and maintaining [[data integrity]] in multi-access environments. For OLTP systems, performance is the number of transactions per second. OLTP databases contain detailed and current data. The schema used to store transactional databases is the entity model (usually [[Third normal form|3NF]]).{{citation needed|date=November 2024}} Normalization is the norm for data modeling techniques in this system.

[[Predictive analytics]] is about [[pattern recognition|finding]] and quantifying hidden patterns in the data using complex mathematical models to prepare for different future outcomes, including demand for [[Product (economics)|products]], and make better decisions. By contrast, OLAP focuses on historical data analysis and is reactive. Predictive systems are also used for [[customer relationship management]] (CRM).