By Lukas Hillesheim, 31 December, 2022

Blog Article & Copyright by Lukas Hillesheim

This article describes how the Analysis Services Multidimensional engine implicitly expands an incomplete tuple such as ([East]) to a complete tuple such as ([East], [2018], [Amount], etc.). This knowledge is crucial to understand which automatic behaviors affect the way a cell value is computed.

1. General

A complete tuple expression is used to uniquely refer to a single cube cell and its value. The [Measures] dimension is discussed below. Example:

Figure 1 - Complete Tuple – All dimensions and hierarchies mentioned

If the tuple expression does not make use of all dimensions or hierarchies that the cube provides, the tuple expression is treated as incomplete. Example:

Figure 2 - Incomplete Tuple – Only [City] mentioned

In order to return a value, the engine must expand the incomplete tuple, implicitly using members for each missing hierarchy or dimension.

Many MDX users assume that the [ALL] member comes into play. The expansion - under this assumption - would look like this:

Figure 3 - Tuple with [ALL]-Members

However, this is not entirely true. Expansion often - but not always - uses the [ALL] member. It depends on the context where the incomplete tuple is called. The context - in the sense of MDX - can be thought of as a complete set of members of all participating hierarchies representing the full dimensionality of the cube. Due to completeness, the context can be used in the case of missing hierarchy members, as it happens in the expansion process. The context is controlled by:

- the schema of dimensions or hierarchies
- the schema of the cube
- the placement of tuple sets on axis 0 and axis 1
- the placement of tuple sets on the slicer axis
- through attribute relationships
- nested iterators

2. Controlling the Initial Context through the Dimension Schema

The schema of dimensions contributes with each hierarchy to complete the set of context members. This happens before execution. Each hierarchy has a default member. By default, the default member of a hierarchy is set to the [ALL] member, but can also be changed in SSDT. The default members of all hierarchies are added to the initial context.

Each hierarchy provides a default member. Without intervention, the default member is set to [ALL]. The developer is able to change the default member using SSDT. At the beginning - before the MDX-statement is run, each default member of each participating hierarchy is added to the Initial Context.

Figure 4 - AS DataTools-Project: Year-Hierarchy

Figure 4 - SSDT dimension editor: Year-Hierarchy

Figure 5 - Default Member for [Year]-Hierarchy

Figure 6 - Default: Default member not modified -> Default = [ALL]

Figure 6 - Default: no modification -> Default = [ALL]

Here are two examples with the [Year].[ALL] member. Both tuple expressions return the same value. In the first example, the [Year].[ALL]-member is implicitly called by expansion. In the second example, the [Year].[ALL]-member explicitly called. The engine returns the same cell value because the schema has not yet been modified.

Example 1. Implicit use of [Year].[ALL]-member:

Figure 7 - Implicit [Year].[ALL] member

Example 2. Explicit use of the [Year].[ALL]-member. The value remains the same; however, the [ALL] member appears on axis 0.

Figure 8 - Explicit [Year].[ALL]-member

The default member [ALL] for the [Year]-hierarchy can be changed in SSDT to a specific member such as [2018]:

Figure 9 - Dimension modification: default member for [Year]-hierarchy set to [2018]

Figure 9 - Schema modification: default member for [Year]-hierarchy set to [2018]

Example 3. After the schema change, the tuple expression returns 2058 instead of 7869:

Figure 10 - Modified tuple expansion regarding [Year].[2018]

Figure 10 - Tuple expansion regarding [Year].[2018]

From now on the tuple expansion would implicitly include be the [Year].[2018]-member:

Figure 11 – Tuple expansion after schema modification

Figure 11 - Tuple expansion regarding [Year].[2018]

3. Controlling the Initial Context through the Cube Schema

From a syntactical point of view there is no difference between the dimension [Measures] and any other dimension. The difference to [City], [Region], [Year], etc. is that the members of [Measures] are not contained in any hierarchy, but are directly assigned to the dimension. The [Measures]-dimension is missing a hierarchy. Without hierarchy, however, there can be no [ALL] member. The question is: why doesn't [Measures] have a hierarchy?

Consider the following scenario where the [Measures]-dimension contains two members, [Sum] and [PercentOfParent]:

- [Measures].[Sum]. Rollup using sum
- [Measures].[PercentOfParent]. Rollup using a division

For a potential [ALL]-member, the question would arise as which of the two rollup algorithms – sum and division – should be applied. However, neither algorithm would be useful: neither the use of [Sum] in the context of a division, nor the use of [PercentOfParent] in the context of an addition. The results of both methods would be incomprehensible to the user. Since the members [Amount], [Count], etc. are part of the [Measures] dimension, but cannot be meaningfully organized within a hierarchy, there is no hierarchy in the [Measures] dimension and thus no [ALL] member.

Instead, in the [Measures]-dimension, one of the members is set as the default member. This is done via the properties of the cube:

Figure 12 - Cube Schema

By default, the DefaultMeasure-property is not set. In this case, the cube takes the first member of the [Measures]-dimension – in the example it´s the [Amount]-member – and sets it as default. The default behavior can be changed as follows:

Figure 13 - Cube Schema: Modify Default Member

After the change would have been applied, the "Berlin" query would show a different result:

Figure 14 - Modified tuple expansion regarding [Year].[2018] and [Measures].[Count]

Figure 14 - Tuple expansion regarding [Year].[2018] and [Measures].[Count]

Now, the tuple expansion would implicitly consider the [2018]-member and the [Count]-member:

Figure 15 - Tuple expansion regarding [Year].[2018] and [Measures].[Count]

4. Control the runtime context through the tuple sets on Axis 0, Axis 1 and Slicer Axis

