Null Processing of FK in Fact table doesnt work when add referenced dimension
-
I have a User table (My fact table) that has a foreign key called CustomerID. CustomerID can be NULL.
I have a dimension called customer with Primary Key called CustomerID.
The Customer and User table are linked by CustomerID in the DSV.
My dimension processes the Nulls correctly as I designed it per MS best practice.
From MS Best Design Practices.
Do include the key columns of snowflake tables joined to nullable foreign keys as attributes that have NullProcessing set to UnknownMember
If tables that are used in a dimension are joined on a foreign key column that might contain nulls, it is important that you include in your design an attribute whose key column is the corresponding key in the lookup table. Without such an attribute, the OLAP server would have to issue a query to join the two tables during dimension processing. This makes processing slower; moreover, the default join that is created by the OLAP server would exclude any rows that contain nulls in the foreign key column. It is important to set the NullProcessing option on the key column of this attribute to UnknownMember. The reason is that, by default, nulls are converted to zeros or blanks when the engine processes attributes. This can be dangerous when you are processing a nullable foreign key. Conversion of a null to zero at best produces an error; in the worst case, the zero may be a legitimate value in the lookup table, thereby producing incorrect results.
To handle nullable foreign keys correctly, you must also set UnknownMember to Visible on the dimension. The Cube Wizard and Dimension Wizard currently set this property automatically; however, the Dimension Wizard lets you manually de-select the key attribute of snowflake tables. You must not deselect the key column if the corresponding foreign key is nullable.
If you do not want to browse the attribute that contains the lookup table key column, you can set AttributeHierarchyVisible to False. However, AttributeHierarchyEnabled must be set to True because it is necessary that all other attributes in the lookup table be directly or indirectly related to the lookup key attribute in order to avoid the automatic creation of new joins during dimension processing.
I have another table called Geography that has primary key of ZIP that links to the customer table FK of ZIP.
When I create a Dimension of Customer Geography it is a REFERENCED dimension with the Customer table with attributes of zip and is materialized.
PROBLEM: when i add this dimension my NULL processing of the CustomerID no longer appears to work. It seems that the SQL behind the scenes is creating an Equal join of Customer.CustomerID = User.CustomerID thereby eliminating all the records in the user table without a customerid.
What am i doing wrong.
Any help would be greatly appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-
While not exactly an answer to the question asked, you could work around this by not having a null FK. ie have a value of say -1 in the FK, then include an 'unknown/not specified' record in the Customer table, which would then relate (again using a -1 FK) to the PK of the geography table (which also would include a PK [-1] record for the 'Unknown/not specified' geo value). Then the use of equi joins across the tables would always produce a full dataset.
Just out of interest, have you swt up the same NullProcessing approach for the Customer to Geo relationship?
Steve.
-
I did set up the NULL processing on the Geo and Cust relation too.
I did alot of research last night and indeed your suggestion appears to be the one.
I also found that other people were experiencing the same issue:
" could do that. It's what I would have to do if were were using SSAS2000. I
suppose another alternative would be to use a Named Query as the cube's fact
table, make the query a left outer join between the real fact table and the
customers table, and treat both Customers and Territory as star dimensions
instead of snowflake dimensions. But I would prefer to avoid doing that if
there is some setting I can make which will fix the problem.
If there isn't such a setting, then I would suggest that this is a design
problem. Obviously SSAS2005 is meant to be able to handle missing and null
keys and in most situations it does this very well. However, I would really
like to hear a definitive answer from Microsoft.
In a situation like what we have, where some keys in the fact table to a
dimension are null, and that dimension is also used to join a snowflake
dimension to the fact table, what is the expected behaviour? Can I change it
so that fact table rows aren't filtered?
"
Note the last reply in the link above says to uncheck the materialized view on the reference dim is NOT the correct solution. It does process the NULL correctly but it does not do the aggreagates as it should.
I did read somewhere this was submitted MS as a bug.
I hope this helps someone else out
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply