IB COMPUTER SCIENCE | A3.4.2 Data Warehouses (HL ONLY)

Lesson objective

Explain how subject-oriented, integrated, time-variant, non-volatile and append-oriented data with query-focused design supports data management and business intelligence.

Learn

A3.4.2 | DATA WAREHOUSES AND BUSINESS INTELLIGENCE (HL ONLY)

01 | THE ORGANISATION HAS DATA, BUT NOT YET ONE ANSWER

A robotics shop sells through a website and a physical counter. The website reports Robot kit, while the counter reports ROBOT_KIT. Their dates use different formats. Managers ask: “Which product categories sold most, and how did sales change from September to October?”

Adding two unexamined exports can create misleading totals. The same item may appear under different labels, a repeated export may duplicate a sale, and a date may be interpreted incorrectly.

A data warehouse is a data environment designed to bring together organised, historical data for analysis and reporting. Its main purpose is to support understanding and decisions rather than process each live checkout.

This HL lesson explains how append-oriented loading, subject orientation, integration, time variation, non-volatility and query-focused design contribute to that purpose. For explain, connect each feature to the useful consequence it produces.

02 | BUSINESS INTELLIGENCE CONNECTS DATA TO DECISIONS

Business intelligence (BI) includes the reporting, analysis and presentation used to understand organisational performance and inform decisions. A dashboard is one interface; the warehouse supplies prepared data that can support it.

The shop might use sales trends to investigate stock needs, compare channels or plan a promotion. A warehouse provides consistent historical evidence so these questions do not require rebuilding a new spreadsheet from unrelated sources every time.

The warehouse does not make a decision automatically or prove the cause of a trend. A rise in sales might reflect a promotion, seasonality, price changes or incomplete reporting. Context and interpretation remain essential.

The objective is trustworthy, usable analytical information, not merely collecting the largest possible amount of data.

03 | OPERATIONAL WORK AND ANALYTICAL WORK DIFFER

Aspect Operational / OLTP system Warehouse-oriented analytical work
Typical question Can this customer place an order now? How have category sales changed over a year?
Typical operation Short transaction updating a small set of records. Read and aggregate many historical records.
Data focus Correct current operational state plus required history. Integrated history across relevant subjects and sources.
Design priority Reliable transactions and responsive concurrent operations. Efficient scans, joins, grouping and reporting.

OLTP means online transaction processing. It supports work such as recording orders or updating stock. A warehouse usually receives data from such systems rather than replacing them.

This is a difference in emphasis, not a rule that operational databases contain no history or cannot run reports. A separate analytical workload can reduce competition with live transactions while using a design better suited to broad queries.

The data pipeline and extra platform also have costs. For a small reporting need, a separate warehouse may be unnecessary; its value depends on integration, history and workload requirements.

04 | THE PIPELINE FROM SOURCES TO REPORTS

Stage What happens Why it matters
Source systems Orders, counter sales and other operational records are produced. They capture the original events.
Staging / preparation Data is collected, checked, mapped and prepared. Problems can be identified before trusted reporting.
Warehouse Integrated historical facts and context are loaded. Analytical queries can use a shared organised dataset.
BI tools Queries, summaries and dashboards present results. Users can investigate patterns and support decisions.

A common process is ETL: extract data from sources, transform it into the agreed form, then load it. ELT loads data into the platform before applying some transformations. The order and architecture vary, but quality and meaning still need attention.

A nightly batch is one option; some systems ingest much more frequently. The report’s freshness is limited by extraction, processing and loading delays, not simply by whether the warehouse runs in the cloud.

05 | SUBJECT-ORIENTED: ORGANISE AROUND THE QUESTION

Subject-oriented means organising warehouse data around major analytical subjects, such as sales, customers, inventory or attendance. The shop’s sales subject can include both web and counter transactions.

The analytical structure is not just a copy of each source application’s screens. A manager investigating sales needs a coherent view of that subject across channels and over time.

This helps users ask related questions consistently: sales by product, category, channel and month can draw from the same defined sales facts.

Subject-oriented does not mean one warehouse can contain only one subject. An enterprise warehouse can cover several related subjects, while a data mart focuses on a particular department or analytical area.

06 | INTEGRATED: MAKE DIFFERENT SOURCES COMPARABLE

Source difference Preparation rule Result
Robot kit versus ROBOT_KIT Use an approved product mapping. Both refer to Robot kit in Robotics.
ISO dates versus day/month/year Parse using each source’s declared format. Dates become comparable calendar values.
Different column labels Map source fields to agreed warehouse fields. Quantity and units share one defined meaning.
Repeated source line Use a source-system plus line-ID identity. A repeated extract need not double-count a sale.

Integrated means resolving relevant differences so data can be analysed together meaningfully. Copying files into one folder is not enough if their product names, units and definitions remain incompatible.

