SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSAS – Hiding Unknown Member

You may have noticed when creating Analysis Services dimensions that a member is created in all of your attributes that doesn’t actually exist in your data source.  The member that is created is the Unknown member shown below. 

The purpose of the Unknown member is to handling errors that occur during processing.  For example, if you have a Fact table that has foreign keys pointing to your dimension that does not have the associated primary keys then those rows would land under the Unknown member.  You have the option to set during processing for rows with errors to land underneath this Unknown member.  Really doing this is a way of hiding things that are either bad with your source data or with your cube design so shouldn’t be done on a regular basis. 

If you would like to hide the Unknown member so it’s not visible to your users you can do so by changing a dimension property.  Select the dimension name inside the Attributes pane of the dimension designer, as shown below.

You will need to change one of the dimension’s properties by hitting F4, which will open the properties of the selected dimension.  Change the UnknownMember property to Hidden, which will hide the Unknown member that appears when browsing the dimension.

If you are using the unknown member you can also change the default name of it from “Unknown” to whatever you would like by changing the UnknownMemberName property.  After reprocessing you should see the Unknown go away.

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by rajasuman.c on 17 January 2011

Nice tip

Leave a Comment

Please register or log in to leave a comment.