Dimension Enhancements (Analysis Services)

Microsoft SQL Server 2005 Analysis Services (SSAS) includes the following dimension enhancements and new features.


In earlier versions of Analysis Services, dimensions were based directly on the levels in a hierarchy. In SQL Server 2005 Analysis Services, dimensions are now based on attributes, which correspond to the columns in the tables of a dimension. Each attribute contains the members of a dimension table column, such as Cities or Locales in a Geography dimension, or Days or Months in a Time dimension. This new architecture separates the structural features of a dimension from its navigational features; attributes provide the structure of a dimension and the levels being used for navigation of the dimension. For more information, see Attributes and Attribute Hierarchies.

Multiple Hierarchies

Analysis Services now supports multiple hierarchies in a single dimension. Previously, hierarchies for a dimension were in fact separate dimensions, related to the main dimension only by an identifying naming convention. In SQL Server 2005 Analysis Services, however, dimensions are no longer described by their hierarchical structure. Instead, attributes that can easily be assembled into hierarchies are used, and different hierarchical structures can be supported within the same dimension. For more information, see User-Defined Hierarchies.

Many-to-Many Dimension Relationships

Analysis Services now supports many-to-many relationships between fact tables and dimension tables by using association tables. Many-to-many dimension relationships expand the dimensional model beyond the classic star schema, and support complex analytics even when dimensions are not directly related to a fact table. For more information, see Dimension Relationships.

Reference Dimension Relationships

Analysis Services supports reference dimensions through the use of reference dimension relationships, in which a reference dimension is indirectly coupled to a measure group by another dimension. Using reference dimension relationships, you can associate a reference dimension with a cube without creating a snowflake dimension. You can chain any number of reference dimensions together. For more information, see Dimension Relationships.

Fact Dimension Relationships

Analysis Services now supports fact dimensions, also known as degenerate dimensions, through the use of fact dimension relationships. A fact dimension is a dimension whose attributes are drawn from a fact table. For more information, see Dimension Relationships.

Role-Playing Dimension Relationships

Analysis Services now supports role-playing dimension relationships, in which multiple relationships between a dimension table and a fact table can be expressed by using a single dimension. In earlier versions of Analysis Services, each relationship between a fact table and a dimension table required a separate dimension. For more information, see Dimension Relationships.

Simplified Dimension Types

In Analysis Services, dimensions have been simplified: two dimension types, standard and linked, now replace the four dimension varieties in SQL Server 2000 Analysis Services. A standard dimension is a dimension in the same database as the cube; a linked dimension is in a different database from the cube. Additional dimension characteristics are now supported through dimension relationships, hierarchies, and attributes. For more information, see Dimensions (Analysis Services).

Linked Measure Groups and Dimensions

In Analysis Services you can bring together data from different data sources by linking a cube to a measure group in another cube that is stored either in the same database or in a different database on an instance of Analysis Services. You can also link a cube to a dimension in another database. After you create a link, users can query data and metadata in the linked object, just as they do in any similar object that is native to the cube. For more information, see Linked Measure Groups and Linked Dimensions.

Member Groups No Longer Required

In SQL Server 2000 Analysis Services, member groups were necessary to accommodate members with more than 64,000 children. Analysis Services now removes this restriction; member groups are no longer necessary.

Dimension Size Virtually Unlimited

Analysis Services no longer depends on memory-resident storage of dimensions. Now, dimension data and metadata is loaded into memory only when it is needed, and the Analysis Services engine can easily handle dimensions of virtually unlimited size.

Community Additions