A tuple set can be placed on each query axis. The hierarchies used in the tuple sets with their respective members contribute to the expansion. Syntactically, Axis 0, 1 and the Axis slicers are treated equally. The only difference between axis 0/1 and the slicer axis is that the tuples on the slicer axis slicer are not used for display.

In the example below, the following tupels are used:

- a 2-dimensional tuple set with 2 tuples on axis 0
- a 1-dimensional tuple set with 2 tuples on axis 1
- a 1-dimensional tuple on the slicer axis

Please note that there are two syntax variants regarding the slicer axis. The slicer can be a single tuple or a tuple set.

Figure 16 - Runtime context

Figure 17 - Matrix (result) with 4 cells

In the example Analysis Services returns four cells with the values 13, 15, 15, and 12. For the generation of the first cell with the value 13, tuple expansion results in the following:

Figure 18 - Cell 1: expanded tuple

For the generation of the second cell with the value 15, tuple expansion results in the following:

Figure 19 - Cell 2: expanded tuple

5. Expansion process for Nested Contexts

The generation of the output - the grid to be displayed from section 4. - can be thought as a process that is run within a loop. The tuples on axis 0, axis 1 and slicer axis contribute to the expansion as current tuples. In other words, iteration within the generation process changes the context for the respective tuple to be evaluated, thereby modifying the tuple expansion process.

There are conceptually two contexts in the following example: the initial context is composed before execution and contains the members that have been set as default by the dimension and cube schema. The runtime context is created during the iteration. The interaction of both contexts can be thought of as an inheritance process in which the runtime context as the last applied context has an overriding effect (last-win rule). As a result of merging both contexts, a result context is created.

From the MDX user's point of view, the effect of context nesting is that all members that he has not explicitly placed in the tuple expressions are implicitly taken from the result context and used for expansion.

The following OOP pseudo-code illustrates how to generate the matrix within an iteration:

Figure 20 - OOP-Pseudocode: Matrix generation

- A function "GetMatrix" generates the grid, i.e. the visual output
- First, the initial context is built using all default members from the schema
- The user places tuple sets on axis 0, axis 1 and the axis slicer
- The triple-nested loop iterates over all tuple sets
- The "GetCellValue"-function is called. All members of the current tuple are passed along with the initial context

Figure 21 - Compute cell value regarding current tuple and context

- The "GetCellValue"-function returns the value for a specific cell
- First, all members of the initial context are added to the tuple
- Then the current members are added, e.g. [Berlin], [2018] etc.
- This results in overlaps; because for the [City]-hierarchy the default member [ALL] from the initial context already exists. The pseudo-function "AddOrOverride" is intended to demonstrate that the current members overwrite existing members from the initial context. Effectively only those members that are not already included in the list of current members are taken from the initial context.

The OOP pseudocode contains the expression "CurrentMember" as in "t1. Hierarchies[0].CurrentMember". This is to show that there is a current member in each hierarchy at any given time, and that this CurrentMember is modified at run time as part of an iteration. "CurrentMember" also exists as a function in the MDX vocabulary. Here, the function has the same task as "CurrentMember" in the OOP pseudocode: it should return the current member for each hierarchy.

Definition of the expansion process: Tuple expansion uses all members of the current tuple along with the CurrentMembers of the result context to compose a complete tuple expression.

6. Controlling the Runtime Context through Attribute Relationships

In the following MDX example, the "CurrentMember"-function is used to "actively" access the current members of the [Region] and [Year] hierarchies and represent them as calculated members. Please note that a member has no representable value. For example, to display "East", the Name property of the [East]-member must be used.

Figure 22 - Attribute relationships: impact on CurrentMember

The result is surprising: while [Year].CurrentMember always returns the [ALL]-member, [Region].CurrentMember changes with each [City] member. This is because [City] and [Region] are both part of the [dCustomer]-dimension and are affected by the attribute relationship between [City] and [Region]. In contrast, [City] and [Year] are in different dimensions and, by definition, cannot be affected by any attribute relationship.

The effect of an attribute relationship on a CurrentMember is that – starting from the N-side – the CurrentMember of the 1-side is modified. So while an iteration takes place over the [City] members, the attribute relationship in the background constantly changes the [Region].CurrentMember.

7. Controlling the Runtime Context through Iterators

Section 5. shows how Initial Context and Runtime Context affect the tuple expansion process. It´s worth to mention that context nesting does not end at the Runtime Context, but proceeds with each iterator creating its own new context.

As an example, the MDX function "Generate" is used below. With the help of "Generate", a set can be iterated over and a second set expression can be applied per loop instance. Of the two syntax variants offered by "Generate", the variant is used in which the results of the respective loop passes are concatenated into a string. The separator in this example is the comma.

In the example, "Generate" returns the [City] members that belong to the respective region. At this point, I do not want to go into the details of the relatively complex function "Generate", but refer to the red marked place in the code with the expression "[City].CurrentMember". If the MDX function "Generate" is considered as a functional counterpart to a loop, then the expression "[City].CurrentMember" would be placed in the loop block of the iterator and returns the current item of the loop.

The example shows that "Generate" creates a new modified context in which there is in fact an ever changing current [City]-member.

Figure 23 - Context nesting through iterators

A last hint to the problem of context nesting and the problem of loop variables. Consider the following OOP pseudocode:

Figure 24 - Nested iterators & unique variable names

In the OOP-pseudo-code there exist two variables, differently named, each pointing to a unique loop instance. Since in MDX, "CurrentMember" is the functional equivalent of a loop variable, a problem arises, because the expression "[City].CurrentMember" cannot uniquely reference a specific loop. In this case, other patterns and techniques would be necessary. However, the possible solutions for this are not part of the article.

MDX - Expansion of incomplete Tuple Expressions