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 CubeFilters or by rewriting the initial query location so as to express the initial 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 CubeFilters 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:

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

pivot.getContext().set(IMdxContext.class, myMdxContext);

and in the cube description as well.

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

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

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:

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:

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:

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

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

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.

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

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.

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.

Documentation Index

​Introduction

​MDX Filtering

​Slicer Axis

​Impact of Slicer on Query Location

​Subselect Expression

​MDX Default Members

​Definition

​Examples

​Hiding the Currency Hierarchy

​Multiple Selections (Subselect)