DDMA Level II
Warehousing and Data Mining cheat sheet
Every chapter's key points, formulas and definitions on one page, for the last week before the exam. Each heading links to the chapter that explains it.
Warehousing and mining concepts
- 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.
Key terms
- 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.
- 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.
- 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.
The rest of the sheet covers
- Building a data warehouse
- Mining and managing data
- Using mined data
- Exam technique and revision
- Glossary