Key Performance Indicators, Actions, and the DRILLTHROUGH Statement - Microsoft SQL Server 2008 Analysis Services - page 283

Our designers decided to have target measure groups specified in RETURN clauses to support
drill-across scenarios. For example, imagine that a user is browsing the data stored in one
measure group. That measure group has a high level of detail and stores data aggregated
from some point. Perhaps it doesn't have records for each sales transaction that occurred
during the day; the sales are aggregated daily. If the user found something interesting, she
would probably want to see the individual transactions that contributed to a sale.
To make it possible for her to see those individual transactions, you issue a drillthrough
request. Because the data retrievable by a DRILLTHROUGH statement must be stored in a
cube (in either relational online analytical processing [ROLAP] or multidimensional online
analytical processing [MOLAP] form), you can add low granularity data into your existing
measure group to bring more data into Analysis Services. Or, you could create another
measure group—one that would probably be based on a ROLAP partition—and a dimen-
sion that has granularity on a lower level of details. (Individual transactions could be
stored in this measure group.) With this approach, the data that is used most often by
analytical applications (aggregated data) is stored separately from rarely used data
(drillthrough data).
When Analysis Services receives a drillthrough request, it analyzes the columns specified
in the RETURN clause. It then uses those columns to identify the target measure group and
maps the cube coordinates specified in the SELECT statement to the coordinates in the
target measure group.
Defining DRILLTHROUGH Columns in a Cube
Because a RETURN clause is optional for a DRILLTHROUGH statement, you don't have to
define one in a client application. The columns that the application is to retrieve by the
DRILLTHROUGH statement can come from the cube definition.
In a cube, columns for a drillthrough query are defined by Drillthrough actions, which
were previously discussed in this chapter. A cube designer can create a Drillthrough
action and mark it as the default. When Analysis Services processes a DRILLTHROUGH state-
ment with no RETURN clause, it internally requests MDSCHEMA_ACTIONS and retrieves the
default action targeted on the current coordinate (the coordinate specified in the inner
SELECT statement). It then retrieves the action string from the CONTENT column and
appends that string to the original DRILLTHROUGH statement.
You can have multiple default Drillthrough actions in a cube, but they have to cover
different portions of the cube. For example, it's a common practice to create a default
action for each measure group by using a MeasureGroupMeasures MDX function to specify
a target property of the action.