Mixing MDX Drillthrough statement and SQL joins

Question

I want to use an MDX drill through command but i also want to join it to some SQL tables as well.

that is there will be an Id in the fact table and not a lot of other data I want to join this on to another table or view and produce a report based on those ID's returned in the drill down.

Ideas?

Answer 1

MDX won't do this directly. The only way I can think of doing this would be to retrieve the recordset from the drillthrough (which can be done with ADO), load it into a temporary table and join it against whatever else you want in a query.

The other option is to widen the drillthrough (if the dimensions have the data you need) and get the fields from that. Note that SSAS2005+ can support multiple drillthrough actions.

This MSDN article has some code snippets that demonstrate working with record sets returned from a drillthrough.