I received an e-mail from a friend at one of our clients asking about storage options for their Analysis Services 2005 cube and what was the best for their scenario. Apparently he had just been to a presentation and he walked away confused over the argument for/against the ROLAP storage method and where it should be used.

His confusion lay in the fact that the speaker kept referring to the advantages of ROLAP for real-time data updates while preserving aggregations. The presentation had gone on in length about caching models and user response time in a manufacturing plant scenario; plus a few other inconsistencies in the speaker’s presentation, my friend walked away befuddled over this issue.

I first pointed him to Microsoft’s definitions for SQL 2005 and 2008 on the storage settings in order for him to have a solid definition reference– they are not different, but best to keep both bookmarks in case anything does change for 2008, and then we spoke over why he was looking at this type of implementation – and we walked right into one of my major pet peeves in presentations.

The speaker for this incident was giving his presentation to a very specific audience: my friend, his colleagues and a few business analysts, who all work at the same company – a bank. The speaker had gone on about the advantages of ROLAP using both the manufacturing plant scenario and then retail sales. While you can make a case for the retail sales portion of the demo, he had already lost a lot of the audience by starting with manufacturing, relating it to a distribution chain for the retail outlets and back again.

I asked my friend why the interest in real time reporting and got the answer I have frequently heard: "Our exe read this article and thought we needed to be more dynamic, so asked us to look into this." Scott’s writing a new Dilbert on this one right now. After we both had a chuckle, we walked through his scenario – a basic fact emerged that my friend would take back to his manager: they have no real time feeds for their reports currently, nor will they in the near/far future.

It made an easy argument back to the execs on why they could not implement– but also forced a decision point to them how they wanted to manage their business process. Without going into too much detail, one of the central roadblocks in my friend’s scenario had to do with critical information for their analysis that they received from their 3rd party vendors and service providers. Without working with these two groups to resolve more timely data, they would not be able to approach even near real time for a while.

Now, at least, my friend is working on answering the question of: ‘is this even a necessary situation, or can they achieve the needed analysis for the business with the current state?’

As you may have noticed, I have neither disdained ROLAP, nor sung its praises. I prefer to look at this in terms of situations and what is the appropriate solution for a given opportunity. Microsoft put this storage option in for a good reason in Analysis Services 2005 and has preserved it in 2008 as they obviously see a need for it to be there.

As with any implementation though, good planning against a clearly laid out set of goals is always the best way to start.