UnknownMember is set to hidden for one or more dimensions

Article ID: 2027628 - View products that this article applies to.
Expand all | Collapse all

SUMMARY

The UnknownMember property for an Analysis Services dimension is used to determine whether the unknown member of a dimension is visible, hidden, or disabled (Visible, Hidden, or None). The unknown member of a dimension can be used to store values for facts which had missing or unknown dimension key values at the time a partition was processed. Setting the UnknownMember property to hidden for a dimension is generally not recommened as it can result in query results that appear not to aggregate correctly when there are facts associated with the UnknownMember. This occurs because the totals returned for a query include the values from the visible members as well as the hidden UnknownMember. If the user doesn't realize that the UnknownMember is hidden they may think that the totals for the query are incorrect. Setting the UnknownMember property for dimensions to Visible or None is the recommended best practice, as it can help identify referential integrity problems with the source data or incorrect processes in the Extraction Transformation and Loading (ETL) processes for the data warehouse. A value of Visible provides a visual cue after processing by surfacing the UnknownMember in query results, while setting the property to None will result in processing errors when the dimension key from a fact record is missing or cannot be matched to an existing member in the dimension.

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:

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

UnknownMember is set to hidden.





The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect dimensions with an UnknownMember type property value that is not set to "Hidden". If you run the BPA tool and get the warning message "UnknownMember set to hidden", then you should consider changing the UnknownMember property type to a value of "Visible" or "None". To identify a list of databases and dimensions that triggered the BPA warning, change the Report Type from 'Results' to 'Collected Data' and expand the "UnknownMember_set_to_hidden" node.SQL Server 2008
SQL Server 2008 R2



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

UnknownMember is set to hidden.





The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect dimensions with an UnknownMember type property value that is not set to "Hidden". If you run the BPA tool and get the warning message "UnknownMember set to hidden", then you should consider changing the UnknownMember property type to a value of "Visible" or "None". To identify a list of databases and dimensions that triggered the BPA warning, change the Report Type from 'Results' to 'Collected Data' and expand the "UnknownMember_set_to_hidden" node.SQL Server 2012 






Properties

Article ID: 2027628 - Last Review: April 2, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2008 Analysis Services
Keywords: 
KB2027628

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com