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).

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.