Using DRILLTHROUGH to Retrieve Source Data

The DRILLTHROUGH statement is used in Multidimensional Expressions (MDX) to retrieve a rowset from the source data for a cube cell.

In order to execute a DRILLTHROUGH statement on a cube, drillthrough must be enabled for that cube in the Drillthrough Options dialog box. The columns that are returned by a DRILLTHROUGH statement are also specified in this dialog box. (If you are programming with Decision Support Objects (DSO), instead of using the dialog box, you can use the AllowDrillThrough and DrillThroughColumns properties.) For more information, see Specifying Drillthrough Options.

The following syntax construct describes the DRILLTHROUGH statement:

<drillthrough> := DRILLTHROUGH [<Max_Rows>] [<First_Rowset>] <MDX select>
   < Max_Rows> := MAXROWS <positive number>
   <First_Rowset> := FIRSTROWSET <positive number>

The DRILLTHROUGH statement contains a SELECT clause to identify the cube cell for which source data is retrieved. The SELECT clause is identical to an ordinary MDX SELECT statement except that in the SELECT clause only one member can be specified on each axis. If more than one member is specified on an axis, an error occurs.

The <max_rows> syntax specifies the maximum number of the rows in each returned rowset. If the OLE DB provider that is used to connect to the data source does not support DBPROP_MAXROWS, the <max_rows> setting is ignored.

The <first_rowset> syntax identifies the partition whose rowset is returned first.

The following example demonstrates the use of the DRILLTHROUGH statement:

DRILLTHROUGH
    SELECT [Warehouse].[All Warehouses].[Canada].[BC] ON ROWS,
    [Time].[1998].[Q1] ON COLUMNS,
    [Product].[All Products].[Drink] ON PAGES,
    [Measures].[Units Shipped] ON SECTIONS
    FROM [My Cube]