Cube Structure

Analysis Services

Analysis Services

Cube Structure

A cube's structure is defined by its measures and dimensions. They are derived from tables in the cube's data source. The set of tables from which a cube's measures and dimensions are derived is called the cube's schema. Every cube schema consists of a single fact table and one or more dimension tables. The cube's measures are derived from columns in the fact table. The cube's dimensions are derived from columns in the dimension tables.

For example, a cube has the following schema.

The cube's measures and dimension levels are derived from the following columns.

Measure or level Members
Source table
Source column Sample column value
Packages measure Not applicable Imports_
Fact_Table
Packages 12
Last measure Not applicable Imports_
Fact_Table
Last May-03-99
Route Category level in Route dimension nonground,
ground
Route_
Dimension_
Table
Route_
Category
nonground
Route level in Route dimension air,
sea,
road,
rail
Route_
Dimension_
Table
Route sea
Hemisphere level in Source dimension Eastern Hemisphere,
Western Hemisphere
Source_
Dimension_
Table
Hemisphere Eastern Hemisphere
Continent level in Source dimension Africa,
Asia,
Australia
Europe,
N. America,
S. America
Source_
Dimension_
Table
Continent Europe
Half level in Time dimension 1st half,
2nd half
Time_
Dimension_
Table
Half 2nd half
Quarter level in Time dimension 1st quarter,
2nd quarter,
3rd quarter,
4th quarter
Time_
Dimension_
Table
Quarter 3rd quarter

A single cube cell is usually derived from multiple rows in the fact table. For example, the cell in the Imports cube for the air member, the Africa member, and the 1st quarter member is derived from the following rows in the Imports_Fact_Table.

Import_
Receipt_ID

Route_ID

Source_ID

Time_ID

Packages

Last
3516987 1 6 1 15 Jan-10-99
3554790 1 6 1 40 Jan-19-99
3572673 1 6 1 34 Jan-27-99
3600974 1 6 1 45 Feb-02-99
3645541 1 6 1 20 Feb-09-99
3674906 1 6 1 36 Feb-17-99

In the preceding table, the fact that each row has the same values for Route_ID, Source_ID, and Time_ID indicates that these rows contribute to the same cube cell.

There are two common types of cube schemas: star and snowflake. In a star schema, each dimension table joins to the fact table. The Imports cube schema shown earlier in this topic is a star schema. In a snowflake schema, one or more dimension tables join to another dimension table rather than to the fact table. The dimension tables that do not join to the fact table are for dimensions with multiple dimension tables. For example, the administrator of the Imports cube wants to expand the cube to multiple years. To accomplish this, the administrator adds a Year_ID column to the Time_Dimension_Table, the Time_Dimension_Table_2 to the cube's schema, and a Year level to the Time dimension. (The Year level is based on the Time_Dimension_Table_2.Year column.) After these changes are made, the Time dimension is based on two dimension tables, and the cube's schema is snowflake, as shown in the following illustration.

After you change a cube's structure, usually you must process the cube with the Full process option so that changes can be seen by end users. For more information, see Processing Cubes.

See Also

Dimensions

Measures