It’s been quite a while since I’ve posted here because I’ve been extremely busy in a release cycle, but I thought I would post an update on the custom drillthrough that I have been working on.  Unfortunately, I do not have the completed code yet.

A few weeks ago, I had a conversation with Chris Webb regarding the use of a stored procedure to implement customized drillthrough in Analysis Services 2005.  We had discussed a couple of ways to implement a solution that would query the underlying database to return drillthrough records. 

One method was to capture the current members that made up a cell and pass those members to a Reporting Services report which would call a stored procedure and return records from the underlying database.  The other method was to capture the current members of a cell and use the metadata for those members (via AMO) to construct a SQL statement that could be passed to RS which would use a stored procedure to return the drillthrough records. 

I spent some time working on the SQL statement solution and got it working, but ran into a couple of problems that made me regret choosing this path.  First, not all client applications handle actions the same way.  For instance, if I were to construct a query by dragging and dropping my dimensions in Excel or the cube browser in SQL Server Management Studio, the queries returned immediately.  However, when using ProClarity Desktop, my queries were much slower.  I used SQL Server Profiler to see what was causing the holdup and found that a connection to Analysis Services would be opened and closed for every cell that was returned to the client.  So, if I had 300 cells that made up a result set, 301 connections would be opened on the server (1 connection for the original query + 300 connections for the cells in the result set).  This occurred because my stored procedure used AMO to read the metadata for the current members.  This was puzzling because with Excel and the cube browser, these connections would not be opened unless the drillthrough action was triggered when I right clicked on a single cell…which is what I intended. 

Another major flaw with this method was passing a SQL statement via the URL to Reporting Services.  This left a big security hole where the SQL in the query string could be manipulated.  Obviously, there are ways around this but the more I thought about this solution, the more I disliked it.

Needless to say, I’m going to pass on creating the SQL statement from within the Analysis Services stored procedure.  My new plan involves two stored procedures, one in Analysis Services and one in SQL Server.  I figure if I can pass the current members to Reporting Services, then I can call a custom assembly in SQL Server that will query AMO and get the metadata for the current members.  After I get this metadata, I can construct a SQL statement in my SQL Server assembly, execute the statement, and then return the records.

I’m still pinched on time, but will post my results as soon as I get them completed.