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.

There is a limit of 10,000 rows that can be returned for a drillthrough rowset in recent version of SSAS. I'm concerned about the risk of of returning partial results without warning if the selection set contains more data than the 10K limit. Is there some mechanism to either display a warning or return no rows at all in that case?

share|improve this question
    
I know that in a linked pivottable in Excel you get a warning that only the top 10k results are being displayed. –  JNK Feb 11 '13 at 15:47
    
I have Excel 2010 here and I can't reproduce that. –  Jeff Sacksteder Feb 11 '13 at 16:03
    
I've tested on 2007 –  JNK Feb 11 '13 at 16:04
    
I see that you get that warning in the selection menu. You still have access to all the items if you search, the gui control can't handle more than that, though. –  Jeff Sacksteder Feb 11 '13 at 16:05
add comment

1 Answer

MDX Drillthrough allows you to specify a limit for the row set returned. As far as I am aware it doesn't have a 'show how many rows this would produce' facility. You could fake it by having a count measure for each of your fact tables and selecting this for the slice in question before you execute the drillthrough.

I'm not sure whether Excel 2010 has much in the way of facilities to manage this out of the box. Back when I were a lad Excel 2000-2003 didn't do drillthrough out of the box so you had to roll your own. This is an example of a VBA script to do a drillthrough action. You can hook the menu on the pivot table fairly easily to provide access to this sort of script. This would at least allow you to provide some sort of validation.

Introspection into the pivot table through the API is possible if a little fiddly. You can get the dimension and slicer state out. Back in the SQL Server 2000 days I did a 'drill across' this way that allowed you to open another cube on the same slice (think insurance policies going across to claims). Now SSAS supports cubes with multiple fact tables (measure groups) this is less useful.

However, if you could intercept the (IIRC) double click event that would kick off a drillthrough and knock that on the head then you could use the introspection to formulate a count query that could guard against over-large drillthroughs. This would then pop up a dialog box along the lines of 'You're going to drill through into 1,347,961 rows.'

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.