locked
nullable foreign key in fact to dim? RRS feed

  • Question

  • I don't have SQL Server running on this computer so I cant test this case. Would someone please comment on the following sceanario.

     

    Consider the following case of a fact table that has two dimension colums and one fact column. So the columns in this fact table would be

     

    Dim_A_ID

    Dim_B_ID

    Fact_1

     

    Dim_A_ID and Dim_B_ID have FK relationships to their respective dimension tables. However, Dim_A_ID is defined as null-able. This may not be a good design to support dimensional modeling, but it's a valid relational design. So a sample data might look like

     

    Table_Dim_A

     

    Dim_A_ID,    Dim_A_Name

    1            ,    ABC

    2            ,    DEF

     

    Table_Dim_B

     

    Dim_B_ID,    Dim_B_Name

    1            ,    GHI

    2            ,    JKL

     

    Fact_Table

     

    Dim_A_ID,    Dim_B_ID,    Fact_1

    1            ,     1            ,     25

    Null        ,     2            ,     45

    2            ,     1           ,     20

     

    Notice that Dim_A_ID for the 2nd fact record is null. It's a valid relational design, but what happens to this record if these tables are used to define the data source view and the cube was generated?

     

    I can imagine 2 sceanarios.

     

    (1) I lose the 2nd record with measure amount 45

     

    or

     

    (2) The measure amount 45 is preserved and when I write an MDX statement that has something like Dim_A.CHILDREN On Rows, I get back

     

    ABC  25

    DEF  20

    Null   45

     

    Wednesday, June 4, 2008 11:25 PM

Answers

  • Take a look at this paper, which addresses data integrity issues in AS 2005. You could convert the null foreign key to the Unknown Member, in order to preserve the associated measure amount.

     

    Handling Data Integrity Issues in Analysis Services 2005

    ...

    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.

    ...

    NULL Foreign Keys in Fact Table

    The sales fact table has records in which the product_id is NULL. By default, the NULLs are converted to zero that is looked up against the product table. If zero is a valid product_id, then the fact data is attributed to that product (probably not what you want). Otherwise a KeyNotFound error is produced. By default, KeyNotFound errors are logged and counted towards the key error limit that is zero by default. Hence the processing will fail upon the first error.

    The solution is to modify the NullProcessing on the measure group attribute. Following are two alternatives:

    • Set NullProcessing=ConvertToUnknown. This tells the server to attribute the records with NULL values to the unknown member "Invalid Product". This also produces NullKeyConvertedToUnknown errors, which are ignored by default.
    • Set NullProcessing=Error. This tells the server to discard the records with NULL values. This also produces NullKeyNotAllowed errors that, by default, are logged and counted towards the key error limit. Modifying the ErrorConfiguration on the measure group or partition can control this.
    Thursday, June 5, 2008 3:14 AM

All replies

  • Take a look at this paper, which addresses data integrity issues in AS 2005. You could convert the null foreign key to the Unknown Member, in order to preserve the associated measure amount.

     

    Handling Data Integrity Issues in Analysis Services 2005

    ...

    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.

    ...

    NULL Foreign Keys in Fact Table

    The sales fact table has records in which the product_id is NULL. By default, the NULLs are converted to zero that is looked up against the product table. If zero is a valid product_id, then the fact data is attributed to that product (probably not what you want). Otherwise a KeyNotFound error is produced. By default, KeyNotFound errors are logged and counted towards the key error limit that is zero by default. Hence the processing will fail upon the first error.

    The solution is to modify the NullProcessing on the measure group attribute. Following are two alternatives:

    • Set NullProcessing=ConvertToUnknown. This tells the server to attribute the records with NULL values to the unknown member "Invalid Product". This also produces NullKeyConvertedToUnknown errors, which are ignored by default.
    • Set NullProcessing=Error. This tells the server to discard the records with NULL values. This also produces NullKeyNotAllowed errors that, by default, are logged and counted towards the key error limit. Modifying the ErrorConfiguration on the measure group or partition can control this.
    Thursday, June 5, 2008 3:14 AM
  • Thank you for the reference. I will have to play with these options.
    Thursday, June 5, 2008 1:25 PM