Drillthrough on calculated measures

Drillthrough is a great feature of Analysis Services, but it has a limitation – only cells without calculations covering them can be drilled through. This automatically rules out drillthrough for calculated members in general and calculated measures in particular. This limitation has been a subject to much debate in forums and blogs. It has been logged on connect site as “Enable drillthrough on calculated measures” suggestion, where it gathered strong support. And competition struggles with very similar issues as well.

While there is no universal solution to this problem, in this article we will show how many practical particular cases can be solved in AS2005. Let’s first understand what does it mean to drill through on calculated measure. According to the definition:

“Drillthrough is the operation in which a user specifies a single cell and the Analysis server returns the most detail level data that contributed to that cell.”

For the regular cells it is pretty straightforward to figure out which detailed level data contributed to them, but in case of arbitrary calculation, it can be very tricky. Basically, one needs to reverse engineer the expression of the calculation, find out all the dependencies, and drill through to each one of them. But in case of simple calculations, such dependencies are obvious. Let’s consider the [Internet Gross Profit] calculated measure from the Adventure Works cube.