MDX Sub Select

In MDX, a sub select is a secondary SELECT statement nested within the primary SELECT statement, in a FROM clause. Its purpose is to reduce, or filter out, the volume of scanned Essbase data. Using a sub select provides an effective way of processing Essbase queries that require partial aggregations.

Syntax

The syntax for using a sub select is shown in the context of the MDX query format:

[<with_section>]
SELECT <axis_specification>
       [, <axis_specification>...]
  <subselect>
 [WHERE [<slicer_specification>]]

Where <subselect> is:

FROM 
     (SELECT <axis_specification>
       [, <axis_specification>...]
      FROM <cube_specification>)

Notes

The following guidelines apply to members you can use in the sub select:

• Can be from any generation or level. The consolidation operators of descendants are analyzed, for potential filtering out of results. If a descendant's operator is ~ (non consolidation) and its descendants do not have any shared members or prototype members of shared members, its sub-hierarchy is removed from results. Similarly, a stored, non-level-0 member in a block storage hierarchy is the sole contributor to the aggregation; its children are not treated as dependencies.

• Can be calculated members defined in the WITH section.

• Can be formulas. Formula contributors are analyzed, but not their descendants.

• Functions that return a value are not evaluated (see MDX Functions that Return a Number), nor are functions that derive their results using data (see Data-based Set Functions in MDX Functions that Return a Set. All dependencies from such expressions are included.

• If members are from the same dimension, they must also be in the same level and hierarchy (applies to aggregate storage databases only).

• The NON EMPTY syntax is not relevant in a sub-select axis specification.

Example

SELECT 
    [Digital Cameras/Camcorders].Children ON COLUMNS
FROM
    (SELECT
    {[Digital Cameras],[Camcorders]} ON COLUMNS
    FROM ASOsamp.Basic)
WHERE ([Curr Year],[94706],[Coupon],[Cash],[1 to 13 Years],[Under 20,000],[Sale],[Units],[Mar])