Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Non-numeric key column used for high cardinality attribute

Non-numeric key column used for high cardinality attribute

Summary

Analysis Services allows an attibute to use different columns as the source for the name and key values of an attribute. The name column is used for display of the dimension members, while the key column is for lookups and indexing of the attributes. As the number of attribute values increases, the performance difference between numeric and non-numeric key column querying and retrieval becomes more noticable. For this reason it is recommended that attributes having more than 500,000 values should use a numeric column for the attribute key column. This is similar to the concept of using a surrogate key in a relational table to improve indexing performance. 


↑ Back to the top

More Information

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated 
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)

The key column for an attribute containing 500000 or more members is a non-numeric data type.The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect dimension attributes with a member count over 500,000 and a key column that is a non-numeric data type. If you run the BPA tool and get the warning message "Non-numeric key column for high cardinality attribute", then you should examine the identified dimension attribute and change the key column to a numeric data type if possible. To identify a list of databases, dimensions and attributes that triggered the BPA warning, change the Report Type from 'Results' to 'Collected Data' and expand the "Non-numeric_key_column_for_high_cardinality_attribute" node.SQL Server 2008
SQL Server 2008 R2




SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)


The key column for an attribute containing 500000 or more members is a non-numeric data type.The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect dimension attributes with a member count over 500,000 and a key column that is a non-numeric data type. If you run the BPA tool and get the warning message "Non-numeric key column for high cardinality attribute", then you should examine the identified dimension attribute and change the key column to a numeric data type if possible. To identify a list of databases, dimensions and attributes that triggered the BPA warning, change the Report Type from 'Results' to 'Collected Data' and expand the "Non-numeric_key_column_for_high_cardinality_attribute" node.SQL Server 2012 








↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2028138
Revision : 1
Created on : 1/7/2017
Published on : 4/2/2012
Exists online : False
Views : 208