Drilling Through
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.