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])