Synetic College Logo
Unit 1 of 4 · B.Sc IT Sem 5

Unit 1: Data warehouse fundamentals

Data Warehousing & Mining notes · PTU syllabus (BSIT504/BSBC501)

3 min read8 topics10 exam questions
On this page
  1. Unit summary
  2. The need for data warehousing
  3. Operational vs informational data stores
  4. Data warehouse characteristics and OLAP operations
  5. Characteristics of a data warehouse
  6. Role and structure of a data warehouse
  7. Cost of data warehousing
  8. OLAP vs OLTP
  9. OLAP operations with an example
  10. Key terms
  11. Quick revision
  12. Important questions

Unit summary

Operational databases run the business; a data warehouse helps understand it. This unit covers the need for data warehousing, operational versus informational data stores, the characteristics, role and structure of a data warehouse, its cost, OLAP versus OLTP, and OLAP operations.

After this unit you can

  • Explain why organisations need data warehouses
  • Distinguish operational and informational data stores
  • Describe the characteristics, role, structure and cost of a data warehouse
  • Compare OLAP and OLTP and perform OLAP operations

PTU syllabus topics

  • Need for data warehousing
  • operational vs informational data stores
  • data warehouse characteristics/role/structure
  • cost of warehousing
  • OLAP vs OLTP
  • OLAP operations
ComparisonOLTP vs OLAP
OLTP
OLAP

Purpose

Day-to-day transactions

Analysis and decisions

Data

Current, detailed

Historical, summarised

Queries

Short, simple, many

Complex, fewer

Design

Normalised

Star or snowflake schema

1

Topic 1

The need for data warehousing

  • Managers need integrated, historical, summarised data to analyse trends — but operational systems are separate, keep only current data, use different formats and slow down if heavy analytical queries run on them.
Key termsDrivers of data warehousing
Integrated view
Combine sales, finance, HR and CRM data
Historical analysis
Five or ten years of trends
Fast analytical queries
Without disturbing transaction systems
Data quality
Cleaned, consistent data
Competitive advantage
Better, faster decisions

Example

A retail chain with 200 stores wants to compare festive-season sales by region and category over five years; store billing databases keep only three months of data, in different formats.

2

Topic 2

Operational vs informational data stores

ComparisonOperational and informational data
Operational data store
Informational data store (warehouse)

Purpose

Run day-to-day operations

Support analysis and decisions

Data

Current, detailed, changing

Historical, summarised and detailed, stable

Access

Many short reads and writes

Few, large read-only queries

Users

Clerks, customers

Managers, analysts

Design

Normalised for updates

Denormalised (star schema) for queries

  • An operational data store (ODS) may also integrate current data from several systems for near-real-time operational reporting.
3

Topic 3

Data warehouse characteristics and OLAP operations

A data warehouse (Inmon) is a subject-oriented, integrated, time-variant and non-volatile collection of data supporting management decisions.

ClassificationOLAP operations on a data cube
OLAP

  • Roll-up

    Summarise: city → state

  • Drill-down

    More detail: year → month

  • Slice

    Fix one dimension: sales in 2026

  • Dice

    Select a sub-cube

  • Pivot

    Rotate the view

4

Topic 4

Characteristics of a data warehouse

FrameworkInmon's four characteristics

  • Subject-oriented

    Organised around subjects — customer, product, sales — not applications

  • Integrated

    Consistent names, codes and units across sources

  • Time-variant

    Every record linked to a time period; long history

  • Non-volatile

    Loaded and read; not updated in place

5

Topic 5

Role and structure of a data warehouse

HierarchyStructure of warehouse data
  1. Highly summarised data

    Yearly totals for top management

  2. Lightly summarised data

    Monthly sales by region

  3. Current detailed data

    Every sale of the recent period

  4. Older detailed data

    Archived on cheaper storage

  5. Metadata

    Data about data — sources, transformations, definitions

  • Role: single source of truth for reporting, dashboards, OLAP, data mining and planning; supports CRM, budgeting, fraud detection and performance management.
  • Data mart: a subset of the warehouse for one department (sales mart, finance mart) — dependent (fed from the warehouse) or independent.
6

Topic 6

Cost of data warehousing

Key termsCost components
Hardware and storage
Servers, disks or cloud capacity
Software
DBMS, ETL, OLAP and BI tools, licences
People
Architects, ETL developers, analysts, administrators
Data preparation
Cleaning and integrating sources — often the largest effort
Operations
Loading, backup, tuning, support
Hidden costs
Training, change management, data governance
  • Cloud data warehouses (Snowflake, BigQuery, Redshift, Azure Synapse) turn large upfront costs into pay-as-you-use charges.
7

Topic 7

OLAP vs OLTP

ComparisonOLTP and OLAP
OLTP
OLAP

Full form

Online transaction processing

Online analytical processing

Function

Day-to-day transactions

Analysis and decision support

Data

Current, detailed

Historical, consolidated

Queries

Simple, many, short; insert and update

Complex, few, long; read mostly

Database design

Normalised ER model

Star or snowflake schema; data cube

Size

Gigabytes

Terabytes and more

Example

ATM withdrawal, railway booking

Quarterly sales by region and product

8

Topic 8

OLAP operations with an example

  • Cube: sales by Time (quarter), Location (city) and Product (category).
ProcessOLAP operations on a sales cube
  1. 1Roll-up

    City → state: total sales of Punjab

  2. 2Drill-down

    Quarter → month: sales of each month of Q3

  3. 3Slice

    Time = Q4: a two-dimensional view

  4. 4Dice

    Q3–Q4, Ludhiana and Delhi, mobiles and laptops: a sub-cube

  5. 5Pivot

    Swap rows and columns to view products by city

  • OLAP types: MOLAP (multidimensional arrays; fast), ROLAP (relational tables; scalable), HOLAP (hybrid).

Key terms

Data warehouse
Subject-oriented, integrated, time-variant, non-volatile collection of data
Data mart
Departmental subset of a data warehouse
Metadata
Data describing the warehouse data
OLAP
Analytical processing of multidimensional data
Slice
Selecting one value of a dimension from a cube

Quick revision

  • Need for warehousing; operational vs informational data.
  • Four characteristics (Inmon).
  • Structure: summary levels, detail, metadata; data marts.
  • Cost components; cloud warehouses.
  • OLTP vs OLAP; roll-up, drill-down, slice, dice, pivot; MOLAP, ROLAP, HOLAP.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.Why can't operational databases serve analysis well?
  2. Q2.State the four characteristics of a data warehouse.
  3. Q3.What is a data mart?
  4. Q4.What is metadata?
  5. Q5.Distinguish OLTP and OLAP.
  6. Q6.Distinguish slice and dice.

Long-answer questions

  1. Q1.Explain the need for data warehousing and compare operational and informational data stores.
  2. Q2.Explain the characteristics, role and structure of a data warehouse.
  3. Q3.Compare OLTP and OLAP.
  4. Q4.Explain OLAP operations with an example.

Stuck on this unit?

Message SBS on WhatsApp for help with Data Warehousing & Mining, or to ask about studying B.Sc IT at Synetic.

WhatsApp us