> ## 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 Engine Configuration

## Axis Position Limit

The axis position limit is the limit of positions allowed per axis in the result.
A value equal or smaller than 0 will disable any limit.

Note that this check is performed after the query evaluation,
when the number of positions per axis of the result has been determined.
Functions like `NON EMPTY`, `light cross-join` and `Filter` affect the position size during query evaluation
and make impossible any initial estimations.
This will thus prevent too big results to be sent to the client, but not prevent too big queries on the server itself.

No axis position limit is set by default (i.e. default value is -1).

## Aggressive Formula Evaluation

Aggressive formula evaluation restricts formula evaluation on the crossjoined tuples actually existing in the cube,
instead of all possible tuples.

Suppose there are two hierarchies, and it is necessary to compute a formula
on the cross-join of the lowest level of each hierarchy, each level having 10,000 members.
According to the MDX specification, the formula must be evaluated on 100,000,000
combinations, even if there are only 10,000 existing combinations in the cube
(this is a "worst" case where we only have one-to-one relationships).
Formulas often return `Null` on non-existing combinations.
In this example, there are 90,000,000 useless evaluations which could be avoided.

The MDX engine is not able to detect if a formula always return `Null` on non-existing combinations,
but we can give it a hint as to where we want the formula evaluated.
For that we have added a new attribute to the calculated members: `COMPANION`.

The MDX engine will, as a minimum, evaluate the calculated member on the locations where its companion
has a non-null value.

When the companion is not explicitly specified, the MDX engine will try to find the optimal companion by analyzing
the expression, resolving underlying calculated members if needed.
It is guaranteed that companion member belongs to the same hierarchy.
The companion search algorithm falls back to the default member of the calculated member's hierarchy
if no better candidate exists.

In most cases the default companion for the calculated measures is `contributors.COUNT`,
effectively transforming the companion rule to "evaluate where the combination exists".

Given that, in aggressive formula evaluation mode the following calculated measure

```mdx theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
WITH MEMBER [Measures].[calculated_measure] AS [Measures].[pnl.SUM] + 1
```

could evaluate to `null` for all cells where `pnl.SUM` is `null`,
because `[Measures].[pnl.SUM]` will be chosen as a companion.

Note that even if aggressive formula evaluation is enabled, it is possible to disable it for a specific measure by
setting the companion to `NULL`:

```mdx theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
WITH MEMBER [Measures].[calculated_measure] AS ...,
    COMPANION = NULL
```

Aggressive formula evaluation should not be used when all the measures
the formula uses return null and any companion candidate would return null, but the formula does not return a null value.
For example, a measure where you add 1 to the contributors count,
and want the value 1 to appear where contributors count is null.

Note that there are constraints on the choice of the companion.
The expression of the companion must be the identifier (i.e. unique name) of a non-calculated member.
Expressions like `[Underlyings].[UnderlierCurrency].[EUR].NextMember` are not supported.

Aggressive formula evaluation is enabled by default.

## Calculated Members

It is possible to define calculated members that will be available in all queries having this MDX context,
by providing a `List<ICalculatedMemberDescription>`.

The default calculated members is an empty list.

## Case Sensitive

You can define if the MDX engine is case-sensitive.
This must be set to `true` when two members have unique names that are case-insensitive equal.
For instance `[Underlyings].[ALL].[AllMember].[EUR]` and `[Underlyings].[ALL].[AllMember].[eur]`.
If it is set to `false` and two members are case-insensitive equal the MDX engine will raise an
exception when running a query on any of the members.

By default, the MDX engine is case-sensitive.

## Default Members

The default members is a `Map<HierarchyIdentifier, List<String>>` that maps each hierarchy identifier to a list of
possible default members.

By default, the default members is an empty map.

See [Filtering](./filtering) for more details.

## Unique Name

The `memberNamePathStyle` property chooses between two styles of unique names:

* if `true`, the engine uses `[dim].[hier].[level1].[member1].[member2].[member3]`
* if `false`, the engine uses  `[dim].[hier].[level3].&[member3]`
  The default value is `true`.

## Epoch Dimension

