> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.
# MDX Filtering
## Introduction
In the MDX language, filtering can be achieved either by specifying a Slicer axis (using the WHERE clause)
or by using a Subselect expression (nested SELECT statements). This affects the originating query, either by generating
`CubeFilter`s or by rewriting the initial query location so as to express the initial MDX filtering.
## MDX Filtering
### Slicer Axis
The Slicer axis filters the data returned by the MDX SELECT statement,
restricting the returned data so that only data intersecting with the specified members will be returned.
It can be thought of as an invisible extra axis in a query. The slicer axis is defined in the WHERE clause of the SELECT statement.
> **Important:** Unlike the WHERE clause in SQL, the WHERE clause of an MDX SELECT statement never directly filters what
> is returned on the Rows axis of a query. To filter what appears on the Rows or Columns axis of a query, you can use a variety
> of MDX functions, for example `Filter()`, `NonEmpty()`, and `TopCount()`.
#### Impact of Slicer on Query Location
In Atoti, filtering expressed through the Slicer axis may be reflected **directly** in the locations evaluated at query time.
Expressing a Slicer axis with a single tuple expression instead of a set, will result in the rewrite of the query so that the query location
includes the tuple's members. Any other slicer expression that does not fall into this case will be translated to a Subselect expression.
### Subselect Expression
Subselect expressions are nested SELECT expressions used to restrict the view of the cube from where the outer external SELECT
is being evaluated. They define a new space over which all calculations are evaluated. In other words, Subselects allow you to run a query
as if a hierarchy only contained a subset of its members. Internally, the evaluation of a Subselect will result in the creation
of `CubeFilter`s characterizing the new space.
Subselects can be nested without limited depth, except available memory. The innermost Subselect defines the starting subspace over
which filtering is applied and passed onto the next outer SELECT. An important thing to note is that nesting is not a commutative operation:
the order in which the nesting is done may produce different results.
This particularity implies that the performance of expensive operations in the main query can be improved using Subselects
to reduce the amount of data the main query has to deal with upfront.
> **Do not** confuse filtering using the Slicer axis or a Subselect statement, and filtering using MDX functions.
> The former changes the values appearing in the result whereas the latter only controls what appears on rows and columns by filtering
> the sets displayed there. There are many functions specialized for filtering such as `Filter()`, `NonEmpty()`, `TopCount()`
> or even the `NON EMPTY` statement.
## MDX Default Members
The default member of a hierarchy is used to evaluate expressions when a hierarchy is not included in a query. Otherwise,
whenever a hierarchy from a dimension is included in a query, all the default members in the hierarchy are ignored. This may
produce counter-intuitive results, especially when using filtering.
The evaluation of the default member follows the following rules:
* If the hierarchy on which the default member is defined is present on any axis, the default member is ignored (by definition).
* If granted, the default member of a hierarchy acts as a default slicer when the given hierarchy is not explicitly
included in a query axis (by definition).
* When using a Subselect:
* If the sub-selected set includes the hierarchy’s default member, then if the corresponding hierarchy is not present in the top-level select,
the MDX engine forces the evaluation on the default member for the hierarchy. This will result in an aggregation on the corresponding default member
(the default member cannot be applied on the top level because the hierarchy is already present).
* If the default member is not in the Subselect, then the evaluation is either on *AllMember* or another given member for a slicing hierarchy.
* In other words, defining a filter on hierarchy members using a Subselect has no visible effect unless It excludes the MDX default member
of that hierarchy or if the hierarchy is expressed on any axis of the top-selection.
### Definition
For each hierarchy, it is possible to define a collection of default member candidates in the MDX context as illustrated below:
```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
final IMdxContext myMdxContext =
MdxContext.builder()
.withDefaultMember(
new LevelIdentifier("Currency", "Currency", IHierarchy.ALL_LEVEL),
IHierarchy.ALLMEMBER,
"EUR")
.withDefaultMember(
new LevelIdentifier("Currency", "Currency", IHierarchy.ALL_LEVEL),
IHierarchy.ALLMEMBER,
"USD")
.build();
```
A default member should be a unique name starting with the unique name of its hierarchy or at least be an
identifier ([https://learn.microsoft.com/en-us/sql/mdx/identifiers-mdx?view=sql-server-ver16](https://learn.microsoft.com/en-us/sql/mdx/identifiers-mdx?view=sql-server-ver16)) that can be resolved by the
MDX engine to avoid query-time error.
It is possible to define several default member candidates. However, for a specific query, only **one**
default member is used: the first member of the enumeration that actually exists in the cube after subcubes have been applied.
For instance in the above example:
* If both *EUR* and *USD* are granted, the MDX engine uses *EUR* as the default member.
* If the subcube properties exclude the *EUR* member, the MDX engine uses the *USD* member as the default member.
* If both *EUR* and *USD* are excluded, the MDX engine will not use any mdx default member. In this case, the default
member would be *AllMember*.
> For a slicer hierarchy that does not contain the `ALL` level, the default member is the member selected by default as the filter
> for that hierarchy.
The MDX context can be set through query context values at query time as illustrated below:
It can also be set in Atoti context values,
```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
pivot.getContext().set(IMdxContext.class, myMdxContext);
```
and in the cube description as well.
```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
StartBuilding.cube("EquityDerivativesCube")
.withSingleLevelDimensions("Trade")
.withDimension("Booking")
.withHierarchy("Desk")
.withLevelOfSameName()
.withSingleLevelDimension("Currency")
.withSharedContextValue(myMdxContext);
```
> You can also specify the DefaultMember property for a security role when building the `IEntitlementsProvider`.
### Examples
Let us consider the following example with the hierarchies `[Booking].[Desk]` and `[Currency].[Currency]`
where the default member on the *Currency* hierarchy is *EUR*.
By definition, when the *Currency* hierarchy is expressed on any axis (ROWS, COLUMNS, or WHERE), the default member is not taken
into account. For instance the following query
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT
NON EMPTY Hierarchize(
DrilldownLevel(
[Currency].[Currency].[ALL].[AllMember]
)
) ON ROWS,
NON EMPTY CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM [EquityDerivativesCube]
```
produces:
and
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT NON EMPTY CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM [EquityDerivativesCube]
WHERE [Currency].[Currency].[ALL].[AllMember]
```
produces:
However, notice that the same query executed by a user for which *EUR* is not granted (e.g. excluded by subcube properties of the security configuration),
would produce the following result:
In this case, as *EUR* is not available for this particular user, the default member will be the *AllMember* displayed as Total, which corresponds
to the aggregation of *GBP*, *USD*, *JPY*, *CHF*, *ZAR*.
#### Hiding the *Currency* Hierarchy
Removing the *Currency* hierarchy from the query will force the evaluation on the default member *EUR*. This is equivalent to executing the following query:
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT NON EMPTY CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM [EquityDerivativesCube]
WHERE [Currency].[Currency].[ALL].[AllMember].[EUR]
```
or equivalently using a Subselect:
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT NON EMPTY CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM (
SELECT
[Currency].[Currency].[ALL].[AllMember].[EUR]
ON COLUMNS
FROM [EquityDerivativesCube]
)
```
#### Multiple Selections (Subselect)
When an MDX query involves a Subselect, the MDX engine first evaluates the Subselection,
then applies some filters to the cube based on the results of this Subselection before finally evaluating the top SELECT.
For instance, consider the following query:
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM (
SELECT {
[Booking].[Desk].[ALL].[AllMember].[N/A],
[Booking].[Desk].[ALL].[AllMember].[LegalEntityA]
} ON COLUMNS
FROM [EquityDerivativesCube]
)
```
The MDX engine first evaluates the Subselection
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT {
[Booking].[Desk].[ALL].[AllMember].[N/A],
[Booking].[Desk].[ALL].[AllMember].[LegalEntityA]
} ON COLUMNS
FROM [EquityDerivativesCube]
```
then grants only *LegalEntityA* and *N/A* members as they are the result of the Subselection.
As a result, the evaluation of the top-level Selection
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM /*...*/
```
results in:
The above query filters on *LegalEntityA* and *N/A* for the *Desk* level (because of the selection),
and on *EUR* for the *Currency* level (because it is the MDX default member).
##### Subselect Granting a Default Member
The following query includes a Subselect granting *EUR*, *CHF*, *GBP*, *JPY*, and *USD*.
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT
NON EMPTY CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM (
SELECT
{
[Currency].[Currency].[ALL].[AllMember].[CHF],
[Currency].[Currency].[ALL].[AllMember].[EUR],
[Currency].[Currency].[ALL].[AllMember].[GBP],
[Currency].[Currency].[ALL].[AllMember].[JPY],
[Currency].[Currency].[ALL].[AllMember].[USD]
} ON COLUMNS
FROM [EquityDerivativesCube]
)
```
Notice that the obtained result is:
Here, the MDX engine forces the evaluation on *EUR* because:
* According to the Subselect, *EUR* is granted.
* However, the *Currency* hierarchy is not present on any axis of the top-level SELECT.
This results in an aggregation on *EUR* only, and not *EUR*, *CHF*, *GBP*, *JPY* and *USD*.
By adding the *Currency* hierarchy on the query axis (ROWS for instance):
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT
NON EMPTY CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS,
NON EMPTY Hierarchize(
DrilldownLevel(
[Currency].[Currency].[ALL].[AllMember]
)
) ON ROWS
FROM (
SELECT
{
[Currency].[Currency].[ALL].[AllMember].[CHF],
[Currency].[Currency].[ALL].[AllMember].[EUR],
[Currency].[Currency].[ALL].[AllMember].[GBP],
[Currency].[Currency].[ALL].[AllMember].[JPY],
[Currency].[Currency].[ALL].[AllMember].[USD]
} ON COLUMNS
FROM [EquityDerivativesCube]
)
```
The result becomes:
##### Subselect Excluding Default Members
The following query grants *CHF*, *ZAR*, *GBP*, *JPY*, *USD*.
```MDX theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
SELECT
NON EMPTY CrossJoin(
[Measures].[contributors.COUNT],
Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
)
) ON COLUMNS
FROM (
SELECT
{
[Currency].[Currency].[ALL].[AllMember].[CHF],
[Currency].[Currency].[ALL].[AllMember].[ZAR],
[Currency].[Currency].[ALL].[AllMember].[GBP],
[Currency].[Currency].[ALL].[AllMember].[JPY],
[Currency].[Currency].[ALL].[AllMember].[USD]
} ON COLUMNS
FROM [EquityDerivativesCube]
)
```
Since *EUR* is not granted, the default member for the top-level SELECT is then *AllMember*.
