An Introduction To Cubes

If you build a Data Warehouse, and nobody looks at it... is it really there? The sad truth is that it might as well not be! You need your users to be looking at their data, and there is no better way of doing this than by building OLAP Cubes for your users to explore. To do this, you are going to need two things - a cube server, such as SQL Server Analysis Services; and a client tool. And one very good piece of news is that you've almost certainly got the latter on every desktop in your organization - it is called Excel!

If you are new to cubes, then the easiest way to think of them is as hyper-intelligent pivot tables. (Indeed, this is how Excel presents them when it is used as an Analysis Services front-end.) This article describes the key concepts you are going to need to understand in order to get cubing.

Let's picture some data we have within our organization - say a list of sales:

At a fundamental level, a cube is just a collection of two things - Dimensions and Measures. (A Measure is also sometimes known as a Fact.)

A Measure is something you can count - it will usually be the NUMBERS in your data - like "Quantity" or "Cost". A Measure will always have a default aggregation associated with it, like "Count" or "Sum" - a bit like a GROUP BY in SQL.

A Dimension is something you can analyze by - it will be the DESCRIPTORS in your data - like "Product" or "Customer". (A date is also a special type of dimension, called a Time Dimension.) One great feature of Dimensions is that they can describe hierarchies or roll-ups - things that belong inside other things - like Customer/ State/ Country; or Date/ Month/ Quarter/ Year - to allow your users to "drill down" through their data.

When you build a cube, you combine Dimensions and Measures to help your users answer questions, like "How many screws have we sold to people in Texas?" and "How much profit did I make on nails in Q1?"

The diagram below shows some typical output in Excel 2007.