It is possible to disable the [Epoch dimension](../concepts/data_versioning#epochs-and-epoch-management) from the MDX context.
That allows having the epoch dimension for the cube, but not showing it to some users,
since the MDX context can be attached to a role.

By default, the epoch dimension is enabled.

## Formatters

You can define a formatter for each measure or level by providing a `Map<String, IFormatter>`
which links the OLAP unique name to its formatter.

By default, there are no specific formatters (empty map).

## Hide Grand Totals

This allows you to remove the grand totals from axes.
The `int[] hiddenGrandTotals` array contains the ordinal of the axes for which the grand totals are removed.
This is equivalent of the Excel feature 'Remove Grand Total'.

By default, there are no grand totals removed (empty array).

## Hide Subtotals

This allows you to remove subtotals from axes.
The `List<String> hiddenSubtotals` list contains the unique names of levels for which the subtotals are removed.
This is equivalent of the Excel feature 'Remove Subtotals'.

By default, there are no subtotals removed (empty list).

## Ignore Invalid Measures

This option controls the behavior when encountering invalid measures in MDX queries. When set to `true`, the MDX engine silently ignores those measures rather than failing the query.
When set to `false`, the engine throws an `MdxException` that identifies the missing or unauthorized measure.

The option is `false` by default so that Atoti matches Microsoft Analysis Services' behavior.

When an invalid measure is ignored (`ignoreInvalidMeasures = true`):

* If the measure is part of a tuple, the whole tuple is ignored.
* If the measure is referenced in a slicer (WHERE clause), the query returns an empty result.
* The measure evaluates to `null` in calculated measure, e.g. `WITH MEMBER [Measures].[CALC] AS [Measures].[InvalidMeasure] + 1` is equal to `1` everywhere.

Combined with `ISubCubePropertiesBuilder#grantMeasure` to hide measures from certain users without breaking their shared dashboards.

## Ignore Invalid Members

This option controls the behavior when encountering invalid hierarchy members in MDX queries. When set to `true`, the MDX engine will silently ignore members that do not exist or to which the user has no access.
When set to `false`, the engine throws an `MdxException`.

Ignore Invalid Members is `true` by default.

When an invalid member is ignored (`ignoreInvalidMembers = true`):

* If the invalid member is part of a tuple in a set, the entire tuple is removed from the set
* If the invalid member is in a slicer (WHERE clause), the query returns an empty result

## Light Crossjoin

Most OLAP clients, and Excel in particular support *crossjoin* queries,
the ability to concatenate two or more dimensions on an analysis axis, often the rows of a pivot table.
This powerful feature allows users to set up arbitrary reports from a set of dimensions,
virtually introducing hierarchies on the fly.

The Light Cross Join option in Atoti makes sure that irrelevant subtotals of a cross join will not be returned,
the same way that Excel handles the cross-join feature basically.

Let's take an example where we cross-join a single-level hierarchy of AllMember\Product and a two-level hierarchy
AllMember\City\Desk.

```text theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
Product                     Desk

AllMember                   AllMember
   |_______OPTION               |_______London
   |_______SWAP                 |        |____Desk A
   |_______BOND                 |        |____Desk B
                                |
                                |_______New York
                                         |____Desk C
```

Without the light cross-join concept, we should return all the rows:

| Path                                                     | Notes                                                                  |
| -------------------------------------------------------- | ---------------------------------------------------------------------- |
| Product.AllMember, Desk.AllMember                        | Grand Total                                                            |
| Product.AllMember, Desk.AllMember.London                 | London Total<br />**Would not be displayed in an Excel Pivot Table**   |
| Product.AllMember, Desk.AllMember.London.Desk A          | Desk A Total<br />**Would not be displayed in an Excel Pivot Table**   |
| Product.AllMember, Desk.AllMember.London.Desk B          | Desk B Total<br />**Would not be displayed in an Excel Pivot Table**   |
| Product.AllMember, Desk.AllMember.New York               | New York Total<br />**Would not be displayed in an Excel Pivot Table** |
| Product.AllMember, Desk.AllMember.New York.Desk C        | Desk C Total<br />**Would not be displayed in an Excel Pivot Table**   |
| Product.AllMember.OPTION, Desk.AllMember                 | OPTION Total                                                           |
| Product.AllMember.OPTION, Desk.AllMember.London          | OPTION Total for London                                                |
| Product.AllMember.OPTION, Desk.AllMember.London.Desk A   | OPTION Total for Desk A                                                |
| Product.AllMember.OPTION, Desk.AllMember.London.Desk B   | OPTION Total for Desk B                                                |
| Product.AllMember.OPTION, Desk.AllMember.New York        | OPTION Total for New York                                              |
| Product.AllMember.OPTION, Desk.AllMember.New York.Desk C | OPTION Total for Desk C                                                |
| Product.AllMember.SWAP, Desk.AllMember                   | SWAP Total                                                             |
| Product.AllMember.SWAP, Desk.AllMember.London            | SWAP Total for London                                                  |
| Product.AllMember.SWAP, Desk.AllMember.London.Desk A     | SWAP Total for Desk A                                                  |
| Product.AllMember.SWAP, Desk.AllMember.London.Desk B     | SWAP Total for Desk B                                                  |
| Product.AllMember.SWAP, Desk.AllMember.New York          | SWAP Total for New York                                                |
| Product.AllMember.SWAP, Desk.AllMember.New York.Desk C   | SWAP Total for Desk C                                                  |
| Product.AllMember.BOND, Desk.AllMember                   | BOND Total                                                             |
| Product.AllMember.BOND, Desk.AllMember.London            | BOND Total for London                                                  |
| Product.AllMember.BOND, Desk.AllMember.London.Desk A     | BOND Total for Desk A                                                  |
| Product.AllMember.BOND, Desk.AllMember.London.Desk B     | BOND Total for Desk B                                                  |
| Product.AllMember.BOND, Desk.AllMember.New York          | BOND Total for New York                                                |
| Product.AllMember.BOND, Desk.AllMember.New York.Desk C   | BOND Total for Desk C                                                  |

With the light cross-join activated, we do not compute the additional subtotals that would not be displayed in an
Excel Pivot Table, e.g. those that detail the second hierarchy but aggregate on the first hierarchy in the cross-join.
With the above example, it would save the computation of 5 cells, the result of the light cross-join being:

| Path                                                     | Notes                     |
| -------------------------------------------------------- | ------------------------- |
| Product.AllMember, Desk.AllMember                        | Grand Total               |
| Product.AllMember.OPTION, Desk.AllMember                 | OPTION Total              |
| Product.AllMember.OPTION, Desk.AllMember.London          | OPTION Total for London   |
| Product.AllMember.OPTION, Desk.AllMember.London.Desk A   | OPTION Total for Desk A   |
| Product.AllMember.OPTION, Desk.AllMember.London.Desk B   | OPTION Total for Desk B   |
| Product.AllMember.OPTION, Desk.AllMember.New York        | OPTION Total for New York |
| Product.AllMember.OPTION, Desk.AllMember.New York.Desk C | OPTION Total for Desk C   |
| Product.AllMember.SWAP, Desk.AllMember                   | SWAP Total                |
| Product.AllMember.SWAP, Desk.AllMember.London            | SWAP Total for London     |
| Product.AllMember.SWAP, Desk.AllMember.London.Desk A     | SWAP Total for Desk A     |
| Product.AllMember.SWAP, Desk.AllMember.London.Desk B     | SWAP Total for Desk B     |
| Product.AllMember.SWAP, Desk.AllMember.New York          | SWAP Total for New York   |
| Product.AllMember.SWAP, Desk.AllMember.New York.Desk C   | SWAP Total for Desk C     |
| Product.AllMember.BOND, Desk.AllMember                   | BOND Total                |
| Product.AllMember.BOND, Desk.AllMember.London            | BOND Total for London     |
| Product.AllMember.BOND, Desk.AllMember.London.Desk A     | BOND Total for Desk A     |
| Product.AllMember.BOND, Desk.AllMember.London.Desk B     | BOND Total for Desk B     |
| Product.AllMember.BOND, Desk.AllMember.New York          | BOND Total for New York   |
| Product.AllMember.BOND, Desk.AllMember.New York.Desk C   | BOND Total for Desk C     |

Light crossjoin is enabled by default.

## N-ary Crossjoin

<small>Since 6.1.19</small>

By default, an MDX `Crossjoin` of more than two sets is evaluated as a chain of nested binary crossjoins.
For example, `Crossjoin(A, B, C)` is internally rewritten as `Crossjoin(Crossjoin(A, B), C)`.

When the n-ary crossjoin optimization is enabled, some of the crossjoin expressions like `Crossjoin(myLevel1.Members, myLevel2.Members, myLevel3.Members)`
are evaluated directly without binary nesting.
This can reduce execution time and lower memory allocations for queries that cross-join many levels.

The optimization is disabled by default. It can be enabled by setting an additional property in the MDX context:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
mdxContext.put(MdxContext.NARY_CROSSJOIN, "true");
// or
StartBuilding.mdxContext().withAdditionalProperty(MdxContext.NARY_CROSSJOIN, "true").build();
```

## Named sets

This allows you to define MDX named sets available for every query having this MDX context.
The named sets are provided as a `List<INamedSetDescription>`.

The default is an empty list.

## Result Limit

The result limit is the maximal number of cells in the result of queries. If set to -1 no limit is set.

Note that this check is performed after the query evaluation, when the number of result cells is determined.
Functions like `NON EMPTY`, `light crossjoin` and `Filter` affect the result cell number during query evaluation
and make impossible the use of initial estimation.

This will thus prevent too big results to be sent to the client, but not prevent too big queries on the server itself.
For preventing too big queries from disrupting the whole server performances,
you can use the [`IQueriesResultLimit` context value](../cube/context_values#iqueriesresultlimit).

## Dimension and hierarchy visibilities

You can use the MDX context to change the visibilities of dimensions and hierarchies:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
StartBuilding.mdxContext()
    .overrideDimensionVisibility(TECHNICAL_DATA_DIMENSION, false)
    .overrideHierarchyVisibility(TRADE_DIMENSION, TRADE_HIERARCHY, true)
    .build();
```

The MDX context and other context values can be customized based on user's roles using the [`IEntitlementsProvider` entitlements provider](security/atoti_access_control).