Our example uses CHF throughout and defines sales value as units multiplied by unit price, excluding tax, shipping, discounts and refunds. A real warehouse must explicitly reconcile such business rules; currency conversion needs a documented rate and date policy where applicable.

Identifiers also need care. W101 in the Web source is identified with its source system; another source could legitimately reuse the same local code. Joining or deduplicating on an ambiguous local ID can merge unrelated events.

07 | CLEANING IS NOT GUESSING

A repeated Web W101 row should not become a second sale when the same extract is replayed. The pipeline can recognise its already processed source identity. But if the source sends a correction, silently ignoring it forever would also be wrong: corrections need a separate policy.

The date 31/02/2026 is invalid. A responsible pipeline flags or quarantines it for investigation rather than inventing a convenient valid date. Missing or incompatible values also need explicit treatment.

Data quality concerns completeness, validity, consistency and related fitness for the intended use. A warehouse can centralise quality checks but cannot recover unknown facts just by storing them.

Keep lineage: information about where a warehouse value came from and how it was transformed. That makes unexpected figures easier to explain and audit.

08 | TIME-VARIANT: KEEP THE TIME CONTEXT

Time-variant means the warehouse supports analysis across time, with historical context such as event dates, periods, snapshots or effective dates. It lets the shop compare September and October instead of seeing only a current total.

A live product record may show today’s category or price. A historical report may need the category or price that applied when a sale occurred. Warehouse design must decide which interpretation a report needs.

Different timestamps mean different things. A sale can occur on 30 September, reach the warehouse on 1 October and be corrected on 3 October. Event time, load time and correction history are not interchangeable.

Time-variant does not mean old values should be overwritten whenever something changes. Its purpose is to retain enough historical meaning to compare periods and understand changes.

09 | APPEND-ONLY: ADD EVENTS WITHOUT ERASING THEIR STORY

In an append-only approach, new events or versions are added rather than routinely replacing previously recorded history. September’s sales remain available when October’s rows arrive.

If a completed sale later has a refund, an event-history design can add a linked refund or adjustment event instead of making the original sale appear never to have happened. Reports then apply the agreed rule for net sales.

This supports traceability and historical comparison. It also means the loading process must avoid accidental duplicate appends when the same extract is replayed.

Append-only is a design approach, not a claim that every warehouse table in every system can never be updated or deleted. Controlled corrections, dimension maintenance, retention and legal requirements may require changes. Preserve intended history and document those operations.

10 | NON-VOLATILE: A STABLE ANALYTICAL RECORD

Non-volatile describes the relative stability of loaded warehouse history. End users normally read it for analysis rather than repeatedly editing old transaction rows as part of day-to-day operations.

A report about September should not silently change because someone overwrites a historical value with an unrelated current value. Controlled loads and corrections help preserve the reliability and reproducibility of analysis.

Non-volatile is not the same term as non-volatile hardware memory here. It does not mean a warehouse cannot load new data, cannot fail or never needs maintenance.

Append-only describes a way of recording new information. Non-volatility describes the stability of the historical analytical record. They support one another but are not identical definitions.

11 | WORK THROUGH THE SHOP’S SOURCE DATA

Source Line ID Source date Product label Units Unit price CHF
Web W101 2026-09-02 Robot kit 2 50
Shop T501 03/09/2026 ROBOT_KIT 1 50
Web W102 2026-10-04 Safety glasses 3 8
Shop T502 05/10/2026 ROBOT_KIT 4 50
Web W101 2026-09-02 Robot kit 2 50
Shop T503 31/02/2026 ROBOT_KIT 1 50

The Web source uses YYYY-MM-DD. The Shop source explicitly uses DD/MM/YYYY. There are six input rows: four distinct valid sales lines, one replay of Web W101 and one invalid calendar date.

The four valid unique sales contribute 100, 50, 24 and 200 CHF, totalling 374 CHF. Counting the duplicate would incorrectly increase that total to 474 CHF. Including the invalid row would also be unjustified.

The pipeline should preserve the rejected row for investigation and make the omission visible in data-quality reporting. A correct total over accepted records is not proof that all real-world sales have arrived.

12 | STEP FROM RAW EXPORTS TO A REPORT

Move through the stages to see the mapping, accepted rows, rejected/repeated records and the loaded sales facts. Then replay the same load: the model uses source identities to avoid inserting the same event twice.

This activity uses a fixed illustrative dataset in JavaScript, not a production ETL tool or live warehouse. Replays are unchanged extracts; handling changed versions of the same source event would require the correction policy described above.

WAREHOUSE FACTS

CATEGORY REPORT

13 | DEFINE WHAT ONE FACT ROW MEANS

