Overview

A cube consists of a number of tables - one fact table and several dimension tables. The fact table relates all the dimension tables together, either in a star or snowflake pattern, as illustrated below.

Star Schema
star schema
Snowflake Schema
snowflake schema

In the star schema, each row in the fact table contains foreign keys to link to each dimension table. In the snowflake schema, dimension tables may themselves link to other dimension tables.

When you build a cube, you define two axes: row and column, which each consist of a sequence of dimensions. For example you might view sales events where Row=City and Column=Product,Gender (where City, Product and Gender are available dimensions). This would produce a cube table showing a breakdown of sales by city, further subdivided by the product sold and gender of the customer. Each combination of (City,Product,Gender) forms a cell in the cube table.

The final step is to identify what to show in that cell. There may be thousands of transactions that correspond to (Brisbane,Beer,Male), so we need to summarise those transactions with a Measure. A Measure lets us compare cells with those around it - for example was more beer bought by men in Brisbane than by women? A measure might be a simple expression such as "Count" - how many - or "Average" - perhaps there were more sales, but each was for less beer. More complex expressions allow you to compare across time periods - was more beer bought this year than the same month last year.

Behind the scenes, the server builds a number of indexes which cluster the information in the sales data for fast access. Facts do not change, so the indexes are stable. However, new facts may arrive on a regular basis, so it may be important to incrementally update the indexes when facts are added to your system.

Once you become familiar with structuring cubes and expressing queries, a lot of valuable insights can be gleaned from your raw sales data.