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?
SSAS Drillthough Limit
ssas
Best 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.'