CREATE TABLE FactSales (
    SourceSystem VARCHAR(10) NOT NULL,
    SourceLineID VARCHAR(20) NOT NULL,
    SaleDate DATE NOT NULL,
    ProductName VARCHAR(60) NOT NULL,
    Category VARCHAR(30) NOT NULL,
    Units INTEGER NOT NULL,
    SalesValueCHF DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (SourceSystem, SourceLineID)
);
SourceSystem SourceLineID SaleDate ProductName Category Units SalesValueCHF
Web W101 2026-09-02 Robot kit Robotics 2 100
Shop T501 2026-09-03 Robot kit Robotics 1 50
Web W102 2026-10-04 Safety glasses Safety 3 24
Shop T502 2026-10-05 Robot kit Robotics 4 200

A fact records a measurable event or observation. Here the grain is one source sales line, not one whole order and not one customer. Units and SalesValueCHF are its numeric measures.

A dimension provides descriptive context such as product, channel or date. A common dimensional design places fact measures in a fact table linked to dimension tables; a star schema is one familiar form.

The teaching table keeps descriptive values directly beside the measures so the example is easy to read. It is not a complete production star schema. A real design would specify keys, history rules, data quality and refresh requirements.

Grain matters when calculating. An order with three lines can produce three fact rows, so COUNT(*) over those rows counts lines, not necessarily distinct orders or customers.

14 | QUERY-OPTIMISED: DESIGN FOR LARGE ANALYSIS

Data optimised for query performance is organised so the expected analytical workload can scan, join, group and summarise efficiently. Warehouses often read many rows but only a subset of columns.

Possible techniques include suitable dimensional schemas, partitioning by useful ranges, column-oriented storage, compression, indexes and precomputed summaries. Not every warehouse uses every technique or the same physical layout.

For example, date partitioning may allow an October query to avoid irrelevant periods where the system can prune them. A stored category/month summary can avoid recomputing a large grouping on every dashboard request, but it has maintenance and freshness costs.

Partly denormalised dimensions can simplify reporting joins at the cost of repeated descriptive values. Query optimisation does not mean the data needs no integrity rules or that every possible query becomes fast automatically.

15 | WRITE A REPORT OVER THE INTEGRATED FACTS

SELECT Category, SUM(Units) AS UnitsSold,
       SUM(SalesValueCHF) AS SalesValueCHF
FROM FactSales
GROUP BY Category
ORDER BY SalesValueCHF DESC, Category ASC;
Category UnitsSold SalesValueCHF
Robotics 7 350
Safety 3 24

Robot kit and ROBOT_KIT have been mapped consistently, so the Robotics total includes both channels. The report uses the agreed measure definition rather than adding incompatible source columns.

Without integration, separate labels could split the category or a duplicate extract could inflate it. Without a defined grain, the row count could be mistaken for a product or customer count.

This is the link from management to BI: prepared facts and common definitions make a query result easier to interpret and compare across reports. They do not remove the need to understand what the measure excludes.

16 | USE HISTORY TO COMPARE PERIODS

SELECT SUM(SalesValueCHF) AS OctoberSalesValueCHF
FROM FactSales
WHERE SaleDate >= '2026-10-01'
  AND SaleDate < '2026-11-01';
Period Accepted sales value CHF
September 2026 150
October 2026 224

The accepted October sales value is 224 CHF, compared with 150 in September. The difference is 74 CHF, or approximately 49.33% relative to September. The event date determines which period each row belongs to.

The observation suggests questions for investigation, not a proven forecast. This small teaching dataset contains only accepted example lines, so it cannot establish a general business trend or the cause of growth.

If the October source has not finished loading, comparing it with a complete September can be misleading. Report the as-of/load context and compare appropriate periods, including seasonality and missing records where relevant.

17 | STORAGE EFFICIENCY DOES NOT MEAN KEEP NOTHING

Historical data grows as events and versions accumulate. Efficient storage involves choosing an appropriate grain, representation, compression, partitioning and retention policy while preserving the detail the analysis needs.

Aggregates can make common reports quicker and smaller to read, but retaining only monthly totals prevents later investigation of individual events unless that detail is stored elsewhere. There is a trade-off between summary convenience and analytical flexibility.

A warehouse is not simply a backup. A backup supports restoring a system; the warehouse transforms and organises selected data for analysis. It also differs from an unrestricted raw-data repository: a trusted reporting layer needs defined structures and meanings.

Cloud hosting can support a warehouse, but “cloud” alone does not provide integration, history or a reliable metric definition. Those are design and governance choices.

18 | LIMITATIONS AND RESPONSIBILITIES

Warehousing needs pipelines, storage, monitoring and ownership of shared definitions. A failed load, a changed source format or an incorrect mapping can make a polished dashboard wrong.

