When you define a dimension, select one or more columns from a dimension table. If you select multiple columns, all should be interrelated such that their values can be organized into a single 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.
Each column in a dimension contributes a level to the dimension. Levels are ordered by specificity and organized in a hierarchy that allows logical avenues for drill down. For example, the Time dimension described above enables cube users to drill down from Year to Quarter, Quarter to Month, and Month to Day. Each drill-down provides greater specificity.
Each level contains members. Members are the values within the column that defines the level. For example, the Quarter level might contain four members: First Quarter, Second Quarter, Third Quarter, and Fourth Quarter. 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 values, 1996, 1997, and 1998, the Quarter level contains 12 members.
In tabular browsers, members provide the column headings, row headings, and subheadings by which measures are separated and displayed to cube users. (In graphical browsers, they provide other types of descriptive labels but serve the same function as in tabular browsers.) For example, in the Time dimension described above, measures are separated under three headings: 1996, 1997, and 1998. If the 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 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.
Each dimension table’s primary key must join to a foreign key in a cube’s fact table or another dimension table. Key columns are not required in the dimension definition.
The Dimension wizard enables you to create dimensions quickly and easily. You can use it separately to create shared dimensions, or you can invoke it while you are creating a cube with the Cube wizard to create private or shared dimensions.
Time dimensions based on datetime columns in the data warehouse are especially easy to construct with the wizard. Standard time hierarchies are available, and the levels associated with a selected hierarchy are automatically extracted from the datetime column.
Microsoft® SQL Server™ OLAP Services also supports multiple-hierarchy dimensions. These dimensions provide similar yet alternate views of cube data. For example, a multiple-hierarchy dimension for time consists of a regular calendar view and a fiscal calendar view. In OLAP Services, a multiple-hierarchy dimension is defined as two or more dimensions with names that share the same prefix followed by a period but have different suffixes (for example, dimensions with names of Time.Calendar and Time.Fiscal). The suffix should not equal any current or future level name or member name in the dimension because queries using the dimension may be ambiguous.