Analysis Services Programming

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.

See Also

MDX