Users need appropriate access to sensitive data. Historical retention increases the need for purpose, access and deletion policies; “append-only” is not an excuse to retain everything without control.

Freshness also has a cost. A nightly load may suit strategic reporting but not a live stock reservation. More frequent ingestion can reduce lag while increasing operational complexity.

Explain the benefit with its condition: “Integrated historical data supports comparable reports because source differences are reconciled, provided the mappings, quality checks and loading process are maintained.”

19 | CHECK THE CHARACTERISTIC AND ITS PURPOSE

Choose the best characteristic or process for each requirement. Your first checked response counts; restart for another attempt.

20 | EXPLAIN THE FEATURE, MECHANISM AND BENEFIT

Use feature → how it works → useful consequence. For example: “The warehouse is integrated: product labels and date formats from web and counter systems are reconciled, therefore category totals combine comparable events instead of splitting or double-counting them.”

For time variation, link stored historical context to comparisons. For non-volatility and append-oriented loading, link retained evidence to traceability. For query-focused design, link physical or logical organisation to the expected large analytical workload.

Keep the limits clear: a warehouse can receive new loads, controlled history can be corrected, operational databases can retain history, and a report can be fast yet misleading if its population or measure is wrong.

Further reading: data warehousing concepts and analytical workloads.

MATCH THE WAREHOUSE FEATURE TO ITS PURPOSE

Terminology

Terminology

Data warehouse

An organised data environment supporting integrated historical analysis and reporting.

Business intelligence

Reporting, analysis and presentation used to understand performance and inform decisions.

OLTP

Online transaction processing: operational work such as recording orders or bookings.

Subject-oriented

Organised around analytical subjects such as sales rather than merely source applications.

Integrated

Source differences are reconciled into consistent meanings and formats.

Time-variant

Retains time context and history to support comparisons and trends.

Append-only approach

Adds events or versions instead of routinely overwriting prior history.

Non-volatile

Loaded analytical history is relatively stable rather than routinely edited by operational users.

Query-optimised

Designed for efficient expected analytical queries, such as scans, joins and summaries.

ETL

Extract, transform, load: a process preparing source data before warehouse loading.

ELT

Extract, load, transform: an approach applying some transformations after loading into the platform.

Staging area

A preparation layer used to collect, inspect and process source data.

Data cleansing

Identifying and handling quality issues under defined rules rather than inventing unknown facts.

Data lineage

Information about source origins and transformations producing a value.

Source identity

An identifier including appropriate source context to distinguish original events.

Idempotent loading

Replaying the same logical input does not apply its effect again.

Quarantine

Separating invalid or uncertain input for investigation.

Event time

When the represented business event occurred.

Load time

When the data entered the warehouse or processing layer.

Fact

A measurable business event or observation at a defined grain.

Measure

A numeric value analysed, such as units or sales value.

Dimension

Descriptive context used to filter, group or interpret facts.

Grain

What one row of a dataset represents.

Star schema

A dimensional design with a fact table linked to descriptive dimension tables.

Data mart

A focused analytical data area for a subject or department.

Partitioning

Dividing data into useful portions for storage, management or query processing.

Column-oriented storage

Organising data by columns to support suitable analytical access and compression.

Precomputed summary

A stored aggregate that can reduce repeated calculation work.

Data freshness

How recently the available data reflects the relevant source events.

Retention policy

Rules governing how long data is kept and how it is removed or archived.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which describe warehouse objectives?
2. Which describe subject orientation?
3. Which are integration tasks?
4. Which describe history accurately?
5. Which describe append-only and correction?
6. Which apply to the worked data?
7. Which support query-focused performance?
8. Which describe fact grain?
9. Which make historical reports more reliable?
10. Which explain the limits accurately?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Explain the primary objective of a data warehouse.

2. Explain subject orientation using the shop.

3. Explain integration using product labels and dates.

4. Explain why source identity matters during loading.

5. Explain time variation and distinguish event time from load time.

6. Explain append-only data and one qualification.

7. Explain non-volatility and distinguish it from append-only.

8. Explain how query-focused design can improve analysis.

9. Explain the accepted total and rejected records in the example.

10. Explain how a defined grain helps report interpretation.

11. Explain one benefit and one limit of the September–October comparison.

12. Explain why a warehouse differs from an operational database and a backup.

EXPLAIN IT | FROM EXPORTS TO A MANAGEMENT REPORT

Explain how the shop can combine web and counter sales for comparable category and monthly reports. Link subject orientation, integration, time variation, append-oriented loading, non-volatility and query-focused design to benefits. Include duplicate handling, invalid input, defined grain and one limitation of the final comparison.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

Selections are kept while this page is open.

Workbook

Workbook

COMING SOON

The workbook for A3.4.2 Data Warehouses (HL ONLY) is coming soon. Complete the matching task and written questions in your exercise book.