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. 


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 








गुण

आलेख ID: 2028138 - पिछली समीक्षा: 02/04/2012 - संशोधन: 1

प्रतिक्रिया