Data Warehouse & OLAP

What is a data warehouse?

Defined in many ways, but not strictly. Loosely:

• A decision support database that is maintained separately from the organization’s operational database.
• Supports information processing by providing a solid platform of consolidated, historical data for analysis.

Data warehousing = the process of constructing and using data warehouses.

Subject-oriented

• Organized around major subjects, such as customer, product, sales.
• Focuses on modelling and analysis of data for decision makers, not on daily operations or transaction processing.
• Provides a simple and concise view around particular subject issues by excluding data not useful in the decision support process.

Integrated

• Constructed by integrating multiple, heterogeneous data sources — relational databases, flat files, on-line transaction records.
• Data cleaning and data integration techniques are applied to ensure consistency in naming conventions, encoding structures, attribute measures, etc. among sources.
  • e.g. hotel price: different sources may differ in currency, whether tax is included, whether breakfast is covered…
• When data is moved into the warehouse, it is converted.

Time-variant

• The time horizon is significantly longer than that of operational systems.
  • Operational database: current value data.
  • Data warehouse: information from a historical perspective (e.g. past 5–10 years).
• Every key structure in the warehouse contains an element of time, explicitly or implicitly — whereas the key of operational data may or may not contain a time element.

Nonvolatile

• A physically separate store of data transformed from the operational environment.
• Operational updates do not occur in the warehouse environment.
  • Does not require transaction processing, recovery and concurrency control mechanisms.
  • Requires only two operations in data accessing: initial loading of data and access of data.

How organizations use data warehouses

Many organizations use warehouse information to support business decision making, e.g.:

• Increasing customer focus — analysing customer buying patterns (buying preference, buying time, budget cycles, appetite for spending).
• Repositioning products and managing product portfolios — comparing sales performance by quarter, by year and by geographic region to fine-tune production strategies.

OLTP vs OLAP

Why a separate data warehouse?

High performance for both systems:

• The operational DBMS is tuned for OLTP: access methods, indexing, concurrency control, recovery.
• The warehouse is tuned for OLAP: complex OLAP queries, multidimensional view, consolidation.

Different functions and different data:

• Missing data — decision support needs historical data that operational DBs typically don’t keep.
• Data consolidation — decision support needs aggregation/summarization of data from heterogeneous sources.
• Data quality — different sources use inconsistent representations, codes and formats that must be reconciled.

The multidimensional data model

2-D view — AllElectronics sales (Vancouver)

Dimensions time (quarter) and item (type); measure = dollars sold (in thousands).

3-D view — adding location

Now view by time, item and location (Chicago, New York, Toronto, Vancouver). Conceptually this is a cube; as tables it is a series of 2-D tables, one per city.

Each cell holds a measure (dollars_sold) for one (time, item, location) combination. Front face = Vancouver.

From tables and spreadsheets to data cubes

• A data warehouse is based on a multidimensional data model, which views data as a data cube.
• A data cube (e.g. sales) lets data be modelled and viewed in multiple dimensions:
  • Dimension tables — e.g. item(item_name, brand, type) or time(day, week, month, quarter, year).
  • Fact table — contains measures (e.g. dollars_sold) and keys to each related dimension table.
• An n-D base cube is called a base cuboid. The top-most 0-D cuboid, holding the highest level of summarization, is the apex cuboid. The lattice of cuboids forms a data cube.

With 3 dimensions there are 2³ = 8 cuboids: 1 apex (0-D), 3 one-D, 3 two-D, and 1 base (3-D).

Conceptual modelling: star, snowflake, fact constellation

Data warehouses are modelled with dimensions and measures:

Star schema

The most common modelling paradigm. The warehouse contains:

1. a large central table (fact table) containing the bulk of the data, with no redundancy; and
2. a set of smaller attendant tables (dimension tables), one per dimension.

The schema graph resembles a starburst, with dimension tables displayed radially around the central fact table.

The fact table holds the four foreign keys plus the measures (units_sold, dollars_sold, avg_sales). Each dimension table describes one dimension.

-- Typical star-schema query: total dollars sold per city per quarter in 2026
SELECT   l.city, t.quarter, SUM(f.dollars_sold) AS total_sales
FROM     sales f
JOIN     time t     ON f.time_key = t.time_key
JOIN     location l ON f.location_key = l.location_key
WHERE    t.year = 2026
GROUP BY l.city, t.quarter
ORDER BY l.city, t.quarter;

OLAP operations

Quick review