Share via

  • Article

Either disable the attribute hierarchy or set the AttributeHierarchyOptimizedState property to NotOptimized for non-key attributes that have 1,000,000 or more members and a cardinality within 95% of the key attribute

This rule analyzes enabled attribute hierarchies in each dimension with an estimated count of more than 1 million members. This analysis determines whether there are enabled attributes that have an estimated count greater than 95% of the estimated count of the key attribute in the dimension. Attributes that have an estimated count that is greater than 95% of the estimated count of the key attribute have high cardinality, that is, an almost 1-to-1 relationship with the key attribute.

Note

An enabled attribute has its AttributeHierarchyEnabled property set to True.

Best Practices Recommendations

For best performance, attributes that have high cardinality should typically be configured as member properties instead of as browsable attributes. To configure an attribute to be only visible as a member property, set the AttributeHierarchyEnabled property of the attribute to False.

Enabling attribute hierarchies on high cardinality attributes will generally cause poor query performance and will not generally return useful query results. Furthermore, high cardinality attributes are usually not interesting to pivot on or group as few members share values. For example, a telephone number might be interesting to see as a member property for each customer. However, being able to pivot and group based on telephone number is not valuable.

If you still want to browse attributes that have high cardinality, consider setting the following attribute properties in addition to the AttributeHierarchyEnabled property:

  • Set the AttributeHierarchyOptimized property to NotOptimized.

  • Set the GroupingBehavior property to DiscourageGrouping.

This will improve performance and provide clients information that the attribute is not very useful for grouping.

For More Information

For more information about attribute properties, see Defining and Configuring Dimension Attributes in SQL Server Books Online.

For more information about performance issues related to attributes that have high cardinality, see the section, "Reducing attribute overhead," in the SQL Server 2005 Analysis Services Performance Guide.