| SubQueries | (standard MDX) |
MDX subQueries (aka. subSelect).
Description
SubQueries (or subSelects) are one of the latest addition to the MDX language. It is a powerful feature that allows for querying an MDX query instead of a cube.
As opposed to the WHERE clause, subQueries also change the hierarchies structure. Hierarchies defined in a subQuery are filtered (accepting all descendants and ascendants members defined in the subQuery).
See example 'SubQueries in Axis - Branch' for more information.
SELECT
[Measures].members ON 0
FROM [NON VISUAL]
( SELECT [Geography].[Switzerland] ON 0 FROM [SALES] )
The 'NON VISUAL' flags control whether aggregation is using all members (NON VISUAL) or only the filtered members.
See Also
Examples
SubQueries are filtering all member related functions defined in the axis of an MDX query.
In our example, [Geography].[Geo].[Europe].children is returning only the sub-select
member [Geography].[Geo].[Switzerland].
Query
SELECT
crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[Personal]) ON 0
FROM
( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM [Sales] )
Result
| Switzerland |
| Personal |
| 8 |
Assertion : MDX Visual Equals
SELECT
{[Geography].[Geo].[Switzerland]}*{[Product].[Prod].[Personal]} ON 0
FROM
[Sales]
Here is an example defining a single member in a subQuery; this will filter all its ascendants and descendants.
In our example, with [Switzerland] in the subQuery, the filter will return its children { [Lausanne],[Geneva]...} and all its ascendants { [Europe] }.
Query
SELECT
Hierarchize( [Geography].[Geo].members ) ON 0
FROM
( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM [Sales] )
Result
| All Regions | Europe | Switzerland | Geneva | Lausanne | Zurich |
| 248 | 248 | 248 | 128 | 56 | 64 |
Assertion : MDX Visual Equals
SELECT
VisualTotals(Hierarchize(Ascendants([Geography].[Geo].[Switzerland]) + Descendants([Geography].[Geo].[Switzerland]) )) ON 0
FROM
[Sales]
SubQueries are also working with tuples. All non defined tuples in the sub-select are filtered in the axis.
Query
SELECT
Hierarchize(crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[icCube].children)) ON 0
FROM
( SELECT {([Geography].[Geo].[Switzerland],[Product].[Prod].[Personal]),([Geography].[Geo].[Spain],[Product].[Prod].[Partnership])} ON 0 FROM [Sales] )
Result
| Spain | Switzerland |
| Partnership | Personal |
| 8 |
Assertion : MDX Equals
SELECT
Hierarchize({([Geography].[Geo].[Switzerland],[Product].[Prod].[Personal]),([Geography].[Geo].[Spain],[Product].[Prod].[Partnership])}) ON 0
FROM
[Sales]
SubQueries can be defined with more than one axis; this has the same behaviour as chained subQueries.
How does it work?
Each axis of the subQuery defines a filter, resulting in a list of filters. Filters are then applied recursively.
Query
SELECT
crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[icCube].children) ON 0
FROM
( SELECT [Geography].[Geo].[Switzerland] ON 0, [Product].[Prod].[Personal] ON 1 FROM [Sales] )
Result
| Switzerland |
| Personal |
| 8 |
Assertion : MDX Equals
SELECT
{([Geography].[Geo].[Switzerland],[Product].[Prod].[Personal])} ON 0
FROM
[Sales]
Assertion : MDX Equals
SELECT
crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[icCube].children) ON 0
FROM
( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM ( SELECT [Product].[Prod].[Personal] ON 0 FROM [Sales] ))
SubQueries can be chained, this has the same behaviour as a subQuery with multiple axis.
How does it work?
Each subQuery defines a filter, resulting in a list of filters. Filters are then applied recursively.
Query
SELECT
crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[icCube].children) ON 0
FROM
( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM ( SELECT [Product].[Prod].[Personal] ON 0 FROM [Sales] ))
Result
| Switzerland |
| Personal |
| 8 |
Assertion : MDX Equals
SELECT
{([Geography].[Geo].[Switzerland],[Product].[Prod].[Personal])} ON 0
FROM
[Sales]
Assertion : MDX Equals
SELECT
crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[icCube].children) ON 0
FROM
( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM ( SELECT [Product].[Prod].[Personal] ON 0 FROM [Sales] ))
Chained subQueries can be used to filter on the same hierarchy, offering more flexibility than subQueries on multiple axis.
In our example, the first filter applied to all [Europe] countries returns [Spain]. The second filter, [Switzerland], returns an empty set as [Spain] is not in [Switzerland].
Query
SELECT
crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[icCube].children) ON 0
FROM
( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM ( SELECT [Geography].[Geo].[Spain] ON 0 FROM [Sales] ))
Result
Assertion : MDX Visual Equals
SELECT {} ON 0 FROM [Sales]
In case a default member is filtered-out by a sub-query, a new default member is assigned (the first member of the hierarchy in the sub-query).
Within a calculated member, the default member is the non filtered-out one.
Query
WITH
MEMBER [Measures].[Default] AS ( [Time].[Calendar].defaultMember, [Measures].[Amount] )
SELECT
{ [Time].[Calendar].defaultMember } ON 0,
{ [Measures].[Default], [Measures].[Amount] } ON 1
FROM
( SELECT [Time].[Calendar].[2011] ON 0 FROM [Sales] )
Result
| 2011 | |
| Default | 1023 |
| Amount |
Assertion : MDX Equals
WITH
MEMBER [Measures].[Default] AS ( [Time].[Calendar].defaultMember, [Measures].[Amount] )
SELECT
{ [Time].[Calendar].[2011] } ON 0,
{ [Measures].[Default], [Measures].[Amount] } ON 1
FROM
[Sales]
In the context of a calculated measure/member, functions (e.g. children, members) are not filtered.
In the following example the children function in the calculated members return all children, ignoring the sub-query filter.
Query
WITH
MEMBER [Measures].[Europe Children Count] AS count([Geography].[Geo].[Europe].children)
SELECT
{ [Measures].[Europe Children Count] } ON 0
FROM
( SELECT ([Geography].[Geo].[Switzerland]) ON 0 FROM [Sales] )
Result
| Europe Children Count |
| 3 |
Assertion : MDX Visual Equals
WITH
MEMBER [Measures].[Europe Children Count] AS count([Geography].[Geo].[Europe].children)
SELECT
{ [Measures].[Europe Children Count] } ON 0
FROM
[Sales]
If visual mode is active, the result of a tuple evaluation is a visual aggregation. However, in the context of a calculated measure/member if a hierarchy is overwritten the result is the actual aggregation on this hierarchy.
As an example, defining in a calculated measure a tuple as ([Geography].[Geo].[Europe],[Amount]) will return the actual value for [Europe] instead of the visual one.
Query
WITH
MEMBER [Measures].[Amount Lausanne] AS [Measures].[Amount]
MEMBER [Measures].[Amount Lausanne * 2] AS 1 + [Measures].[Amount] * 2
MEMBER [Measures].[Non Visual Amount] AS ( [Geography].[Geo].currentMember,[Measures].[Amount])
SELECT
{ [Geography].[Geo].[Europe],[Geography].[Geo].[Switzerland] } ON 0,
{ [Measures].[Amount Lausanne],[Measures].[Amount Lausanne * 2], [Measures].[Non Visual Amount] } ON 1
FROM
( SELECT ([Geography].[Geo].[Lausanne]) ON 0 FROM [Sales] )
Result
| Europe | Switzerland | |
| Amount Lausanne | 56 | 56 |
| Amount Lausanne * 2 | 113 | 113 |
| Non Visual Amount | 255 | 248 |
Assertion : MDX Equals
WITH
MEMBER [Measures].[Amount Lausanne] AS ([Measures].[Amount],[Geography].[Geo].[Lausanne])
MEMBER [Measures].[Amount Lausanne * 2] AS 1+ ([Measures].[Amount],[Geography].[Geo].[Lausanne]) * 2
MEMBER [Measures].[Non Visual Amount] AS [Measures].[Amount]
SELECT
{ [Geography].[Geo].[Europe],[Geography].[Geo].[Switzerland] } ON 0,
{ [Measures].[Amount Lausanne],[Measures].[Amount Lausanne * 2], [Measures].[Non Visual Amount] } ON 1
FROM [Sales]
If visual mode is off, values are not anymore a visual aggregation but the 'actual' aggregation as in a classical MDX query (without sub-select).
Therefore subQueries will filter only available members without changing their aggregate values.
Query
SELECT
{ [Geography].[Geo].[Europe] + [Geography].[Geo].[Switzerland].children } ON 0
FROM
NON VISUAL ( SELECT ([Geography].[Geo].[Lausanne]) ON 0 FROM [Sales] )
Result
| Europe | Lausanne |
| 255 | 56 |
Assertion : MDX Equals
SELECT
{ [Geography].[Geo].[Europe],[Geography].[Geo].[Lausanne] } ON 0
FROM
[Sales]
Subqueries work also on dimensions that are defined in across measure groups.
In our example, the subselect is defined in dimension [Time] which belongs to both [Amount] and [Fx] measure groups.
Query
WITH
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
{[Time].[Calendar].[2010], [Time].[Calendar].[Q2 2010], [Time].[Calendar].[Month].[May 2010]} on 1
FROM
( SELECT [Time].[Calendar].[May 2010] on 0 FROM [SALES] )
Result
| amountUSD | amountEUR | |
| 2010 | 1023.0 | 1106.886 |
| Q2 2010 | 1023.0 | 1106.886 |
| May 2010 | 1023.0 | 1106.886 |
Assertion : MDX Visual Equals
WITH
MEMBER [Product].[Prod].[2010] AS ([Time].[Calendar].[May 2010],[Product].[Prod].defaultMember)
MEMBER [Product].[Prod].[Q2 2010] AS ([Time].[Calendar].[May 2010],[Product].[Prod].defaultMember)
MEMBER [Product].[Prod].[May 2010] AS ([Time].[Calendar].[May 2010],[Product].[Prod].defaultMember)
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
{[Product].[Prod].[2010],[Product].[Prod].[Q2 2010],[Product].[Prod].[May 2010]} on 1
FROM [SALES]
WHERE ([Time].[Calendar].[May 2010])
Subqueries work also on a dimension that is defined for a single measure group.
In our example, the subselect is defined in dimension [Geography] which belongs only to [Amount]. Note how
a subquery is working in a similar way as a VisualTotal.
Query
WITH
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
[Geography].[Geo].members on 1
FROM
( SELECT [Geography].[Geo].[Madrid] on 0 FROM [SALES] )
WHERE [Time].[Month].[May 2010]
Result
| amountUSD | amountEUR | |
| All Regions | 1.0 | 1.082 |
| Europe | 1.0 | 1.082 |
| Spain | 1.0 | 1.082 |
| Madrid | 1.0 | 1.082 |
Assertion : MDX Visual Equals
WITH
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
VisualTotals({[Geography].[Geo].[All Regions],[Geography].[Geo].[Europe],[Geography].[Geo].[Spain],[Geography].[Geo].[Madrid]}) on 1
FROM
[SALES]
WHERE [Time].[Month].[May 2010]
Assertion : MDX Visual Equals
WITH
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
[Geography].[Geo].members on 1
FROM
( SELECT ([Geography].[Geo].[Madrid],[Time].[Month].[May 2010]) on 0 FROM [SALES] )
The [Geography].[Geo] is used both on the axis 0 and in the slicer to create an empty cube.
Query
SELECT [Geography].[Geo].[All Regions] ON 0 FROM ( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM [Sales] WHERE [Geography].[Geo].[Spain] )
Result
Assertion : Cell Equals
SELECT ( [Geography].[Geo].[Switzerland], [Geography].[Economy].[Spain] ) ON 0 FROM [Sales]
Assertion : Cell Equals
SELECT { ( [Geography].[Geo].[Switzerland], [Geography].[Economy].[Spain] ) } ON 0
FROM [Sales]
Assertion : Cell Equals
SELECT {} ON 0
FROM [Sales]
The [Geography].[Geo] is used both on the axis 0 and in the slicer to create an empty cube.
Query
SELECT [Geography].[Geo].[All Regions] ON 0 FROM NON VISUAL ( SELECT [Geography].[Geo].[Switzerland] ON 0 FROM [Sales] WHERE [Geography].[Geo].[Spain] )
Result
Assertion : Cell Equals
SELECT ( [Geography].[Geo].[Switzerland], [Geography].[Economy].[Spain] ) ON 0 FROM [Sales]
Assertion : Cell Equals
SELECT { ( [Geography].[Geo].[Switzerland], [Geography].[Economy].[Spain] ) } ON 0
FROM [Sales]
Assertion : Cell Equals
SELECT {} ON 0
FROM [Sales]
A slicer is used both within the actual sub-select (to select the actual country) and within the outer select (to select the period) itself.
Query
WITH MEMBER [Measures].[x] AS [Time].[Calendar].currentMember.NAME
SELECT { [Measures].[x], [Measures].[Amount] } ON 0, [Geography].[Geo].[All Regions] ON 1
FROM (SELECT Iif( [Time].[Calendar].currentMember.NAME = '2011', [Geography].[Geo].[Spain], [Geography].[Geo].[France] ) ON 0 FROM [Sales] WHERE [Time].[Calendar].[2011] )
WHERE [Time].[Calendar].[May 2010]
Result
| x | Amount | |
| All Regions | May 2010 | 3 |
Assertion : Cell Equals
WITH MEMBER [Measures].[x] AS [Time].[Calendar].[May 2010].NAME
MEMBER [Measures].[y] AS ( [Time].[Calendar].[May 2010], [Geography].[Geo].[Spain], [Measures].[Amount] )
SELECT { [Measures].[x], [Measures].[y] } ON 0, [Geography].[Geo].[All Regions] ON 1
FROM [Sales]
Special Examples (errors, null, ...)
An empty slicer within the sub-select is generating an error.
Query
SELECT [Geography].[Geo].members ON 0
FROM (SELECT { [Geography].[Geo].[Spain], [Geography].[Geo].[France] } ON 0 FROM [Sales] WHERE [Geography].[Geo].[UnknownCountry] )
Result
Error Code : OLAP_SLICER_NULL_EVAL
Error Message : the slicer cannot be evaluated to a NULL member or an empty set.
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.