SELECT Statement
This statement is used to create queries that return multidimensional data, either in a Microsoft® ActiveX® Data Objects (ADO) Cellset object or in an OLE DB Dataset object.
BNF
BNF for SELECT (OLAP)
<select_statement> ::= [WITH <single_formula_specification>
[<single_formula_specification>...]]
SELECT [<axis_specification> [, <axis_specification>...]]
FROM <cube_specification>
[WHERE <slicer_specification>]
[<cell_props>]
<single_formula_specification> ::= <member_specification>
| <set_specification>
| <cache_specification>
<member_specification> ::= MEMBER <parent_of_member>.<member_name> AS '<value_expression>'
[, <solve_order_specification>]
[, <member_property_definition>...]
<solve_order_specification> ::= SOLVE_ORDER = <unsigned_integer>
<member_property_definition> ::= <member_property_name> = <value_expression>
<set_specification> ::= SET <set_name> AS '<set>'
<cache_specification> ::= CACHE AS '(<set> [,<set>])'
<axis_specification> ::= [NON EMPTY] <set> [<dim_props>] ON <axis_name>
<set> ::= member:member
| <set_value_expression>
| {<set> | <tuple> [,<set> | <tuple>...]}
| (<set>)
<tuple> ::= <member> | (<member>[,<member>...]) | <tuple_value_expression>
<axis_name> ::= COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS | AXIS(<index>)
<dim_props> ::= [DIMENSION] PROPERTIES <property> [, <property>...]
<property> ::= <dimension_property> | <level_property> | <member_property>
<dimension_property> ::= <dimension_name>.ID | <dimension_name>.KEY | <dimension_name>.NAME
<level_property> ::= [<dimension_name>.]<level_name>.ID
| [<dimension_name>.]<level_name>.KEY
| [<dimension_name>.]<level_name>.NAME
<member_property> ::= <level_name>.<member_property_name>
<cube_specification> ::= <cube_name>
<slicer_specification> ::= <tuple>
<cell_props> ::= [CELL] PROPERTIES <cell_property> [, <cell_property>...]
<cell_property> ::= <mandatory_cell_property>
| <optional_cell_property>
| <provider_specific_cell_property>
<mandatory_cell_property> ::= CELL_ORDINAL | VALUE | FORMATTED_VALUE
<optional_cell_property> ::= FORMAT_STRING | FORE_COLOR | BACK_COLOR | FONT_NAME | FONT_SIZE | FONT_FLAGS
<provider_specific_cell_property> ::= <identifier>
BNF for SELECT (Data Mining)
<column_ref_list>::= <column_ref>
| <column_ref_list> , <column_ref>
<column_ref>::= <identifier>
| <identifier>.<column_ref>
| <column_ref> ( <column_ref_list> )
| SKIP
| CLUSTER()
| $SUPPORT
| $VARIANCE
| $STDEV
| $STDDEV
| $PROBABILITY
| $PROBABILITY_VARIANCE
| $PROBABILITY_STDEV
| $PROBABILITY_STDDEV
| $ADJUSTEDPROBABILITY
| $DISTANCE
| PREDICT ( <column_ref> [, <pred_option_list>] )
| <column_ref> AS <identifier>
<pred_option_list>-> <pred_option>
| <pred_option_list> , <pred_option>
<pred_option>::= EXCLUDE_NULL
| INCLUDE_NULL
| INPUT_ONLY
| EXCLUSIVE
| INCLUSIVE
| INCLUDE_STATISTICS
<select>::= <pred_select>
| <model_select>
<pred_select>::= SELECT [FLATTENED] <expression_list> FROM <identifier> [NATURAL] PREDICTION JOIN
<query> AS <identifier> [ON <on_list>] [<where_clause>]
| SELECT [FLATTENED] <expression_list> FROM <identifier> [NATURAL] PREDICTION JOIN
<expression> AS <identifier> [ON <on_list>] [<where_clause>]
<model_select>::= SELECT [DISTINCT] <expression_list> FROM <identifier> [<where_clause>]
| SELECT [DISTINCT] <expression_list> FROM <identifier>.PMML
| SELECT [DISTINCT] <expression_list> FROM <identifier>.CONTENT [<where_clause>]
<expression_list>::= <expression>
| <expression_list> , <expression>
<expression>::= <value>
| <column_ref>
| *
| <expression> + <expression>
| <expression> - <expression>
| <expression> * <expression>
| <expression> / <expression>
| -<expression>
| +<expression>
| ( <expression> )
| <expression> OR <expression>
| <expression> AND <expression>
| NOT <expression>
| <expression> = <expression>
| <expression> <> <expression>
| <expression> < <expression>
| <expression> <= <expression>
| <expression> > <expression>
| <expression> >= <expression>
| PREDICTSTDEV ( <column_ref> )
| PREDICTSTDDEV ( <column_ref> )
| PREDICTVARIANCE ( <column_ref> )
| PREDICTSUPPORT ( <column_ref> )
| PREDICTPROBABILITY ( <column_ref> )
| PREDICTADJUSTEDPROBABILITY ( <column_ref> )
| CLUSTERDISTANCE ( [<expression>] )
| CLUSTERPROBABILITY ( [<expression>] )
| PREDICTHISTOGRAM ( <column_ref> )
| TOPCOUNT ( <expression>, <column_ref>, <expression> )
| TOPSUM ( <expression>, <column_ref>, <expression> )
| TOPPERCENT ( <expression>, <column_ref>, <expression> )
| BOTTOMCOUNT ( <expression>, <column_ref>, <expression> )
| BOTTOMSUM ( <expression>, <column_ref>, <expression> )
| BOTTOMPERCENT ( <expression>, <column_ref>, <expression> )
| ( SELECT <expression_list> FROM <expression> <where_clause> )
| ( <singleton_list> )
| <expression> AS <identifier>
<singleton_list>::= <singleton>
| <singleton_list> UNION <singleton>
<singleton>::= SELECT <expression_list>
<where_clause>::= WHERE <expression>
<delete>::= <delete_reg>
| <delete_content>
Note Microsoft SQL Server™ 2000 Analysis Services data mining algorithms do not support probability variance or probability standard deviation. The columns $PROBABILITY_VARIANCE, $PROBABILITY_STDEV, and $PROBABILITY_STDEV always contain 0.
The $ADJUSTEDPROBABILITY column is an Analysis Services extension to the OLE DB for Data Mining specification.
Remarks
In the <tuple> ::= (<member>[,<member>...]) syntax, each <member> value must be from a different dimension.
In the <slicer_specification> syntax, the members in the <tuple> value must be in dimensions other than those in the <axis_specification> values.
If a dimension in the cube is omitted from the <axis_specification> values and <slicer_specification> value, the dimension's default member is implicitly added to the <slicer_specification> value.
The DISTINCT keyword is ignored in data mining queries.
Inserting Comments
Like SQL, Multidimensional Expressions (MDX) syntax can contain user-readable comments that are ignored when the commands are processed. The three different character sets that indicate comments are outlined in the following table.
| Characters | Scope |
|---|---|
| // (C++ style forward slashes) | All text after the forward slashes (/) and before the end of the same line is ignored. |
| -- (SQL hyphens) | All text after the hyphens (-) and before the end of the same line is ignored. |
| /*...*/ (C style slash and asterisk pairs) | All text between the opening forward slash (/) and asterisk and the closing asterisk (*) and closing forward slash (/) is ignored. This type of comment can span multiple lines. |
Example
The following example shows the use of comments in an MDX command:
/* Using this query to view
information about units shipped
and units ordered */
SELECT
{ [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,
// The next command specifies nonempty members only
NON EMPTY [Store].[Store Name].Members ON ROWS
FROM Warehouse -- Pulled from the Warehouse cube
For more information, see Comments in MDX.