Improve PAW-generated MDX for hierarchy selections

Dear team,

Summary

Improve MDX generation in Planning Analytics Workspace so that, where the resulting set semantics are unambiguous, PAW can generate simpler and more maintainable MDX instead of preserving the complete sequence of UI operations.

The goal is not to replace DRILLDOWNMEMBER in general, but to optimize the resulting set expression once the final selection is known.

Description

PAW currently translates hierarchy selections and other Set Editor interactions into a sequence of MDX operations. The generated MDX is valid, but in some cases it can be unnecessarily complex because it reflects the individual UI operations rather than the final set semantics.

For example, in the 24Retail model, the Organization hierarchy is:

Total Company
├── East Region
├── 200
├── West Region
└── Canada

If the user wants the direct children of Total Company, excluding member 200, the intended result is:

EXCEPT(
    [Organization].[organization].[Total Company].Children,
    {[Organization].[organization].[200]}
)

The equivalent expression using DESCENDANTS is:

EXCEPT(
    DESCENDANTS(
        [Organization].[organization].[Total Company],
        1,
        SELF
    ),
    {[Organization].[organization].[200]}
)

For distance 1, .Children is the preferred and more concise form. DESCENDANTS(..., 1, SELF) can be used when a specific level/distance is required.

The issue is not that the MDX generated by PAW is invalid. The issue is that PAW can preserve the sequence of operations used to arrive at the result instead of recognizing a simpler semantic pattern.

Important distinction

The optimization should be based on the final result-set semantics, not on interpreting each individual click.

For example:

Total Company + Children
→ DRILLDOWNMEMBER is correct because the parent remains in the set.

Children of Total Company, excluding 200
→ EXCEPT(Parent.Children, {200}) is a more direct representation.

Therefore, this request is not proposing a global replacement of DRILLDOWNMEMBER.

Proposed enhancement

Introduce a semantic optimization step for generated MDX where the resulting set can be represented safely by a simpler expression.

Examples of recognizable patterns could include:

• Direct children of a parent

• Direct children of a parent minus one or more excluded members

• Descendants at a specific distance/level

• Keep-of-level combined with exclusions

• Other cases where the final result can be expressed more directly without enumerating the remaining members

For example, instead of retaining a verbose operation sequence, PAW could recognize:

Parent.Children - {Excluded Members}

and generate:

EXCEPT(
    [Parent].Children,
    {[Excluded Members]}
)

where the semantics are unambiguous.

Implementation consideration

This optimization does not necessarily need to happen after every individual Set Editor click.

Possible points for optimization include:

• when leaving the Set Editor

• when saving a Named Set or Subset

• when finalizing the MDX expression

• through an explicit "Simplify MDX" option

This would allow PAW to preserve the interactive editing model while still producing cleaner MDX at an appropriate point.

Round-trip consideration

PAW MDX is not only an output format; it can also represent the state of an interactive view or Set Editor.

A semantic simplification could therefore make it more difficult for PAW to reconstruct the exact UI state from the generated expression.

If a simplified expression cannot be reliably mapped back to the interactive Set Editor state, PAW should retain the original expression or treat the simplified expression as custom MDX rather than breaking the editing experience.

Acceptance criteria

1. If the resulting set is equivalent to Parent.Children minus one or more explicitly excluded members, PAW should be able to generate a concise equivalent expression such as EXCEPT(Parent.Children, {...}).

2. PAW should not unnecessarily enumerate all remaining sibling members when a dynamic set expression can represent the same result.

3. Additional members subsequently added below the parent should be included dynamically when the generated expression is based on .Children or an equivalent dynamic construct.

4. DRILLDOWNMEMBER should remain available and be retained when the parent itself is intentionally part of the resulting set.

5. The simplified expression must produce exactly the same resulting member set as the original expression.

6. The optimization should work with multiple excluded members, not only a single exclusion.

7. Where ordering is relevant, simplification should preserve the expected member order or explicitly use/document the appropriate ordering semantics.

8. If PAW cannot safely reconstruct the interactive state from the simplified expression, it should fall back to the original expression or clearly treat the expression as custom MDX.

Expected benefits

• Simpler and more readable generated MDX

• Easier troubleshooting and maintenance

• Less unnecessarily verbose MDX

• More robust expressions based on hierarchy semantics rather than enumerated results

• Better alignment between the generated MDX and the resulting set

The key principle is:

Optimize the result set, don't reinterpret every click.

The challenge of determining user intent from individual UI interactions remains non-trivial. However, once the resulting selection matches a well-defined semantic pattern, PAW should be able to recognize and represent that pattern more efficiently.

Thanks in advance.

With best regards,
Vitalij