Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

9.5. Drillthrough

Drill down is the process of navigating from a summary level to more detailed levels across a cube dimension. Drillthrough is a completely different animal. Drillthrough retrieves fact data corresponding to a cell or some specified range of cells. Often the lowest level of detail in a cube is still comprised of aggregated values, but users occasionally have a need to see the associated row-level data from the fact table. In Analysis Services 2008, even if you use the MOLAP storage mode (discussed later in this chapter), you can still use Drillthrough. You can modify a server configuration advanced property, OLAP\Query\DefaultDRILLTHROUGHMaxRows, to control the default size of the returned dataset.

By default, Drillthrough returns the granularity attribute of each dimension and all measures. If you want your Drillthrough action to behave like Drillthrough in Analysis Services 2000, you can create a ROLAP dimension from the fact table that contains the measures to return. And just how do you define Drillthrough? You could create an application that performs Drillthrough programmatically using the SQL query supported by Analysis Services 2008. Excel 2007 is an application that creates Drillthrough commands to Analysis Services when you double-click a cell in a pivot table (you learn about analyzing a cube using pivot tables in Excel 2007 in Chapter 17). Another option is to create a Drillthrough action. The following section describes the available action types, ...