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.