Skip to main content

Data warehouse, data mart and need

Short answer

A data warehouse is a subject-oriented, integrated, time-variant, non-volatile collection supporting decisions; a data mart is a department-level subset focused on one subject. Organizations need a separate warehouse because OLTP systems are tuned for transactions, keep only current data, lack integration and history, and cannot handle heavy analytical queries. Applications include retail basket analysis, banking risk, and telecom churn analysis.

What is a data warehouse and data mart? Why do organizations need a warehouse separately though they have a transactional database system? Explain with its application.

Data warehouse

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data that supports management's decision making (W. H. Inmon). It stores historical data from many sources in one place for analysis.

Data mart

A data mart is a small, department-level subset of the warehouse (or a small standalone warehouse) that focuses on one subject, such as sales or finance. It is cheaper and faster to build, and gives its users faster queries.

Why a separate warehouse when a transactional database exists?

  1. Performance: OLTP databases are tuned for short transactions. Heavy analytical queries (large scans and aggregates) would slow down daily operations, and their locks would block updates.
  2. Different data: decision making needs historical data (years), while operational databases keep only current data.
  3. Integration: analysis needs data from many systems, with consistent names, formats and codes. Operational systems are separate and inconsistent.
  4. Data quality: the warehouse holds cleaned, validated data.
  5. Different structure: the warehouse uses a denormalised star schema for fast aggregation; OLTP uses normalised tables.
  6. Different functions: OLAP needs multidimensional views, which operational DBMSs do not provide.

Applications

  • Retail: market-basket analysis, stock planning, seasonal sales trends.
  • Banking: customer profitability, credit risk, fraud analysis.
  • Telecom: churn analysis, call-pattern and billing analysis.
  • Healthcare: patient history, disease trends and hospital resource planning.
  • Government: census, tax and e-governance reporting.

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Data Mining chapter 1 questions →