I have a SSAS 2005 OLAP cube which we've very nearly made into a ROLAP cube using a mixture of proactive caching for the fact partitions and timed refreshes for the dimension data.
There are two fact partitions – one containing yesterdays snapshot and one containing intra-day changes to that snapshot. That's how we're able to achieve near real-time cube updates within SSAS (which Microsoft has stated is not suitable for real-time).
So, proactive caching updates the fact data (with sub-second timing) but is not set to update the dimension data. About 10% of the dimensions receive updates intraday so we are continually refreshing them using a SQL Server process that runs every 2 minutes.
We are seeing cube corruption, periodically, requiring a full-reprocess of the cubes (taking several hours in some cases). We believe this corruption is caused when proactive caching happens to update a fact while a dimension update is happening (perhaps when a new fact, requiring a dimension value that doesn't exist yet, is being added). We don't know for sure because SQL Server and SSAS 2005 provide little no useful trace information for these exceptions.
Our short-term solution has been to turn off the dimension updates entirely. Proactive caching still updates the facts and keeps our near real-time updates flowing until a new fact requiring a new dimension value comes in. At that point, the cubes stop updating and we manually determine the faulty dimension and manually re-process it.
So, what we are looking for is a way to avoid having to manually update the dimensions (which causes an outage to users).
Best Answer
Sounds like you're getting orphaned fact rows a.k.a. early-arriving facts, meaning you have facts records that don't yet have a corresponding dimension member in one of the dimensions.
There's not really a way to solve this with the approach you're using because it's sounds like you'll always been a in situation where your dimensions are lagging your facts. But you can work around it by changing your dimension key error options in your fact processing. There is an option to ignore dimension keys not found. What you want to do is set that to "Ignore" and you might have to play around with some of the error limit options as well.
This should do the trick. Not the prettiest of solutions, but it will work and prevent these outages.