One of the subtle changes made to PowerPivot with Denali is that it now supports drill-through actions in Excel. In SQL Server 2008 R2 PowerPivot we get the following message:
In Denali PowerPivot supports drill-trough in Pivottables. Let’s say I have the following Pivottable where I want to see the underlying rows of data that make up this sum of salesamount:
I can double click on the measure and it will open a new sheet in Excel that shows you the underlying rows of data in the fact table:
Notice that the rows are filtered by the values that combine the measure that we clicked on.
By default the drill-through will return only the first 1000 rows, but this is configurable in the connection, go to Excel, Data, Connections:
Select the PowerPivot Data connection, this is the connection that is made to the in memory, internal SSAS engine that runs inside PowerPivot.
In this dialog you can change the number or records you want to retrieve in the Drill through.