Understanding Data Warehouse Terminology: DB, DW, ODS, OLTP, OLAP, BI, and Data Mining

Today I collected official explanations and many excellent blog posts to clarify common data‑warehouse terminology. Below are the key abbreviations and their meanings.

1. DB (DataBase) : the operational (OLTP) database that stores the latest state of data for production systems.

2. DW (Data Warehouse) : stores data at multiple time points, preserving historical states to support statistical analysis.

3. DM has two common meanings:

Data Mart: a localized DW built for a specific business application, focusing only on the data needed by that application.

Data Mining: the process of discovering useful, novel, and understandable patterns from large datasets (also known as Knowledge Discovery in Databases, KDD).

4. ODS (Operating Data Store) : the earliest DW model that mirrors source schemas and adds a date column to each table to capture daily changes, enabling historical analysis.

5. OLTP (On‑Line Transaction Processing) : traditional relational databases used for routine transactional workloads such as banking.

6. OLAP (On‑Line Analytical Processing) : the primary application of DW systems, supporting complex analytical queries for decision‑making.

7. BI (Business Intelligence) : after obtaining OLAP statistics and insights from DM, leaders can adjust production (e.g., product placement) based on the analysis.

Overall Data Center Architecture

The overall DW architecture synchronizes metadata from various systems to the ODS via ETL, then transforms ODS data into a thematic DW. A DM is built for a specific business domain, and decision‑makers view reports generated from the DM.

1. Relationship between Data Warehouse and Data Mining

Think of a DW as a mine and Data Mining as the extraction work inside it. Without rich, complete data, Mining cannot produce meaningful insights. DW aggregates data from multiple sources, stores it in a large, integrated relational database, and delivers the right data to the right people at the right time.

Data Mining is not a magical process; it requires clean, integrated data from the DW to discover useful patterns.

2. Integration Example: ODS to DW

Integration Example

3. Does OLAP Replace Data Mining?

OLAP tools support hypothesis verification, while Data Mining helps generate hypotheses. For example, a market analyst may hypothesize that diapers and baby formula are often bought together and use OLAP to test this; Data Mining, however, can uncover unexpected associations such as diapers and beer being purchased together, which OLAP cannot discover on its own.

Thus, Data Mining can find patterns beyond human intuition, complementing but not being replaceable by OLAP.

4. Relationship between Data Warehouse and Data Mart

A Data Warehouse serves the entire enterprise, while a Data Mart is a smaller, department‑level warehouse with fewer subjects and less historical data, serving localized management needs.

References: https://blog.csdn.net/u011878191/article/details/49130733 https://www.jianshu.com/p/72e395d8cb33 https://blog.csdn.net/xuxurui007/article/details/8374203