These 17 chapters cover the full syllabus, from what a data warehouse is to OLAP, clustering and web mining, in about 133 minutes of reading. Each chapter below has a short overview and a list of what it covers, so you can start at the beginning or jump straight to the topic you need.
These are independent study notes that explain standard topics in data warehousing and data mining with original worked examples. They are not official course material and are not affiliated with or endorsed by any university, publisher or company.
How to use these notes
- If you are new to the subject, read the chapters in order, because each one builds on the ones before it.
- Every chapter opens with an In this chapter list, so you can jump straight to any section.
- Each topic has a worked Example and an Explanation of it, mostly using one running case, a retail chain called ShopMart.
- Every chapter ends with Exam points, which sum up the facts most worth remembering before a test.
- The buttons at the bottom of each chapter take you to the previous chapter, the next chapter or back to this index.
Jump to a chapter
Fundamentals of Data Warehousing
Why organisations need a separate store for analysis, and Inmon’s four-part definition of a data warehouse. The chapter closes by contrasting an operational (OLTP) database with a warehouse (OLAP), using a bank as the example.
In this chapter:
- You will see why separate operational systems cannot answer company-wide questions, and how a warehouse solves this.
- You will learn Inmon’s definition and what subject-oriented, integrated, time-variant and non-volatile each mean.
- You will weigh the benefits of a warehouse against its cost and limits.
- You will compare an OLTP database with an OLAP warehouse on eight features.
Data Warehouse Architecture
The parts that make up a warehouse, from source data and ETL to metadata, marts and access tools, arranged in the classic three-tier architecture. It then covers how a warehouse is built and how it is spread across many processors.
In this chapter:
- You will name the eight components of a warehouse and the job of each, from source data to information delivery.
- You will trace a dashboard query through the bottom, middle and top tiers.
- You will compare top-down (Inmon) and bottom-up (Kimball) ways of building a warehouse.
- You will see how bitmap indexes, partitioning and parallel processing make large queries fast.
Multi-Dimensional Data Model
Facts, dimensions and data cubes, with the OLAP operations (roll-up, drill-down, slice, dice, pivot) worked on a sales cube. It compares star, snowflake and fact-constellation schemas and the three kinds of measures.
In this chapter:
- You will read a data cube as facts measured along dimensions with concept hierarchies.
- You will apply roll-up, drill-down, slice, dice and pivot to a sales cube.
- You will compare star, snowflake and fact constellation schemas and know when each fits.
- You will classify measures as distributive, algebraic or holistic.
Warehousing Strategy and Processes
How to decide why, where and how fast to build a warehouse, and the load, warehouse, query and support processes that keep it running. A seven-step implementation plan and a failure case show what goes wrong without them.
In this chapter:
- You will set out a warehousing strategy: sponsor, scope, approach and phasing.
- You will follow data through the load, warehouse and query processes and the support processes around them.
- You will plan an implementation in seven steps, from requirements to maintenance.
- You will recognise the common reasons warehouse projects fail.
Hardware and System Architectures
The servers, operating systems and storage a warehouse runs on, and how client/server tiers serve its users. It compares SMP, cluster, MPP and NUMA designs and explains distributed DBMS fragmentation, replication and two-phase commit.
In this chapter:
- You will list the hardware and operating-system needs of a warehouse and match RAID levels to uses.
- You will compare two-tier, three-tier and web-based client/server models.
- You will distinguish SMP, shared-disk clusters, MPP and NUMA.
- You will explain fragmentation, replication and two-phase commit in a distributed DBMS.
Warehousing Software and Schema Design
The software that makes up a warehouse, then the four-step dimensional design process. It covers the design issues every exam asks about: grain, surrogate keys, slowly changing dimensions, aggregates and partitioning.
In this chapter:
- You will name the software types in a warehouse and an example tool for each.
- You will design a star schema in four steps: process, grain, dimensions, facts.
- You will choose a grain and see its effect on table size.
- You will handle changing customer data with Type 1, 2 and 3 slowly changing dimensions.
Introduction to Data Mining
What data mining is and why it matters, with the seven-step KDD process walked through on a telecom churn example. It lists the main mining tasks with an example of each and the issues that make mining hard.
In this chapter:
- You will define data mining and test whether a pattern is non-trivial, unknown and useful.
- You will follow the seven steps of KDD on a churn-prediction example.
- You will sort mining tasks into predictive and descriptive, with an example of each.
- You will list the main issues in data mining, from scalability to privacy.
Data Pre-processing
How dirty data is made fit for mining: handling missing values, smoothing noisy data by binning, clustering and regression, and fixing inconsistencies. It ends with integration, correlation analysis and normalisation, worked with numbers.
In this chapter:
- You will spot incomplete, noisy and inconsistent data in a sample table.
- You will choose a method to fill missing values, from ignoring the tuple to predicting the value.
- You will smooth noisy data by binning, clustering and regression, with a worked binning example.
- You will integrate sources and normalise values with min-max, z-score and decimal scaling.
Data Reduction
Ways to shrink data while keeping its meaning: cube aggregation, attribute selection, compression (wavelets, PCA) and numerosity reduction. It covers discretisation, the 3-4-5 rule, concept hierarchies and the decision tree as a reduction tool.
In this chapter:
- You will reduce data with cube aggregation and attribute subset selection.
- You will compare lossless and lossy compression, wavelets and PCA.
- You will apply numerosity reduction with regression, histograms, clustering and sampling.
- You will discretise values with the 3-4-5 rule and build concept hierarchies.
Concept Description and Classification
The two-step classification process, and how data is generalised and characterised with attribute-oriented induction. It covers attribute relevance, class comparison with t- and d-weights, and statistical measures worked on a salary data set.
In this chapter:
- You will describe the two steps of classification: learning a model and using it.
- You will generalise a student relation with attribute-oriented induction.
- You will measure attribute relevance and compare classes with t-weight and d-weight.
- You will compute the mean, median, mode, quartiles and five-number summary of a data set.
Classification Algorithms
Statistical, distance-based and decision-tree classifiers, each worked on the classic buys_computer data. It includes a full Naïve Bayes calculation, a KNN vote, and the entropy and information-gain steps that build a decision tree.
In this chapter:
- You will predict with regression and classify a new record with Naïve Bayes.
- You will see how the choice of k changes a KNN vote.
- You will compute entropy and information gain to choose a decision tree’s root.
- You will compare ID3, C4.5, CART and scalable tree algorithms, and see why trees are pruned.
Clustering
How unlabelled data is grouped, starting with distance measures and a worked dendrogram and K-means run. It then covers CURE and Chameleon, density-based DBSCAN and OPTICS, grid-based STING and CLIQUE, and model-based EM.
In this chapter:
- You will compute Euclidean, Manhattan, Minkowski and Jaccard measures.
- You will build a single-link dendrogram and run K-means by hand.
- You will explain how CURE and Chameleon find clusters of any shape.
- You will compare DBSCAN, OPTICS, STING, CLIQUE and EM.
Association Rules
Support, confidence and lift, then a full Apriori run on nine transactions with rule generation. It compares sampling, partitioning and FP-Growth, parallel algorithms, and the neural network approach.
In this chapter:
- You will compute support, confidence and lift for a rule.
- You will run Apriori by hand on nine transactions and generate rules from a frequent itemset.
- You will compare sampling, partitioning and FP-Growth with Apriori.
- You will distinguish Count Distribution from Data Distribution in parallel mining.
Data Visualization and Querying
How warehouse data is shown and queried: visualisation techniques, pre-computed aggregates and the aggregate navigator, and the value of keeping history. It compares managed, ad hoc and executive query facilities.
In this chapter:
- You will match visualisation techniques to the shape of the data.
- You will see how an aggregate navigator answers a query from the smallest suitable table.
- You will explain why keeping history makes trend analysis possible only in the warehouse.
- You will compare managed, ad hoc and executive query facilities.
OLAP
OLAP functions and tools, and the three kinds of OLAP server (ROLAP, MOLAP, HOLAP) compared side by side. It closes with OLAM, where OLAP navigation and data mining are combined.
In this chapter:
- You will list OLAP functions beyond the basic five, such as drill-across and what-if analysis.
- You will compare ROLAP, MOLAP and HOLAP on storage, speed and scale.
- You will see why sparse data is a problem for MOLAP.
- You will explain how OLAM combines OLAP navigation with mining.
Warehouse Administration
Running a warehouse day to day: security controls, backup and recovery schedules, load and query tuning, and the tests that prove the data is correct.
In this chapter:
- You will apply security controls, from roles to row-level security and masking.
- You will plan full, incremental and partial backups and a restore.
- You will tune loads and queries using parallelism, indexes and summaries.
- You will test a warehouse from unit tests to user acceptance.
Applications and Recent Trends
Where warehouses and mining are used across industries, and three specialised kinds of mining: web, spatial and temporal. It ends with recent trends such as cloud warehouses, streaming and lakehouses.
In this chapter:
- You will match warehousing applications to industries.
- You will distinguish web content, structure and usage mining.
- You will give examples of spatial and temporal mining.
- You will name recent trends such as cloud warehouses, streaming and lakehouses.
Text books for further reading
- Alex Berson, Stephen J. Smith, “Data Warehousing, Data-Mining & OLAP”, TMH
- Mark Humphries, Michael W. Hawkins, Michelle C. Dy, “Data Warehousing: Architecture and Implementation”, Pearson
- Margaret H. Dunham, S. Sridhar, “Data Mining: Introductory and Advanced Topics”, Pearson Education
- Arun K. Pujari, “Data Mining Techniques”, Universities Press
- Pieter Adriaans, Dolf Zantinge, “Data-Mining”, Pearson Education
Credits and disclaimer
- These notes are an original summary of widely taught concepts. The explanations, worked examples and figures were written for this blog.
- They are not affiliated with or endorsed by any university, publisher or company. Product names (Oracle, Teradata, Snowflake, Amazon Redshift, Google BigQuery, Power BI, Tableau, Informatica and others) are trademarks of their respective owners.
- The buys_computer training set used in Chapter 11 is a classic teaching example found in many data mining textbooks.
- Notes are for learning only and may contain errors; check your prescribed textbooks and syllabus for the final word.
Independent study notes for learning purposes. Not affiliated with or endorsed by any university, publisher or course provider. Product and company names are trademarks of their respective owners.