OLAP and data mining

A warehouse is only useful if people can get answers out of it. Two families of tools do that: OLAP, for exploring summarised data, and data mining, for discovering patterns.

Online analytical processing (OLAP) stores warehouse data in a multi-dimensional structure called a cube. Each dimension is one way of looking at the data: product, place, time. Each cell holds a measure, such as total sales. Because the sums are prepared in advance, analysts can look at the data from any side and change the level of detail almost instantly. The operations to know:

• Roll-up: summarise to a higher level (quarters become years, cities become countries).
• Drill-down: the reverse: go into more detail (a year becomes its four quarters).
• Slice: hold one dimension at a single value, such as all the sales in 2025, giving one layer of the cube.
• Dice: choose particular values on several dimensions: for example just two products in two countries.
• Pivot: rotate the cube, swapping rows and columns for a new view of the same numbers.

These support decisions such as budgeting (where is money being wasted?) and forecasting (what will sell next quarter, judging by past trends?).

OLAP needs a person to ask the questions. Data mining goes through the raw data with algorithms to find patterns nobody asked about. The common techniques:

• Classification: put items in known categories (spam or not; likely to buy or not) using a model trained on examples that already have labels.
• Clustering: group items that are alike, with no labels given. It reveals customer segments, and where a new product sits among existing ones.
• Regression: predict a number from other numbers, such as next quarter's profit from past sales and costs.
• Association rule discovery: find things that go together, such as sausages and barbecue sauce, to place products and plan offers.
• Sequential pattern discovery: find common orders of events, such as the pages most people click one after another, to predict what a customer will do next.
• Anomaly detection: spot outliers that do not fit the pattern: a card used in two countries at once, an unusual jump in network requests, a drug batch outside its limits.

Companies use these to target marketing, set prices, detect fraud and forecast demand. They need good data to work from (hence the cleaning in ETL), and their conclusions should be questioned: a pattern in the data is not proof of a cause.