| OLE DB Programmer's Reference |  |
| 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.