Introduction to Dimensions
Dimensions can also be based on OLAP data mining models. They can be used to store the results of a mining model analysis and can be browsed within the context of a virtual cube.
All dimensions are based directly or indirectly on tables, even dimensions based on OLAP mining models. When you create a dimension from a table, you select the columns that define it. The order in which the columns are selected is significant because it affects the placement of members within the dimension's hierarchy. However, the order of members of a dimension created through a mining model analysis is determined by the analysis. This is because the dimension represents the content of the model.
Dimensions are hierarchical, and in most cases their members are arranged in a pyramid-like configuration. The horizontal placement results from column values with the same level in the hierarchy of the dimension, and the vertical placement results from column values having different levels in the hierarchy of the dimension.
For example, the Factory Location dimension has the following members.
This dimension is defined by selecting the Region column and then the State column from the following table.
Note The All Factory Locations member is generated by Microsoft® SQL Server™ 2000 Analysis Services. Generation of an All member is optional. For more information, see (All) Level and All Member.
The primary key of each dimension table joins to a foreign key in a cube's fact table or another dimension table. (An exception is when a virtual cube's dimension uses custom rollup formulas or custom member formulas to determine the values of all of its members.) In the preceding example, the State_ID column joins to a foreign key column in the cube's fact table. Key columns are not required in the dimension definition. For more information about the relationships between dimension tables and fact tables, see Cube Structure.
Dimensions categorize the numeric data (that is,
The smaller alphanumeric values around the cube are the members of the dimensions. The numeric values within the cube represent the measure, Production Count. These values exist for all cells in the cube but are shown only for those in the foreground.
The total, aggregated value of this cube is the sum of Production Counts for all Products, all Time, and all Factory Locations. This value is the sum of the Production Counts in all 64 of the cube's cells.
The Dimension Wizard enables you to create dimensions quickly and easily. You can use it by itself to create shared dimensions, or, while you are creating a cube with the Cube Wizard, you can invoke the Dimension Wizard to create private or shared dimensions. After a shared dimension is created, you can maintain it in Dimension Editor. After a private dimension is created, you can maintain it in Cube Editor. However, dimensions created by mining model analysis cannot be edited.
After a dimension is created, you must process the dimension. After a dimension is changed or its table is updated, usually you must process the dimension. However, dimension processing can disrupt end users' access to the cubes that include the dimension.
Queries Against Dimension Data
End users can issue queries to analyze either the whole cube or selected portions of it at varying levels of detail. This is accomplished by specifying criteria for each dimension. You can perform these types of operations in Cube Browser. For more information, see Viewing Cube Data.
For example, if the end user chooses to limit the query to Production Counts for hammers, only the front fourth of the cube is retrieved. The Production Counts for pliers, saws, and drills are ignored. This type of operation is called slicing.
The other type of operation that end users perform with dimensions is the combination of drilldown and drillup. This operation determines the level of detail to which the retrieved measure values are separated. For example, if the end user does not drill down at all, the following dataset is returned.
| All Time | |
| All Factory Locations | 49005 |
Notice that no part of the Product dimension appears in the dataset's column headings or row headings. This is because the end user sliced on one of the members of the Product dimension, hammer. A dimension can be used either to slice or to drill down or up, not both.
If the end user drills down into All Factory Locations, a new query is issued, and the following dataset is returned.
| All Time | ||
| All Factory Locations | 49005 | |
| East | 16915 | |
| West | 32090 |
If the end user drills down into the East member, a new query is issued, and the following dataset is returned.
| All Time | |||
| All Factory Locations | 49005 | ||
| East | 16915 | ||
| Maine | 8883 | ||
| Ohio | 8032 | ||
| West | 32090 |
If the end user drills down into All Time, a new query is issued, and the following dataset is returned.
| All Time | 1st half | 2nd half | |||
| All Factory Locations | 49005 | 24981 | 24024 | ||
| East | 16915 | 8962 | 7953 | ||
| Maine | 8883 | 4814 | 4069 | ||
| Ohio | 8032 | 4148 | 3884 | ||
| West | 32090 | 16019 | 16071 |
If the end user drills up the East member, a new query is issued, and the following dataset is returned.
| All Time | 1st half | 2nd half | ||
| All Factory Locations | 49005 | 24981 | 24024 | |
| East | 16915 | 8962 | 7953 | |
| West | 32090 | 16019 | 16071 |
The end user can continue to issue queries by slicing on members in some dimensions and drilling down and up on members in other dimensions.
Object Hierarchy
In the object hierarchy, dimensions are immediately subordinate to the following objects:
- Database
These dimensions are shared among the cubes in the database.
- Cube
These dimensions are included in the cube. They may be derived from the shared dimensions in the database or they may be private (that is, unshared).
In the object hierarchy, levels are immediately subordinate to dimensions. The levels of a dimension are created when the dimension is created. Levels are based on the columns in the dimension's definition.
If you are programming with Decision Support Objects (DSO), the class types associated with the dimension are:
- clsDatabaseDimension
- clsCubeDimension
- clsPartitionDimension
- clsAggregationDimension