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 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<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 Members
This option will prevent the engine from throwing an MdxException
if encountering an invalid (non-existing) 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 set up arbitrary reports from a set of dimensions, virtually introducing hierarchies on the fly.
The Light Cross Join option in ActivePivot 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.
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 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.
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 customized based on user's roles using the IEntitlementsProvider
entitlements provider.