Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
Interesting solution that will keep the cubes from going offline but the result, if I understand you correctly, is records which have "unknown" for dimension values. So, while the data may be in the cube, the users may not be able to find it and may not know that its missing in summarized data reports. –  ScottCher Mar 2 '11 at 17:18
    
In addition, unless we specifically alert on this condition, we won't know this has happened. At least with our current solution, we know when the cube has stopped updating so we can manually reprocess the dimensions that need it and the users see updated data as soon as we do. To my knowledge, there are no triggers in SSAS that would alert us to creating "unknown" values because of dimension issues. There is logging, but we'd have to monitor the log files. Please correct me if I'm wrong here, I'm certainly not an expert. –  ScottCher Mar 2 '11 at 17:20
    
+1 its a viable solution if you don't mind the drawbacks of having "unknown" show up for missing dimension values. I appreciate the feedback –  ScottCher Mar 2 '11 at 17:21
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.