Generated MDX is incorrect with multiple dimensions on row or column Axis.

[swaterbury-CW]

When using:

  for dim in dims:
        elements_dict = tm1.elements.get_leaf_elements(dimension_name=dim, hierarchy_name=dim)
        print(dim)
        element_sample = elements_dict
        if elements_dict.__len__() > 10:
            k = int(round(len(elements_dict) * percent, 0))
            element_sample = random.sample(elements_dict, k=1)

        if dim == dims[0]:
            for el in element_sample:
                mdx = mdx.add_member_tuple_to_columns(el.unique_name)
        else:
            for el in element_sample:
                mdx = mdx.add_member_tuple_to_rows(el.unique_name)

MDX generated follows the format on row Axis of

{([Dim1].[Dim1].[Dim1Element]),([Dim2].[Dim2].[Dim2Element])}

This is an incorrect MDX string for multiple stacked dimensions and it fails to render. When manually modified to:

{[Dim1].[Dim1].[Dim1Element]}*{[Dim2].[Dim2].[Dim2Element]}

MDX Returns successfully and without error.

[rclapp]

The add_tuple_to_axis methods seem to add extra parenthesis when passing strings. It also seems to expect members rather than a tuple, which seems incorrect. As a workaround you can use the tuple methods to create tuple then add them to the axis

https://github.com/cubewise-code/mdxpy?tab=readme-ov-file#mdxtuple

My suggestion is that we either remove string support for the tuple axis functions. We could also make an update that supports tuples expressed as strings, but the way it is implemented right now it clearly doesn’t work.

From: swaterbury-CW @.> Reply-To: cubewise-code/mdxpy @.> Date: Tuesday, February 11, 2025 at 10:27 AM To: cubewise-code/mdxpy @.> Cc: Subscribed @.> Subject: [cubewise-code/mdxpy] Generated MDX is incorrect with multiple dimensions on row or column Axis. (Issue #55)

When using:

for dim in dims: elements_dict = tm1.elements.get_leaf_elements(dimension_name=dim, hierarchy_name=dim) print(dim) element_sample = elements_dict if elements_dict.len() > 10: k = int(round(len(elements_dict) * percent, 0)) element_sample = random.sample(elements_dict, k=1)

if dim == dims[0]:

    for el in element_sample:

        mdx = mdx.add_member_tuple_to_columns(el.unique_name)

else:

    for el in element_sample:

        mdx = mdx.add_member_tuple_to_rows(el.unique_name)

MDX generated follows the format on row Axis of

{([Dim1].[Dim1].[Dim1Element]),([Dim2].[Dim2].[Dim2Element])}

This is an incorrect MDX string for multiple stacked dimensions and it fails to render. When manually modified to:

{[Dim1].[Dim1].[Dim1Element]}*{[Dim2].[Dim2].[Dim2Element]}

MDX Returns successfully and without error.

— Reply to this email directly, view it on GitHubhttps://github.com/cubewise-code/mdxpy/issues/55, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEK7GZTVK5TQIIQS5GFAOH32PIJAHAVCNFSM6AAAAABW5KRV22VHI2DSMVQWIX3LMV43ASLTON2WKOZSHA2DKNZRGU3TEOI. You are receiving this because you are subscribed to this thread.Message ID: @.***>

[MariusWirtz]

@swaterbury-CW I think mdxpy is doing what it should do actually.

I think you are doing something illegal when you place a set of tuples with different dimensionality on one axis:

{
  ([Dim1].[Dim1].[Dim1Element]),
  ([Dim2].[Dim2].[Dim2Element])
} ON ROWS

It would be the same as this:

SELECT 
{
([Product].[Bike A]),
([Year].[2025]),
([Region].[Germany])
} ON ROWS
...

I think if you add MDX sets to the axes instead of MDX tuples you get what you need.

for dim in dims:
    elements_dict = tm1.elements.get_leaf_elements(dimension_name=dim, hierarchy_name=dim)
    print(dim)
    element_sample = elements_dict
    if elements_dict.__len__() > 10:
        k = int(round(len(elements_dict) * percent, 0))
        element_sample = random.sample(elements_dict, k=1)

    if dim == dims[0]:
        mdx_set = MdxHierarchySet.members([Member.of(el.unique_name) for el in element_sample])
        mdx = mdx.add_hierarchy_set_to_column_axis(mdx_set)
    else:
        mdx_set = MdxHierarchySet.members([Member.of(el.unique_name) for el in element_sample])
        mdx = mdx.add_hierarchy_set_to_row_axis(mdx_set)

This should produce an MDX like this:

SELECT 
{[Product].[Bike A], [Product].[Bike B], [Product].[Bike C]} *
{[Year].[2025]} *
{[Region].[Germany], [Region].[Austria], [Region].[Switzerland]}
 ON ROWS
...

[rclapp]

Good point Marius.

One thing that I think is best practice is to not use the add to axis functions directly with strings as you can get into these types of edge cases.

However, we currently don't do any validation in the creation of objects, but we could. I think it is a slippery slope. If we were to ensure tuples don't contain improper dimensionality, should we also validate the existence of a member? Do we want to lint MDX?

On Feb 12, 2025 4:56 AM, Marius Wirtz @.***> wrote:

@swaterbury-CWhttps://github.com/swaterbury-CW I think mdxpy is doing what it should do actually.

I think you are doing something illegal when you place a set of tuples with different dimensionality on one axis:

{ ([Dim1].[Dim1].[Dim1Element]), ([Dim2].[Dim2].[Dim2Element]) } ON ROWS

It would be the same as this:

SELECT { ([Product].[Bike A]), ([Year].[2025]), ([Region].[Germany]) } ON ROWS ...

I think if you add MDX sets to the axes instead of MDX tuples you get what you need.

for dim in dims: elements_dict = tm1.elements.get_leaf_elements(dimension_name=dim, hierarchy_name=dim) print(dim) element_sample = elements_dict if elements_dict.len() > 10: k = int(round(len(elements_dict) * percent, 0)) element_sample = random.sample(elements_dict, k=1)

if dim == dims[0]:
    mdx_set = MdxHierarchySet.members([Member.of(el.unique_name) for el in element_sample])
    mdx = mdx.add_hierarchy_set_to_column_axis(mdx_set)
else:
    mdx_set = MdxHierarchySet.members([Member.of(el.unique_name) for el in element_sample])
    mdx = mdx.add_hierarchy_set_to_row_axis(mdx_set)

This should produce an MDX like this:

SELECT {[Product].[Bike A], [Product].[Bike B], [Product].[Bike C]} * {[Year].[2025]} * {[Region].[Germany], [Region].[Austria], [Region].[Switzerland]} ON ROWS ...

— Reply to this email directly, view it on GitHubhttps://github.com/cubewise-code/mdxpy/issues/55#issuecomment-2653193834, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEK7GZT6U5OEATGXZUEXE2T2PMLDHAVCNFSM6AAAAABW5KRV22VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNJTGE4TGOBTGQ. You are receiving this because you commented.Message ID: @.***>