Warehousing and mining concepts: DDMA Warehousing and Data Mining notes

Key takeaways

A data warehouse is a central repository of integrated data drawn from one or more source systems, built for analysis and reporting rather than day-to-day transaction processing.
Its four characteristics are subject-oriented, integrated, time-variant and non-volatile: once loaded, data is added to, never changed or deleted.
Data moves from operational source systems through extraction, transformation and loading (ETL) into the warehouse, where the load, warehouse and query managers and the end-user access tools handle it.
OLTP systems handle many small, fast read/write transactions; a warehouse handles large, complex read-heavy queries over historical data.
OLAP's four operations are roll-up, drill-down, slice and dice, and pivot, while the star, snowflake and fact constellation schemas set how fact and dimension tables relate.

A data warehouse is a central repository of integrated data drawn from one or more source systems, structured and stored specifically to support analysis and reporting rather than day-to-day transaction processing.

Characteristics of a data warehouse: subject-oriented (organised around business subjects like sales or customers, not individual applications), integrated (data from many sources made consistent), time-variant (holds historical data over a long period), and non-volatile (once loaded, data is not changed or deleted, only added to).

The data warehousing process moves data from operational source systems through extraction, transformation and loading (ETL) into the warehouse, from where it is queried and analysed.

Load manager

Performs the operations needed to extract and load data into the warehouse.

Warehouse manager

Manages the data once it is in the warehouse, organising, archiving and maintaining it.

Query manager

Manages user queries, directing them efficiently to the right data.

End-user access tools

Reporting, analysis and visualisation tools end users interact with directly.

Users and uses of a data warehouse: analysts and executives use it for reporting, trend analysis and strategic decision making, drawing on history a day-to-day transactional system does not retain.

The basic data warehouse architecture layers source systems, an ETL process, the central warehouse (often with smaller subject-specific data marts drawing from it), and end-user access tools on top.

OLTP systems

Optimised for many small, fast read/write transactions, day-to-day operational processing, e.g. a point-of-sale system.

Data warehouses

Optimised for large, complex read-heavy queries over historical data, analysis rather than transaction processing.

Online Analytical Processing (OLAP) is the technology that lets analysts explore warehouse data interactively across multiple dimensions, supported by four basic operations:

Roll-up

Aggregates data to a higher, less detailed level, e.g. from daily to monthly sales.

Drill-down

The reverse, moving to a more detailed level, e.g. from monthly down to daily.

Slice and dice

Selecting a subset of the data (slice) and viewing it from different angles (dice).

Pivot (rotate)

Reorients the data's dimensions to view it from a different perspective.

Star schema

A central fact table connected directly to denormalised dimension tables, simple and fast to query.

Snowflake schema

Extends the star schema by normalising dimension tables into sub-dimensions, saving storage at the cost of more complex queries.

Fact constellation schema

Multiple fact tables sharing dimension tables, supporting more complex, multi-subject analysis.

Exam tip

OLAP's four operations (roll-up, drill-down, slice and dice, pivot) are commonly tested by describing an analyst's action and asking which operation it represents, roll-up and drill-down are opposites along the same detail axis, slicing/dicing and pivoting change which dimensions are being viewed.

Warehousing and mining concepts

Test Your Knowledge