Data Structures in SQL Server Analysis Services
Part three of a four-part series of blogs

This series of blogs introduces the basic data structures for storing data, including OLTP and OLAP databases.

  1. Types of Data Storage Structures
  2. Comparison of the Two Storage Methods
  3. Cubes in SQL Server Analysis Services (this blog)
  4. 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:

Example of pivot table in Excel
 

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: 

Sample SQL code

In a grouping query, WITH CUBE forces SQL Server to calculate all possible totals.

 

The query above would show the following results:

Sample results - a list of actors

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:

Diagram of 3 tables with number of records for each

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:

Sample results

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.

  1. Types of Data Storage Structures
  2. Comparison of the Two Storage Methods
  3. Cubes in SQL Server Analysis Services (this blog)
  4. Summary of Jargon in SQL Server Analysis Services
This blog has 0 threads Add post