How to display unknown data of fact table? RRS feed

  • Question

  • I have the following question: When there is a fact and dimension tables with a regular relationship and the fact table has some records that are not covered by this relationship, is there a way to display the data as unknown?

    In my case there is fact table "A" with "AppId" column and dimension table "B" with "AppId" PK, there is no relationship between the 2 tables, because not all "AppId" fields of table A exist in table B. Is there a way to display ALL the data from fact table A, including these records with unexistent "AppId" field?  


    Friday, December 29, 2006 5:08 PM


All replies

  • If you're using AS 2005, you can configure an "Unknown" member for dimension "B", and select appropriate Error Configuration properties when processing:


    Microsoft SQL Server 9.0 Technical Articles 
    Handling Data Integrity Issues in Analysis Services 2005 

    T.K. Anand
    Microsoft Corporation

    March 2005

    Summary: Learn about common data integrity issues, and see how Analysis Services 2005 gives you the tools to handle them.


    Unknown Member

    The Dimension object has a property called UnknownMember that takes three possible values—None, Hidden, Visible. When UnknownMember=Hidden/Visible, the Analysis Server automatically creates a special member called the Unknown Member in every attribute of the dimension. UnknownMember=Hidden indicates that the unknown member will be hidden from query results and schema rowsets. The default value of UnknownMember is None.

    The UnknownMemberName property can be used to specify a meaningful name for the unknown member. The UnknownMemberTranslations property can be used to specify localized captions for the unknown member.


    Error Nomenclature

    Before we discuss the Error Configuration control, we need to clearly define the different types of data integrity errors that the server can encounter. We have already learned about two of them in the previous section on Null Processing. Following is the complete list:


    • KeyNotFound—This is the classic referential integrity error in relational databases. It can be encountered during partition as well as dimension processing.

    Error Configuration

    The ErrorConfiguration object is central to the management of data integrity errors. The server comes with a default error configuration (in the msmdsrv.ini config file). The error configuration can also be specified on the database, dimension, cube, measure group and partition. In addition, the error configuration can also be overridden on the Batch and Process commands.

    The ErrorConfiguration object specifies how the server should handle the four types of data integrity errors. It has the following properties:


    • KeyErrorAction (Default=ConvertToUnknown)—This is the action that the server should take when a KeyNotFound error occurs. It has two options:
    • ConvertToUnknown—tells the server to interpret the offending key value as the unknown member.
    • DiscardRecord—tells the server to discard the record. This is how Analysis Services 2000 handles KeyNotFound errors.



    In this section, we will discuss various scenarios involving data integrity issues and show how the controls described in the previous section can be used to address them. We will continue to use the relational schema specified earlier.

    Referential Integrity Issues in Fact Table

    The sales fact table has records with product_id that does not exist in the product dimension table. The server will produce a KeyNotFound error during partition processing. By default, KeyNotFound errors are logged and counted towards the key error limit, which is zero by default. Hence the processing will fail upon the first error.

    The solution is to modify the ErrorConfiguration on the measure group or partition. Following are two alternatives:

    • Set KeyNotFound=IgnoreError.
    • Set KeyErrorLimit to a sufficiently large number.

    The default handling of KeyNotFound errors is to allocate the fact record to the unknown member. Another alternative is to set KeyErrorAction=DiscardRecord, to discard the fact table record altogether.



    Friday, December 29, 2006 6:56 PM
  • First of all thanks for your reply. I defined Unknown member for measure group as described in article above and it does work for A and B tables.
    Unfortunately I still have a problem.
    I have a third table "C" that is a Referenced dimension (B is Intermediate dimension), when I add this referenced dimension to cube it stops show unknown members of measure group, although I defined "C" UnknownMember to visible and "B" ErrorConfiguration to custom.

    (I'm using
    AS 2005)

    Wednesday, January 3, 2007 3:25 PM
  • Based on this past post from the SQL Server OLAP newgroup, this seems to be a known issue (don't know if/when it's being fixed). You can confirm this by not making Dimension "C" materialized - though I don't know whether performance will still be OK:


    microsoft.public.sqlserver.olap > Repost - referenced dimension causes fact table to be filtered

    I posted a reply to your original post -- basically turning off the
    Materialized option on the reference dimension should help.

    I do agree that this is a bit of a flaw in materialized reference dimensions
    and I have filed a DCR to solve it -- unfortunately it isn't an easy change.



    Wednesday, January 3, 2007 4:27 PM