Another gotcha in Sql Server Analysis Services (SSAS) 2005 related to nullable foreign keys...

Let's say you have DimensionA which contains a foreign key to the related dimension DimensionB and that relationship is optional (nullable foreign key).  DimensionB is represented as AttributeB within DimensionA.

When I first created DimensionA and related it to my fact table, everything worked nicely.  I then decided to have the name column for AttributeB show the text value for DImensionB's key rather than they key itself.  I figured this would make things go more quickly when building reports. I used object binding on the NameColumn and set the source table and source column.  Suddenly I started getting the error "The attribute key cannot be found" when I tried to process the cube.  Huh?

I checked my source tables and of course the key did exist.  I finally realized that I could examine the key values being stored for DimensionA and indeed the key was missing, even though it was present in the source data.

Looking at the source data I also noticed that this particular key had a null value for AttributeB.  It became clear that rather than looking up the values for the name column, SSAS was creating an inner join between the tables used for DimensionA and DimensionB and in the process dropping all the rows with a null value in that related field.   Arg!

Getting rid of the object binding for AttributeB's name column fixed the problem.

SSAS nullable foreign keys dimensions