Problem with ROLAP dimension updates

Simple scenario:
Cube based on a single table. (FactContacts)
One HOLAP count of rows measure.
One ROLAP fact dimension with an attribute based on a nullable string column. (Status)

Everything works fine as long as I process the cube manually through VS. However, I often get the following error if I rely on the cube to update automatically as my source table changes. (Interestingly the error doesn't always occur.)

The query could not be processed:
o Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_FactContacts, Column: Status, Value:aaaa.
o Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

My first guess was that things are getting updated in the wrong sequence (measure before dimension), so I've tried changing the ProcessingPriority properties on the dimension and on the measure group but that didn't have any effect.

Any ideas?
I should probably also clarify I have proactive caching enabled, both on the dimension and on the measure group. My notification method is SQL Server.

I'd really like to know if this could possibly be a bug of SSAS, or if I'm just missing something. This problem is pretty much a showstopper for my project, as real-time data is a requirement.

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.