Cubing — SELECT statement (MDX)

The SELECT statement queries a cube and produces another cube with the results of the query.

Syntax


>>-+----------------------------------------------+------------->
   |       .-,----------------------------------. |   
   |       V                                    | |   
   '-WITH----| Query-scoped object definition |-+-'   

           .-,----------------------.                   
           V                        |                   
>--SELECT----| Axis specification |-+--FROM--CubeName----------->

>--+------------------------+----------------------------------><
   '-WHERE--TupleExpression-'   

Query-scoped object definition

|--+-MEMBER--MemberIdentifier--AS--MemberExpression--+---------------------------------------------------+-+--|
   |                                                 |    .-,------------------------------------------. | |   
   |                                                 |    V                                            | | |   
   |                                                 '-,----MemberPropertyName--=--MemberPropertyValue-+-' |   
   '-SET--SetIdentifier--AS--SetExpression-----------------------------------------------------------------'   

Axis specification

|--+-----------+--AxisSet--+---------------------------------------------+--+---------------------------------------+-->
   '-NON EMPTY-'           |                            .-,------------. |  |                  .-,----------------. |   
                           |                            V              | |  |                  V                  | |   
                           '-+-----------+--PROPERTIES----PropertyName-+-'  '-CELL PROPERTIES----CellPropertyName-+-'   
                             '-DIMENSION-'                                                                              

>--ON--+-COLUMNS--+---------------------------------------------|
       +-ROWS-----+   
       +-PAGES----+   
       +-SECTIONS-+   
       +-CHAPTERS-+   
       +-AXIS(n)--+   
       '-n--------'

Parameters

CubeName: Specifies the cube on which to perform the query.
TupleExpression: Specifies the slice of the cube to which to limit the results of the query.
MemberIdentifier: Specifies the temporary member to create for the scope of the query.
MemberExpression: Specifies the expression that defines the query-scoped member.
MemberPropertyName: Specifies a property of the query-scoped member.
MemberPropertyValue: Specifies the value to assign to the property of the query-scoped member.
SetIdentifier: Specifies the temporary set to create for the scope of the query.
SetExpression: Specifies the expression that defines the query-scoped set.
AxisSet: Specifies the members to include on a particular axis of the result cube.
PropertyName: Specifies a property to include on a particular axis of the result cube.
CellPropertyName: Specifies an intrinsic cell property. Currently, no values for specified cell properties will be returned, but the MDX statement is considered valid by InfoSphere™ Warehouse Cubing Services.
n, AXIS(n): Specifies the axis on which to include the specified member. The first five axes have equivalant literal expressions: AXIS(0) is equivalent to COLUMNS, AXIS(1) is equivalent to ROWS, AXIS(2) is equivalent to PAGES, AXIS(3) is equivalent to SECTIONS, and AXIS(4) is equivalent to CHAPTERS.

Description

A SELECT statement consists of two required clauses (SELECT and FROM) and any of two optional clauses (WITH and WHERE):

SELECT: The SELECT clause is required and specifies which members of the target cube to include in the result cube. If you specify PROPERTIES or DIMENSION PROPERTIES, the result cube will include the specified properties for each member. If you specify CELL PROPERTIES, the result cube will not include the specified cell properties, but the MDX SELECT statement is still considered valid. Each set you specify is mapped to a different axis of the result cube; you can optionally use the ON keyword to specify which axis to map the set to. You can optionally use the NON EMPTY keyword to exclude empty members of the specified set from the result cube.
FROM: The FROM clause is required and specifies the cube that is the target of the query.
WITH: The WITH clause is optional and defines any query-scoped calculated members or query-scoped named sets to create. You can optionally define and set member properties for any calculated members that you define in this clause.
WHERE: The WHERE clause is optional and specifies the slice of the target cube to which the query limits its results. The slicer typically specifies members from one or some, but not all, of the hierarchies of a cube.