Credit to Andrew Wiles for finding this nasty little bug in SSAS…it sure took me a while to find any solution.
Many of us would never try to use ROLAP in SSAS…I mean it almost defeats the purpose of going the cube route in the first place (from a purist’s point of view). There are however a few instances where even proactive caching won’t be good enough, and you have to resort to using ROLAP (if you still want to build cubes).
It just so happens that I found myself in one of those (few and far between) situations. After developing and successfully processing a ROLAP dimension, I was unable to browse the dimension and its hierarchies. And you’ve got to love the error message: “The operation has been cancelled…” without any further indication of that the problem really is.
At first you obviously think that there is a problem with the way in which you’ve built the hierarchies themselves…then you start to think that it might be the dimension browser. Eventually you just become annoyed and angry that a seemingly simple object is not working, especially after changing the storage mode back to MOLAP and proving that you aren’t completely incompetent after all.
To cut to the chase, it seems like the problem is with the default error configuration property. SSAS sets this property to “custom”, and when you change it to “default” you will be able to browse the dimension (after processing it first of course) without any problems. Very intuitive, isn’t it…
More information can be found on the following MSDN post. A Microsoft connect ticket has also been created for this issue, so please vote.
Thanks a lot! I was about to throw everything out of of the window!