MDX where clause (aka. slicer).
Description
The MDX where clause even though sharing the same name as the SQL statement is fundamentally different.
A MDX WHERE clause reduces the scope of the query. The WHERE clause reduces the cube into a new 'sliced' cube, the slice being defined by the WHERE clause.
SELECT
[Measures].Members ON 0,
[Geography].[Geo].Members ON 1
FROM
[Sales]
WHERE
([Time].[Calendar].[Year].[2010])
In the previous query, only that section of the cube where the year is 2010 is used; the rest of the cube is ignored.
Tuples and sets can be used in a WHERE clause :
SELECT
...
WHERE
{([Geography].[Geo].[Spain],[Product].[icCube].[Personal]),([Geography].[Geo].[Switzerland],[Product].[icCube].[Corporate])}
See 'Where clause on a set of tuples' example for further explanation.
The where clause does not allow to use a hierarchy being used in one of the axis. SubQueries, which are similar, offer greater flexibility. The cost maybe slower performance.
See Also
Where Clause Exists Where Set Clause Exists SubQueries
Examples
The standard example where the where clause is used to overwrite default values.
In our example the value returned is ([Geography].[Geo].[Switzerland],[Measures].[Amount]).
Query
SELECT
[Measures].[Amount] ON 0
FROM
[Sales]
WHERE
[Geography].[Geo].[Switzerland]
Result
Assertion : Cell Equals
SELECT
( [Geography].[Geo].[Switzerland], [Measures].[Amount] ) ON 0
FROM
[Sales]
The standard example where the where clause is used to overwrite default values.
In our example the value returned is ([Geography].[Geo].[Switzerland], [Product].[Prod].[Personal], [Measures].[Amount]).
Query
SELECT
( [Measures].[Amount] * [Geography].[Economy].members ) ON 0
FROM
[Sales]
WHERE
( [Geography].[Geo].[Switzerland], [Product].[Prod].[Personal] )
Result
| Amount |
Amount |
Amount |
| ALL |
None |
Switzerland |
| 8 |
8 |
8 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[x] AS ( [Measures].[Amount], [Geography].[Economy].[Switzerland], [Product].[Prod].[Personal] )
SELECT
[Measures].[x] * Hierarchize( Ascendants( [Geography].[Geo].[Switzerland] )) ON 0
FROM
[Sales]
An example to highlight that the limitation on the where clause applies to hierarchy and not dimensions. Indeed, a dimension used in the slicer may be used as well in one axis.
In the example the where clause is producing the evaluation of the tuple ([Geography].[Geo].[Europe],[Geography].[Economy].[None]).
Query
WITH
MEMBER [Measures].[Pol Name] AS [Geography].[Economy].currentMember.Name
SELECT
{([Geography].[Geo].[Europe],[Measures].[Amount]),
([Geography].[Geo].[Europe],[Measures].[Pol Name])} ON 0
FROM
[Sales]
WHERE
([Geography].[Economy].[None])
Result
| Europe |
Europe |
| Amount |
Pol Name |
| 248 |
None |
Assertion : Cell Equals
WITH
MEMBER [Measures].[Pol Name] AS [Geography].[Economy].currentMember.Name
SELECT
{([Geography].[Geo].[Europe],[Geography].[Economy].[None],[Measures].[Amount]),
([Geography].[Geo].[Europe],[Geography].[Economy].[None],[Measures].[Pol Name])} ON 0
FROM
[Sales]
A where clause can be applied to a set of members performing a SUM aggregation.
Query
SELECT
{[Measures].[Amount]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS ([Geography].[Geo].[Spain],[Measures].[Amount])+([Geography].[Geo].[Switzerland],[Measures].[Amount])
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount]), ([Geography].[Geo].[Switzerland],[Measures].[Amount]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
A where clause can be applied to a set of members performing a COUNT aggregation.
Query
SELECT
{[Measures].[Amount-Count]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS ([Geography].[Geo].[Spain],[Measures].[Amount-Count])+([Geography].[Geo].[Switzerland],[Measures].[Amount-Count])
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Count]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Count]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
A where clause can be applied to a set of members performing a MIN aggregation.
Query
SELECT
{[Measures].[Amount-Min]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Min( { [Geography].[Geo].[Spain], [Geography].[Geo].[Switzerland] },[Measures].[Amount-Min] )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Min]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Min]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
A where clause can be applied to a set of members performing a MAX aggregation.
Query
SELECT
{[Measures].[Amount-Max]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Max( { [Geography].[Geo].[Spain], [Geography].[Geo].[Switzerland] },[Measures].[Amount-Max] )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Max]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Max]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
A where clause can be applied to a set of members performing a AVERAGE (leaves) aggregation.
Query
SELECT
{[Measures].[Amount-AvgLeaves]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
| Amount-AvgLeaves |
| 35.857142857142854 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-AvgLeaves]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-AvgLeaves]) } )
SELECT
{[Measures].[Spain+Switzerland]} ON 0
FROM
[Sales]
A where clause can be applied to a set of members performing a DISTINCT COUNT (leaves) aggregation.
In our example, behaves like count as values are unique.
Query
SELECT
{[Measures].[Amount-Unique]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
Assertion : Cell Equals
SELECT
{[Measures].[Amount-Count]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
A where clause can be applied to a set of members performing a NON EMPTY (leaves) aggregation.
Query
SELECT
{[Measures].[Amount-NoEmpty]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[True] AS 1
SELECT
{[Measures].[True]} ON 0
FROM
[Sales]
Not supported.
Query
SELECT
{[Measures].[Amount-AvgChildren]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
| Amount-AvgChildren |
| #ERROR : (OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : the aggregation 'average (children)' is not supported in the slicer (aka. where).
Not supported.
Query
SELECT
{[Measures].[Amount-Open]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
| Amount-Open |
| #ERROR : (OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : the aggregation 'open' is not supported in the slicer (aka. where).
Not supported.
Query
SELECT
{[Measures].[Amount-Close]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
| Amount-Close |
| #ERROR : (OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : the aggregation 'close' is not supported in the slicer (aka. where).
Not supported.
Query
SELECT
{[Measures].[Amount-OpenNoEmpty]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
| Amount-OpenNoEmpty |
| #ERROR : (OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : the aggregation 'open (non-empty)' is not supported in the slicer (aka. where).
Not supported.
Query
SELECT
{[Measures].[Amount-CloseNoEmpty]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Result
| Amount-CloseNoEmpty |
| #ERROR : (OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_SLICER_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : the aggregation 'close (non-empty)' is not supported in the slicer (aka. where).
A where clause can be applied also to a set of tuples.
Query
SELECT
{[Measures].[Amount]} ON 0
FROM
[Sales]
WHERE
{([Geography].[Geo].[Spain],[Product].[icCube].[Personal]),([Geography].[Geo].[Switzerland],[Product].[icCube].[Corporate])}
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[Spain+Switzerland] AS
([Geography].[Geo].[Spain],[Product].[icCube].[Personal],[Measures].[Amount])+([Geography].[Geo].[Switzerland],[Product].[icCube].[Corporate],[Measures].[Amount])
SELECT
{[Measures].[Spain+Switzerland]} ON 0
FROM
[Sales]
The geography dimension members are being filtered by the slicer content. Here [Canada] is filtered out because of [Spain] defined in the slicer.
Query
SELECT
[Geography].[Geo].[Canada] on 0
FROM
[Sales]
WHERE
[Geography].[Economy].[Spain]
Result
Assertion : Cell Equals
SELECT {} ON 0 FROM [Sales]
Similar to the previous but note how the [Canada] member is still accessible within axis expression.
Query
SELECT
Iif( [Geography].[Geo].[Canada].Parent.Parent.NAME = 'All Regions', ( [Measures].[Fx], [Geography].[Geo].[Spain] ), ( [Measures].[Amount], [Geography].[Geo].[Spain] ) ) on 0
FROM
[Sales]
WHERE
[Geography].[Economy].[Spain]
Result
| Fx |
| Spain |
| 1.0310000000000001 |
Assertion : Cell Equals
SELECT ( [Measures].[Fx], [Geography].[Economy].[Spain] ) ON 0 FROM [Sales]
Similar as previous example showing that the [Measures] dimension is handled the same way.
Query
SELECT
Iif( Count( [Measures].members ) = 1, [Time].[Calendar].[2010], [Time].[Calendar].[2011] ) ON 0
FROM
[Sales]
WHERE
[Measures].[Amount]
Result
Assertion : MDX Equals
SELECT
[Time].[Calendar].[2010] ON 0
FROM
[Sales]
WHERE
[Measures].[Amount]
Calculated measures are ignored from filtering.
Query
WITH
MEMBER [Measures].[x] AS 42
SELECT
Iif( Count( [Measures].[x] ) = 1, [Time].[Calendar].[2010], [Time].[Calendar].[2011] ) ON 0
FROM
[Sales]
WHERE
[Measures].[Amount]
Result
Assertion : MDX Equals
SELECT
[Time].[Calendar].[2010] ON 0
FROM
[Sales]
WHERE
[Measures].[Amount]
Calculated measures are not filtering anything.
Query
WITH
MEMBER [Measures].[x] AS 42
SELECT
Iif( Count( [Measures].members ) = 14, [Time].[Calendar].[2010], [Time].[Calendar].[2011] ) ON 0
// ^^^^
// we've 14 regular measures (non of them have been filtered out)
FROM
[Sales]
WHERE
[Measures].[x]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[x] AS 42
SELECT
[Time].[Calendar].[2010] ON 0
FROM
[Sales]
WHERE
[Measures].[x]
Calculated members are ignored from filtering.
Query
WITH
MEMBER [Time].[Calendar].[Year].[1900] AS 42
SELECT
Iif( Count( [Time].[Calendar].[Year].[1900] ) = 1, [Measures].[Amount], [Measures].[Fx] ) ON 0
FROM
[Sales]
WHERE
[Time].[Calendar].[Year].[2010]
Result
Assertion : MDX Equals
SELECT
[Measures].[Amount] ON 0
FROM
[Sales]
WHERE
[Time].[Calendar].[Year].[2010]
Calculated members are not filtering anything.
Query
WITH
MEMBER [Time].[Calendar].[Year].[1900] AS 42
SELECT
Iif( Count( [Time].[Calendar].[Year].members ) = 2, [Measures].[Amount], [Measures].[Fx] ) ON 0
// ^^^
// we've 2 regular years (non of them have been filtered out)
FROM
[Sales]
WHERE
[Time].[Calendar].[Year].[1900]
Result
Assertion : MDX Equals
WITH
MEMBER [Time].[Calendar].[Year].[1900] AS 42
SELECT
[Measures].[Amount] ON 0
FROM
[Sales]
WHERE
[Time].[Calendar].[Year].[1900]
Calculated members are not filtering anything.
Query
WITH
MEMBER [Time].[Calendar].[Year].[1900] AS -( [Measures].currentMember, [Time].[Calendar].[Year].[2010] )
SELECT
Iif( Count( [Time].[Calendar].[Year].members ) = 2, [Measures].[Amount], [Measures].[Fx] ) ON 0
// ^^^
// we've 2 regular years (non of them have been filtered out)
FROM
[Sales]
WHERE
( [Time].[Calendar].[Year].[1900], [Geography].[Geo].[Spain] )
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[x] AS -( [Measures].[Amount], [Time].[Calendar].[Year].[2010], [Geography].[Geo].[Spain] )
SELECT
[Measures].[x] ON 0
FROM
[Sales]
A where clause cannot use a hierarchy that is used in the axis. SubQueries should be used to overcome this constraint.
Members in calculated members do not have this limitation.
Query
SELECT
{ ( [Measures].[Amount], [Geography].[Geo].[Switzerland] ) } ON 0
FROM
[Sales]
WHERE
[Geography].[Geo].[Spain]
Result
A where clause cannot use a hierarchy that is used in the axis. SubQueries should be used to overcome this constraint.
Members in calculated members do not have this limitation.
Query
SELECT
[Geography].[Geo].[Switzerland] ON 0
FROM
[Sales]
WHERE
[Geography].[Geo].[Spain]
Result
A where clause cannot use a hierarchy that is used in the axis. SubQueries should be used to overcome this constraint.
Members in calculated members do not have this limitation.
Query
SELECT
[Geography].[Geo].members ON 0
FROM
[Sales]
WHERE
[Geography].[Geo].[Spain]
Result
Special Examples (errors, null, ...)
Evaluating the slicer to null is making the result empty.
Query
SELECT
[Measures].[Amount] ON 0
FROM
[Sales]
WHERE
( [Geography].[Geo].[Spain] , [Geography].[Economy].[France] )
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.
icCube OLAP version: 2.5.1 13-May-2012 02:21:10 GMT