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
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.

Example

Two stores, two jobs: a bank's core system records each withdrawal instantly (operational). The warehouse holds five years of summarised data so managers can study branch-wise trends without slowing down the live system (informational).

Exam tip

Compare them on purpose, data age, updates, users and query type.

3

Topic 3

Data warehouse characteristics and OLAP operations

OLAP operations let users analyse a data cube from different angles: roll-up, drill-down, slice, dice and pivot. 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

Example

OLAP on sales data: roll up from city to state totals; drill down from year to quarters; slice for Q1 only; dice for Q1–Q2 and two cities; pivot to swap rows and columns.

Exam tip

Define each OLAP operation with one example.

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

Example

The four characteristics in a retail warehouse: organised around customers and products (subject-oriented); sales, inventory and loyalty data use one customer ID (integrated); five years of monthly snapshots (time-variant); loaded data is not edited by daily transactions (non-volatile).

Exam tip

Learn Inmon's definition word for word.

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.

Example

Levels of data: top management sees yearly totals (highly summarised); regional managers see monthly sales by region (lightly summarised); analysts can drill into individual transactions (current detail); older detail is archived.

Exam tip

The structure runs from current detail to lightly and highly summarised data, plus metadata.

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.

Example

Hidden costs: a company budgets for servers and software, but data cleaning and ETL development take more time and money than expected, along with training users and ongoing maintenance.

Exam tip

ETL and data quality work is often the largest cost.

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

Example

Two queries: 'Record a sale of ₹500 to customer 101' is OLTP — short, frequent, current data. 'Compare monthly sales by region over three years' is OLAP — complex, read-only, historical data.

Exam tip

OLTP is optimised for fast updates; OLAP for fast complex reads.

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).

Example

One cube, five operations: from sales by quarter, city and category, roll up cities to states; drill down Q1 into months; slice for 'Electronics'; dice for Q1–Q2, Ludhiana and Delhi; pivot to show categories as rows and quarters as columns.

Exam tip

Practise drawing a small cube and naming each operation.

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