Problem with ROLAP dimension and UnknownMember

I've set up a simple test cube with one fact table, "FactContacts" and one dimension table, "DimStatus". I also have a degenerate dimension "Contact ID" based on a column in the fact table. The only measure is the count of rows in "FactContacts". I've also set up a drillthrough on this measure group to return "Contact ID" and "Status". The "StatusID" column in the fact table allows nulls, so the UnknownMember is enabled on DimStatus and null processing is set to UnknownMember in the dimension usage tab.

Everything works fine as long as my dimensions are set to MOLAP mode. However, when I set them to ROLAP, it seems the nulls are not handled properly. When I slice on "Status" the Unknown count is non-zero, but the drillthrough doesn't return any rows. When I slice on "Contact ID" the count of Contacts for each ID is 1, but when I drillthrough only the cells that correspond to Contacts with a non-null StatusID return a row.

Essentially, the drillthrough does an inner join on the tables while I want it to do an outer join. Is there a way to achieve this? Any help will be greatly appreciated.

There is a similiar issue with materializing referenced dimensions. I'm not aware of a work around for this.

Our general recommendation to folks to to make sure your references are always populated. (Set your foreign key fields to NOT NULL.) Of course, you may have a reference to a NULL member in a dimension. You can easily support this as demonstrated in the following example:

create table DIM_Date (

DateID int not null identity(1,1),

Date datetime null

)

alter table DIM_Date add

constraint PK_Date primary key (dateid),

constraint AK_Date unique (date)

set identity_insert DIM_Date on

insert into DIM_Date (DateID, Date) values(-1, NULL)

set identity_insert DIM_Date off

In this example, any NULL date references would be set to a foreign key value of -1. Your inner joins between your fact table and your dimension table will always resolve correctly.

B.

|||Thanks for your reply Bryan. I was hoping to avoid this workaround but I guess this is what I'll have to do.