One question, ten reports
A made-up bakery chain has two regions, two products and two months of sales. The manager asks for sales by region. Then by product. Then by month. Then by region and month.
Writing a new report each time takes a day. There is a faster way: organise the data so any view is one click or one line away.
Key words
| Word | What it means | Example |
|---|---|---|
| OLAP | Online analytical processing: a way to organise data so you can view it from many sides quickly. | Sales by region, then by month |
| Cube | Data arranged by several dimensions, with a measure in each cell. | Region by product by month |
| Dimension | A way to split the data. | Region, product or month |
| Measure | The number you add up or average. | Sales amount |
| Slice | Fix one dimension to a single value and look at the rest. | Only January |
| Drill down | Move from a total to the detail inside it. | From all regions to one region |
| Roll up | Move from the detail to a total. | From each month to the year |
| Scenario | A what-if picture of the future based on a change. | What if cake prices rise? |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can explain what OLAP does, use slice, drill down and roll up on a table of sales, and run a simple what-if scenario.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.
Four things OLAP gives you
- Data from many sources. OLAP sits on top of blended data, so one view can use the till, the website and the stock list.
- Unlimited views. Change the dimension and you have a new report. No new program to write.
- Complex calculations. Totals, running totals and shares of the whole, done fast.
- Scenario planning. Change a figure and see the predicted result.
Try it: change the view
select region, sum(amount) as total
from sales
group by region
order by region;
-- Try it: change region to product, then to month. One word gives a new report.Worked example: slice, drill down and roll up
| Move | What you do | Bakery example |
|---|---|---|
| Slice | Fix one dimension to one value | Only the January rows |
| Dice | Fix several dimensions to chosen values | January and February, North only |
| Drill down | Go from a total to its detail | From all sales to sales in the North |
| Roll up | Go from detail to a total | From each product to all products |
The cells students fill in are left blank in this preview.