Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.