Drilling Through

OLE DB Programmer's Reference

It is often useful to retrieve the actual database rows that underlie a particular tuple. OLE DB for OLAP supports this action through a capability known as DRILLTHROUGH. For example, in this statement:

DRILLTHROUGH SELECT {[Unit Sales]} ON COLUMNS, {[Time].{[1997].[Q1]} ON ROWS FROM Sales WHERE [Seattle]

the SELECT statement might return the following cellset:

	Unit Sales
Q1	25

The underlying rowset might have the following rows, which contain the data underlying the specified cell:

ID	Date	Product	Location	Unit sales	Price
25678	1/5/1997	Tractor	Seattle	3	5000
34566	2/3/1997	Tractor	Seattle	5	6000
34700	2/7/1997	Truck	Seattle	15	15000
35110	2/27/1997	Bulldozer	Seattle	2	30000

The syntax of DRILLTHROUGH is as follows:

<drillthrough> ::= DRILLTHROUGH [MAXROWS <numeric_value_expression>] <select_statement>

In the example above, <select_statement> specified a single cell. The provider can choose to support select statements that return more than one cell, but it is not required to. Except for that restriction, the <select_statement> can be any valid MDX query.

If the MAXROWS clause is specified, the number of rows returned is limited to a maximum value specified by <numeric_value_expression>.

The provider might not support drill-through on all cubes. The client application should query the CUBES schema rowset and examine the DRILLTHROUGH property to determine whether it is available.

1998-2001 Microsoft Corporation. All rights reserved.