OLAP and Data Mining: Two Ways to Turn Stored Data into Decisions
Business intelligence is the practice of using an organisation's stored data to guide its decisions.
OLAP answers questions an analyst already knows how to ask, by summarising warehouse data along dimensions such as time, region and product.
Data mining searches for patterns nobody asked for, working on the raw data rather than on pre-summarised cubes.
The two are used together: mining finds a pattern worth investigating, and OLAP is how the analyst then examines it.
Online Analytical Processing: Interactive Views of Warehouse Data
Online Analytical Processing (OLAP) analyses data already held in a data warehouse, so it works on history rather than on live transactions.
It gives the analyst multidimensional views, so the same sales figure can be looked at by month, by store, by product line, or by any combination of those.
Note
OLAP is separate from the online transaction processing (OLTP) that runs the business day to day.
OLTP handles many small writes, such as recording one sale, and must respond in milliseconds.
OLAP handles few very large reads across years of history, and is measured in seconds rather than milliseconds.
How OLAP Works: The Cube
Data organisation: warehouse data is structured into an OLAP cube, a multidimensional array whose axes are the dimensions an analyst wants to slice by.
Pre-Processing: Data is cleaned, transformed, and organized into cubes before analysis.
Multidimensional analysis: because the totals are already calculated for each combination of dimensions, a query that would otherwise scan millions of rows becomes a lookup.
Note
An OLAP cube is not a literal cube and is not limited to three dimensions.
A cube with dimensions for time, region, product and salesperson has four axes, and the word "cube" is only a convenient picture.
The Five OLAP Operations
Roll-up: aggregates data up a hierarchy, for example from daily sales to monthly sales.
Drill-down: the reverse, breaking an aggregate back into its detail, for example from monthly sales back to daily.
Slice: fixes one dimension to a single value, producing a flat view such as sales for one region only.
Dice: fixes several dimensions at once, producing a sub-cube such as one product in one region over two quarters.
Pivot: rotates the cube so the dimensions swap axes, showing the same numbers arranged differently.
Tip
Start with a roll-up to see the overall shape, then drill down into whatever looks unusual.
Working the other way round means examining detail without knowing which detail matters.
Data Mining: Finding Patterns Nobody Specified
Data mining extracts meaningful patterns from large data sets, often from raw data rather than from the pre-processed cubes OLAP uses.
The analyst does not state the pattern in advance, so mining can surface a relationship that no one thought to look for.
Note
Data mining and OLAP are normally used together rather than as alternatives.
Mining identifies a pattern, and OLAP is the tool the analyst then uses to examine and interpret it.
The Six Data Mining Techniques on the Syllabus
Classification
Assigns each item to one of several predefined categories based on its attributes.
A model is trained on labelled data and then applied to new, unlabelled data.
An email filter classifies each message as spam or not spam from its content, sender and subject line.
Clustering
Groups items into clusters of similar items with no categories defined in advance.
The algorithm finds the natural groupings itself, so the number and meaning of the groups emerge from the data.
Example: A marketing team uses clustering to group customers into segments based on spending habits and interests, even though the segments were not defined in advance.
Regression
Predicts a continuous numerical value from the relationship between input variables.
Example: A real estate company uses regression to predict house prices based on features like size, location, and number of bedrooms.
Finds items that occur together more often than chance would explain.
It produces rules of the form: if A is present, B is likely to be present too.
Supermarket basket data might yield the rule that a customer buying bread and milk is likely to also buy butter.
Sequential pattern discovery
Finds patterns in time-ordered data, where the order of events is the pattern.
An online store finds that customers who buy a console often return later for games, then for accessories.
Anomaly detection
Identifies outliers, the data points that do not follow the expected pattern.
A bank flags a sudden large withdrawal from an overseas location as a possible fraudulent transaction.
Common Mistake
Classification and regression are often confused in exam answers.
Classification predicts a discrete label such as spam or not spam.
Regression predicts a continuous value such as a house price.
Clustering is distinct from both because it has no labelled training data to learn from.
Where Data Mining Is Used
Marketing: mining customer response data shows which adverts are acted on and which are ignored, which is what makes personalised advertising possible.
Sales: comparing sales patterns across products and locations shows what is selling well and where products are underperforming, so stock can be sent where it moves.
Fraud detection: mining maps the links that normal behaviour produces, and the absence of an expected link, or the presence of an unexpected one, marks a transaction for investigation.
Human resources: analysing training records, performance data and exit reasons shows which parts of the employee experience drive people to leave.
Customer service: mining support tickets identifies the most frequent problems and their usual solutions, which is what populates help centre articles and chatbot answers.
How OLAP and Data Mining Work Together
Complementary roles: OLAP gives structured analysis along dimensions the analyst chooses, while data mining uncovers patterns the analyst did not specify.
Better decisions: a pattern found by mining is worth little until an analyst can examine it by region, period and product, which is what OLAP provides.
Different data: OLAP queries pre-aggregated cubes and is therefore fast, while mining works on the raw detail and is therefore slower but richer.
Analogy
Data mining works like a detective who notices that a particular kind of theft keeps happening.
OLAP is the magnifying glass that then examines those cases by street, by hour and by month.
Neither tool does the other's job: the detective does not know in advance what to magnify, and the magnifying glass finds nothing on its own.
Where This Connects to the Rest of the Course
The six mining techniques reappear as machine learning approaches in A4.3, where the algorithms behind them are examined: regression in A4.3.1, classification in A4.3.2, clustering in A4.3.4, and association rules in A4.3.5.
The ethics of mining personal data, including privacy, algorithmic bias and transparency about how conclusions were reached, are covered in A4.4.1.
Mining a data set that does not represent the whole population produces a model that is confidently wrong about the groups it under-samples.
Active recall
Distinguish between OLAP and OLTP.
Name the five OLAP operations and say what each one does to a cube.
Which mining technique predicts a continuous value, and which predicts a category?
What kind of pattern does sequential pattern discovery find that association rule discovery does not?
