MDX Engine Configuration

The MDX Engine can be configured through the IMdxContext context value. It is easy to configure using the fluent builder StartBuilding.mdxContext(). This article details the options and their meanings.

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 specified, the MDX engine takes the default member of the hierarchy of the calculated member. In most cases the default companion for the calculated measures is contributors.COUNT, effectively transforming the companion rule to "evaluate where the combination exists".

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:

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<String, List<String>> that maps each hierarchy unique name to a list of possible default members.

By default, the default members is an empty map.

Epoch Dimension

It is possible to disable the Epoch dimension 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<Strin> 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 Members

This option will prevent the engine from throwing an MdxException if encountering an invalid (inexisting) member.

Ignore Invalid Member is true by default.

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 setup arbitrary reports from a set of dimensions, virtually introducing hierarchies on the fly.

The Light Cross Join option in ActivePivot makes sure that irrelevant sub totals 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.

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 Would not be displayed in an Excel Pivot Table
Product.AllMember, Desk.AllMember.London.Desk A	Desk A Total Would not be displayed in an Excel Pivot Table
Product.AllMember, Desk.AllMember.London.Desk B	Desk B Total Would not be displayed in an Excel Pivot Table
Product.AllMember, Desk.AllMember.New York	New York Total Would not be displayed in an Excel Pivot Table
Product.AllMember, Desk.AllMember.New York.Desk C	Desk C Total 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 sub-totals 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.

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.

Dimension and hierarchy visibilities

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

StartBuilding.mdxContext()
        .overrideDimensionVisibility(TECHNICAL_DATA_DIMENSION, false)
        .overrideHierarchyVisibility(TRADE_DIMENSION, TRADE_HIERARCHY, true)
        .build();

The MDX context and other context values can be customised based on user's roles using the IEntitlementsProvider entitlements provider.

Axis Position Limit​

Aggressive Formula Evaluation​

Calculated Members​

Case Sensitive​

Default Members​

Epoch Dimension​

Formatters​

Hide Grand Totals​

Hide Subtotals​

Ignore Invalid Members​

Light CrossJoin​

Named sets​

Result Limit​

Dimension and hierarchy visibilities​