Recently, I was setting up drillthrough on a degenerate dimension with ROLAP storage. I encountered the error below when executing the drillthrough action.
Using SQL Server profiler, I was able to locate the cause of the issue. Looking at the trace, I noticed that the ‘IntegerData’ trace field was failing at row 310,000 so I looked at server properties to see which ROLAP related properties were set to 300,000.
The culprit turned out to be ‘ROLAPDimensionProcessingEffort’ which was set to 300,000. So, as a test, I set the property to 2,000,000 (a number higher than the number of members in my degenerate dimension) and this resolved the error.
Although changing this property resolved the error, the cost of using the ROLAP storage for this dimension turned out to be quite expensive. My greatest concern was that a huge number of dimension members were being queried and cached when I was essentially targeting only 30 records for drillthrough.
I had posted this problem to an internal message board at Microsoft and was informed that “…client applications may mistakenly try to fetch more data than they really wanted to from ROLAP dimensions…”. This is why the ‘ROLAPDimensionProcessingEffort’ property exists.
Needless to say, I’m not impressed with ROLAP dimensions and this one more reason to avoid using them if you can.