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.