On a fairly regular basis, customers I work with wonder out loud (read: gripe) why SSAS 2005 and 2008 don’t allow them to easily drill-down to the relational tables that back a cube using an action. SSAS 2000 did this nicely, but SQL Analysis Services 2005 and 2008 want to return rows from the cube vs. directly from the tables.

There is a sort-of-but-not-too-painful workaround available which encompasses using ROLAP partitions which point to the drill targets in question, but it’s still kind of inconvenience to deal with because you may end up setting up quite a few ROLAP partitions to support everything you need to do.

Brian Knight has come up with a really nice, clean solution which leverages a helper assembly to do the same thing. I like this approach much more because once you’ve installed the assembly, creating drill-through actions is quite easy. Thanks, Brian!

http://blogs.pragmaticworks.com/brian_knight/2009/09/creating-a-ssas-rowset-action.html

http://blogs.pragmaticworks.com/brian_knight/2009/09/calling-an-external-query-from-mdx.html