BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
This series of blogs introduces the basic data structures for storing data, including OLTP and OLAP databases.
- Types of Data Storage Structures
- Comparison of the Two Storage Methods
- Cubes in SQL Server Analysis Services (this blog)
- Summary of Jargon in SQL Server Analysis Services
Posted by Andy Brown on 16 May 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Cubes
The unit of analysis in a UDM is called a cube, even though it doesn't necessarily have 3 dimensions. You can envisage cubes using either pivot tables or the WITH CUBE statement.
Cubes as Pivot Tables
Using our 3-table example, you could imagine a cube as a pivot table in Excel as shown below:

Cubes as SQL Query Results
The cubes-as-pivot-tables example shown above suggests that a cube is limited to two dimensions. In fact, the WITH CUBES command in SQL better explains them:

In a grouping query, WITH CUBE forces SQL Server to calculate all possible totals.
The query above would show the following results:

The result of running this query – for each studio and director combination, we get a total (though many are null).
This shows exactly what cubes do: they calculate every possible combination of a particular aggregate function (here summing) for each combination of dimensions.
Cube Size
The diagrams below show the number of rows of data stored by our example database, and by our example cube. First the database:

Our core SQL Server database contains 121 directors, 40 studios and 260 films, making at total of 121 + 40 + 260 = 421 records.
Then the cube:

When you deploy the cube, you get potentially one aggregate number for each combination of dimensions. Thus the total number of rows could be as high as121 x 40 = 4,840.
This shows that the amount of data stored for a cube grows geometrically with every new dimension added, whereas the size of a SQL Server database grows arithmetically for every new table added.
- Types of Data Storage Structures
- Comparison of the Two Storage Methods
- Cubes in SQL Server Analysis Services (this blog)
- Summary of Jargon in SQL Server Analysis Services