Avoid specifying more than one non-aggregatable attribute per dimension

This rule analyzes the attributes in each database dimension to determine whether multiple non-aggregatable dimension attributes are in the same database dimension.

Best Practices Recommendations

To increase usability, you should typically define no more than one dimension attribute to be non-aggregatable. A dimension attribute is non-aggregatable if its IsAggregatable property is set to False.

A non-aggregatable attribute has no member at the (All) level. Therefore, the attribute's default member will always be some member other than the member at the (All) level. In addition, a query will include this default member regardless of whether the attribute is expressly included in the query. This default behavior produces unexpected numbers when multiple non-aggregatable attributes have been defined. For example, you have a time dimension in which both the [Calendar Year] and [Fiscal Year] attributes are non-aggregatable. Queries for the data of a specific [Calendar Year] will be filtered by the default [Fiscal Year], unless the query expressly specifies a different [Fiscal Year]. Furthermore, [Calendar Year] and [Fiscal Year] do not align but overlap. Therefore, it is actually difficult to obtain the full data for either a [Calendar Year] or a [Fiscal Year] because the one is simultaneously filtered by the other.

To increase usability, you should define a default member for each non-aggregatable attribute.

For More Information

For more information about how to work with non-aggregatable attributes, see Configuring the (All) Level for Attribute Hierarchies in SQL Server Books Online and the section, "Optimizing the dimension design," in the SQL Server 2005 Analysis Services Performance Guide.