Friday, February 23, 2007

A Cube Example

Again taken from msdn...

A cube is defined by its measures and dimensions. The measures and dimensions in a cube are derived from the tables and views in the data source view on which the cube is based, or which is generated from the measure and dimension definitions.

The Imports cube contains two measures, Packages and Last, and three related dimensions, Route, Source, and Time.

Cube Example 1

The smaller alphanumeric values around the cube are the members of the dimensions. Example members are ground (member of the Route dimension), Africa (member of the Source dimension), and 1st quarter (member of the Time dimension).


The values within the cube cells represent the two measures, Packages and Last. The Packages measure represents the number of imported packages, and the Sum function is used to aggregate the facts. The Last measure represents the date of receipt, and the Max function is used to aggregate the facts.


The Route dimension represents the means by which the imports reach their destination. Members of this dimension include ground, nonground, air, sea, road, or rail. The Source dimension represents the locations where the imports are produced, such as Africa or Asia. The Time dimension represents the quarters and halves of a single year.


Business users of a cube can determine the value of any measure for each member of every dimension, regardless of the level of the member within the dimension, because Analysis Services aggregates values at upper levels as needed. For example, the measure values in the preceding illustration can be aggregated according to a standard calendar hierarchy by using the Calendar Time hierachy in the Time dimension as illustrated in the following diagram.

Diagram of measures organized along time dimension

In addition to aggregating measures by using a single dimension, you can aggregate measures by using combinations of members from different dimensions. This allows business users to evaluate measures in multiple dimensions simultaneously. For example, if a business user wants to analyze quarterly imports that arrived by air from the Eastern Hemisphere and Western Hemisphere, the business user can issue a query on the cube to retrieve the following dataset.

Packages Last
All Sources Eastern Hemisphere Western Hemisphere All Sources Eastern Hemisphere Western Hemisphere
All Time 25110 6547 18563 Dec-29-99 Dec-22-99 Dec-29-99
1st half 11173 2977 8196 Jun-28-99 Jun-20-99 Jun-28-99
1st quarter 5108 1452 3656 Mar-30-99 Mar-19-99 Mar-30-99
2nd quarter 6065 1525 4540 Jun-28-99 Jun-20-99 Jun-28-99
2nd half 13937 3570 10367 Dec-29-99 Dec-22-99 Dec-29-99
3rd quarter 6119 1444 4675 Sep-30-99 Sep-18-99 Sep-30-99
4th quarter 7818 2126 5692 Dec-29-99 Dec-22-99 Dec-29-99

After a cube is defined, you can create new aggregations, or you can change existing aggregations to set options such as whether aggregations are precalculated during processing or calculated at query time.