Dimension Structure
The structure of the dimension you create determines the variety of the dimension. You create dimensions based on dimension table columns, member properties, or from the structure of OLAP data mining models. When you define a dimension, there are a number of possible approaches. Each approach produces a different dimension variety. You can:
- Select one or more columns from a
dimension table or joined dimension tables. If you select multiple columns, all columns should be interrelated such that their values can be organized into a hierarchy. To define the hierarchy, sort the columns from most general to most specific. For example, a Time dimension is created from the columns Year, Quarter, Month, and Day. This approach produces a regular dimension (that is, a dimension that is neither a parent-child dimension nor a virtual dimension). - Select two columns from a single dimension table. One column identifies each component of the dimension's hierarchy, and the other identifies each component's parent. For each row in the table, these two columns identify a parent-child linkage. All the linkages are combined to determine the dimension's hierarchy. For example, a Genealogy dimension is created from the columns Person and Parent. This approach produces a parent-child dimension.
- Select one or more
member properties in another dimension. Each member property is based on a column in the other dimension's table. This column contains values that are attributes of another column's values. For example, the Store Type dimension (included in Microsoft® SQL Server™ 2000 Analysis Services) is created from the Store Type member property, which is in the Store dimension and is an attribute of the store_id column. This approach produces a virtual dimension.An alternative approach to defining a virtual dimension is to directly select columns in another dimension's table. With this approach, member properties are not required.
- Choose to create a dimension when creating an OLAP mining model in the Mining Model Wizard. You can create a dimension from the results of the mining model analysis and can also create a virtual cube that contains the dimension and the mining model's source cube.
The columns or member properties in a dimension definition contribute
The relationship between the number of columns or member properties in a dimension's definition and the number of levels in the dimension depends on the variety of the dimension. In a regular dimension, each column in the definition of the dimension contributes a level. In a parent-child dimension, the two columns in the definition of the dimension contribute a number of levels that depends on the data in the columns. In a virtual dimension, each member property or column in the definition of the dimension contributes a level.
Each level contains members. Members are the values in the columns or member properties that define the levels. For example, the Quarter level might contain four members: Quarter 1, Quarter 2, Quarter 3, and Quarter 4. However, if data in the table spans more than one year, the Quarter level contains more than four members. For example, if the Year level contains three different members, 1996, 1997, and 1998, the Quarter level contains twelve members.
The relationship between the levels and members in a Time dimension for a single year is shown in the following illustration. (Arrows pointing down indicate members that are not shown. Day members exist for each month, but are shown only for January due to space limitations.)
Using Tabular Browsers
In tabular browsers, members provide the column headings, row headings, and subheadings by which measures are separated and displayed to end users. (In graphical browsers, they provide other types of descriptive labels but serve the same function as in tabular browsers.) For example, in a Time dimension for three years, measures are separated under three headings: 1996, 1997, and 1998. If the end user drills down beneath the Year level, the members of the Quarter level are displayed as subheadings, and the measures are separated further by quarter. If the end user drills down beneath the Quarter level, the members of the Month level are displayed as subheadings beneath the Quarter level headings, and the measures are separated further by month.