A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it. For example, consider a Customer dimension based on a relational source table with eight columns:

• CustomerKey
  
• CustomerName
  
• Age
  
• Gender
  
• Email
  
• City
  
• Country
  
• Region
  

The corresponding Analysis Services dimension has seven attributes:

• Customer (based on CustomerKey, with CustomerName supplying member names)
  
• Age, Gender, Email, City, Region, Country
  

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it. For Analysis Services, this specifies a natural relationship and potential aggregation. In the Customer dimension, a natural hierarchy exists for the Country, Region, City, and Customer attributes. The natural hierarchy for {Country, Region, City, Customer} is described by adding the following attribute relationships:

• The Country attribute as an attribute relationship to the Region attribute.
  
• The Region attribute as an attribute relationship to the City attribute.
  
• The City attribute as an attribute relationship to the Customer attribute.
  

For navigating data in the cube, you can also create a user-defined hierarchy that does not represent a natural hierarchy in the data (which is called an ad hoc or reporting hierarchy). For example, you could create a user-defined hierarchy based on {Age, Gender}. Users do not see any difference in how the two hierarchies behave, although the natural hierarchy benefits from aggregating and indexing structures — hidden from the user — that account for the natural relationships in the source data.

The SourceAttribute property of a level determines which attribute is used to describe the level. The KeyColumns property on the attribute specifies the column in the data source view that supplies the members. The NameColumn property on the attribute can specify a different name column for the members.

To define a level in a user-defined hierarchy using Business Intelligence Development Studio, the Dimension Designer allows you to select a dimension attribute, a column in a dimension table, or a column from a related table included in the data source view for the cube. For more information about creating user-defined hierarchies, see Creating User-Defined Hierarchies.

In Analysis Services, an assumption is usually made about the content of members. Leaf members have no descendents and contain data derived from underlying data sources. Nonleaf members have descendents and contain data derived from aggregations performed on child members. In aggregated levels, members are based on aggregations of subordinate levels. Therefore, when the IsAggregatable property is set to False on a source attribute for a level, no aggregatable attributes should be added as levels above it.

The main constraint when you create an attribute relationship is to make sure that the attribute referred to by the attribute relationship has no more than one value for any member in the attribute to which the attribute relationship belongs. For example, if you define a relationship between a City attribute and a State attribute, each city can only relate to a single state.

You can use MDX queries to retrieve data from attribute relationships, in the form of member properties, with the PROPERTIES keyword of the MDX SELECT statement. For more information about how to use MDX to retrieve member properties, see Using Member Properties (MDX).

Concepts

Other Resources

Help and Information