How do I stop refresh conflicts between Proactive Caching of facts and timed refresh of Dimensions in a SQL Server Analysis Service 2005 OLAP cube?

Question

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).

Answer 1

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.

Answer 2

We've decided to re-engineer our dimensions so we can enable proactive caching for them in addition to the cube partitions.

From what I've read, proactive caching is a two-part, single process.

1. First, you poll your data source to find which objects need to be reprocessed
2. Second, you run a processing query which loads the missing data.

SSAS does this using an object dependency order so new dimension values should be loaded before the fact records dependent on them are loaded, thus avoiding conflicts.

Care should be given setting up the polling and processing queries for proactive caching. The more efficient those queries are, the more real-time your cube updates will be.

I'll follow-up if this solution works effectively for us. Hopefully this will help others have the same or similar problems.