Planning Analytics

We have discovered what we believe to be a pretty significant defect in the TM1 server's MDX engine. Before I go to the trouble of creating a support ticket and reproducible test case I thought it would be good to first see if other customers and partners are also aware of this current MDX limitation and if @Hubert Heijkers or @STUART KING are aware of the defect and if plans already exist to fix it.

I will try and be as clear and succinct as possible in explaining the issue.

In a dimension containing alternate hierarches when querying a cell value via MDX for an intersection containing a leaf member ​if any hierarchy ​other than the same named hierarchy is addressed for the member then the query will return no data. For consolidated elements the MDX works as expected and alternate hierarchies can be addressed and data is returned. Since leaf elements are "shared resources" what should happen is that regardless of which alternate hierarchy is referenced the same value shoudl be returned (as long as the leaf element in question is a member of that hierarchy).

Let's try and make a very simple example. We have a dimension Product which contains hierarchies Product, Brand, Color, Size and Leaves. Product ABC is a leaf and is present in all hierarchies. The dimension is used in a cube called Sales which for simplicity just contains 2 other dimensions, Time and Sales Measure. The use case is that we want a member set query on the Time dimension to return the periods in which there are sales of ABC. So the MDX looks like this ...

{Filter(
    {TM1FilterByLevel(
        {TM1SubsetAll([Time].[Time])},
        0
    )},
    [Sales].([Time].[Time].CurrentMember,[Product].[Product].[ABC],[Sales Measure].[Sales Measure].[Sales])>0
)}

The above MDX will work and return the set of leaf elements in Time which have sales of ABC. However, if we address any other hierarchy of Product then TM1 returns a null set. If querying for a consolidation not a leaf then the appropriate hierarchy can be addressed and the MDX works.

This is clearly a defect as 1) it shouldn't matter which hierarchy in Product we use to address ABC as the result should be the same in all and 2) if you have a leaf element which isn't in the same named hierarchy then you are flat out of luck, there's no way to address the value in a filter.

Provided the same named hierarhcy always contains all leaves it is possible to work around this by first checking the element level and if a leaf then ignore the hierarchy context and substitute the same named hierarchy. But this is a sub-optimal workaround, we shoudl be able to just use the currently selected hierarchy.

I hope that made sense. Anyone else hase come accross this? And anyone from IBM able to comment on when it might be fixed?

------------------------------
Scott Wiltshire
------------------------------

#PlanningAnalyticswithWatson

Hi Scott,

I recreated your example and I'm NOT able to reproduce the behaviour as described.  Can you start by confirming which version of TM1 server you are using?

Here is my cube:



dim1 has a same named hierarchy and a hierarchy named dim1h2.  Both hierarchies have the same members P and ABC. 

I'm testing with this API call: https://pa.localdomain.com:12354/api/v1/ExecuteMDXSetExpression?$expand=Tuples($expand=Members)

Same result with this body in the ODATA API call:

{
"MDX": "{Filter({TM1FilterByLevel({TM1SubsetAll([dim2].[dim2])}, 0)},[cube1].([dim2].[dim2].CurrentMember,[dim1].[dim1].[ABC],[dim3].[dim3].[sales])>0)}"
}

and this:

{
"MDX": "{Filter({TM1FilterByLevel({TM1SubsetAll([dim2].[dim2])}, 0)},[cube1].([dim2].[dim2].CurrentMember,[dim1].[dim1h2].[ABC],[dim3].[dim3].[sales])>0)}"
}

Are you using the ODATA API or one of the older client tools to test this?

------------------------------
Stuart King
IBM Planning Analytics Offering Manager
------------------------------

Original Message:
Sent: Fri September 10, 2021 05:45 AM
From: Scott Wiltshire
Subject: Defect in accessing values for leaf members at alternate hierarchy intersections when querying via MDX

We have discovered what we believe to be a pretty significant defect in the TM1 server's MDX engine. Before I go to the trouble of creating a support ticket and reproducible test case I thought it would be good to first see if other customers and partners are also aware of this current MDX limitation and if @Hubert Heijkers or @STUART KING are aware of the defect and if plans already exist to fix it.

I will try and be as clear and succinct as possible in explaining the issue.

In a dimension containing alternate hierarches when querying a cell value via MDX for an intersection containing a leaf member ​if any hierarchy ​other than the same named hierarchy is addressed for the member then the query will return no data. For consolidated elements the MDX works as expected and alternate hierarchies can be addressed and data is returned. Since leaf elements are "shared resources" what should happen is that regardless of which alternate hierarchy is referenced the same value shoudl be returned (as long as the leaf element in question is a member of that hierarchy).

Let's try and make a very simple example. We have a dimension Product which contains hierarchies Product, Brand, Color, Size and Leaves. Product ABC is a leaf and is present in all hierarchies. The dimension is used in a cube called Sales which for simplicity just contains 2 other dimensions, Time and Sales Measure. The use case is that we want a member set query on the Time dimension to return the periods in which there are sales of ABC. So the MDX looks like this ...

{Filter(
    {TM1FilterByLevel(
        {TM1SubsetAll([Time].[Time])},
        0
    )},
    [Sales].([Time].[Time].CurrentMember,[Product].[Product].[ABC],[Sales Measure].[Sales Measure].[Sales])>0
)}

The above MDX will work and return the set of leaf elements in Time which have sales of ABC. However, if we address any other hierarchy of Product then TM1 returns a null set. If querying for a consolidation not a leaf then the appropriate hierarchy can be addressed and the MDX works.

This is clearly a defect as 1) it shouldn't matter which hierarchy in Product we use to address ABC as the result should be the same in all and 2) if you have a leaf element which isn't in the same named hierarchy then you are flat out of luck, there's no way to address the value in a filter.

Provided the same named hierarhcy always contains all leaves it is possible to work around this by first checking the element level and if a leaf then ignore the hierarchy context and substitute the same named hierarchy. But this is a sub-optimal workaround, we shoudl be able to just use the currently selected hierarchy.

I hope that made sense. Anyone else hase come accross this? And anyone from IBM able to comment on when it might be fixed?

------------------------------
Scott Wiltshire
------------------------------

#PlanningAnalyticswithWatson

Hi Stuart,

Thanks for the quick response. Obviously I dumbed down the example. I might have dumbed it down too much or possibly it is something which is version dependent and has been fixed in the latest version of the server. I will check with the developer who reported the issue and report back.

------------------------------
Scott Wiltshire
------------------------------

Original Message:
Sent: Fri September 10, 2021 09:10 AM
From: STUART KING
Subject: Defect in accessing values for leaf members at alternate hierarchy intersections when querying via MDX

Hi Scott,

I recreated your example and I'm NOT able to reproduce the behaviour as described.  Can you start by confirming which version of TM1 server you are using?

Here is my cube:



dim1 has a same named hierarchy and a hierarchy named dim1h2.  Both hierarchies have the same members P and ABC. 

I'm testing with this API call: https://pa.localdomain.com:12354/api/v1/ExecuteMDXSetExpression?$expand=Tuples($expand=Members)

Same result with this body in the ODATA API call:

{
"MDX": "{Filter({TM1FilterByLevel({TM1SubsetAll([dim2].[dim2])}, 0)},[cube1].([dim2].[dim2].CurrentMember,[dim1].[dim1].[ABC],[dim3].[dim3].[sales])>0)}"
}

and this:

{
"MDX": "{Filter({TM1FilterByLevel({TM1SubsetAll([dim2].[dim2])}, 0)},[cube1].([dim2].[dim2].CurrentMember,[dim1].[dim1h2].[ABC],[dim3].[dim3].[sales])>0)}"
}

Are you using the ODATA API or one of the older client tools to test this?

------------------------------
Stuart King
IBM Planning Analytics Offering Manager

Original Message:
Sent: Fri September 10, 2021 05:45 AM
From: Scott Wiltshire
Subject: Defect in accessing values for leaf members at alternate hierarchy intersections when querying via MDX

We have discovered what we believe to be a pretty significant defect in the TM1 server's MDX engine. Before I go to the trouble of creating a support ticket and reproducible test case I thought it would be good to first see if other customers and partners are also aware of this current MDX limitation and if @Hubert Heijkers or @STUART KING are aware of the defect and if plans already exist to fix it.

I will try and be as clear and succinct as possible in explaining the issue.

In a dimension containing alternate hierarches when querying a cell value via MDX for an intersection containing a leaf member ​if any hierarchy ​other than the same named hierarchy is addressed for the member then the query will return no data. For consolidated elements the MDX works as expected and alternate hierarchies can be addressed and data is returned. Since leaf elements are "shared resources" what should happen is that regardless of which alternate hierarchy is referenced the same value shoudl be returned (as long as the leaf element in question is a member of that hierarchy).

Let's try and make a very simple example. We have a dimension Product which contains hierarchies Product, Brand, Color, Size and Leaves. Product ABC is a leaf and is present in all hierarchies. The dimension is used in a cube called Sales which for simplicity just contains 2 other dimensions, Time and Sales Measure. The use case is that we want a member set query on the Time dimension to return the periods in which there are sales of ABC. So the MDX looks like this ...

{Filter(
    {TM1FilterByLevel(
        {TM1SubsetAll([Time].[Time])},
        0
    )},
    [Sales].([Time].[Time].CurrentMember,[Product].[Product].[ABC],[Sales Measure].[Sales Measure].[Sales])>0
)}

The above MDX will work and return the set of leaf elements in Time which have sales of ABC. However, if we address any other hierarchy of Product then TM1 returns a null set. If querying for a consolidation not a leaf then the appropriate hierarchy can be addressed and the MDX works.

This is clearly a defect as 1) it shouldn't matter which hierarchy in Product we use to address ABC as the result should be the same in all and 2) if you have a leaf element which isn't in the same named hierarchy then you are flat out of luck, there's no way to address the value in a filter.

Provided the same named hierarhcy always contains all leaves it is possible to work around this by first checking the element level and if a leaf then ignore the hierarchy context and substitute the same named hierarchy. But this is a sub-optimal workaround, we shoudl be able to just use the currently selected hierarchy.

I hope that made sense. Anyone else hase come accross this? And anyone from IBM able to comment on when it might be fixed?

------------------------------
Scott Wiltshire
------------------------------

#PlanningAnalyticswithWatson

The server where we saw this behavior was running 11.6.00000.14

Our main environment is running 11.8.00500.12. In 11.8 we also can't reproduce and MDX query of alternate hierarchy leaf intersection behaves as expected.

So I presume this is something which has been fixed possibly without anyone realizing it was an issue to start with. False alarm, we will upgrade all our instances to the latest version.

------------------------------
Scott Wiltshire
------------------------------

Original Message:
Sent: Fri September 10, 2021 09:10 AM
From: STUART KING
Subject: Defect in accessing values for leaf members at alternate hierarchy intersections when querying via MDX

Hi Scott,

I recreated your example and I'm NOT able to reproduce the behaviour as described.  Can you start by confirming which version of TM1 server you are using?

Here is my cube:



dim1 has a same named hierarchy and a hierarchy named dim1h2.  Both hierarchies have the same members P and ABC. 

I'm testing with this API call: https://pa.localdomain.com:12354/api/v1/ExecuteMDXSetExpression?$expand=Tuples($expand=Members)

Same result with this body in the ODATA API call:

{
"MDX": "{Filter({TM1FilterByLevel({TM1SubsetAll([dim2].[dim2])}, 0)},[cube1].([dim2].[dim2].CurrentMember,[dim1].[dim1].[ABC],[dim3].[dim3].[sales])>0)}"
}

and this:

{
"MDX": "{Filter({TM1FilterByLevel({TM1SubsetAll([dim2].[dim2])}, 0)},[cube1].([dim2].[dim2].CurrentMember,[dim1].[dim1h2].[ABC],[dim3].[dim3].[sales])>0)}"
}

Are you using the ODATA API or one of the older client tools to test this?

------------------------------
Stuart King
IBM Planning Analytics Offering Manager

Original Message:
Sent: Fri September 10, 2021 05:45 AM
From: Scott Wiltshire
Subject: Defect in accessing values for leaf members at alternate hierarchy intersections when querying via MDX

We have discovered what we believe to be a pretty significant defect in the TM1 server's MDX engine. Before I go to the trouble of creating a support ticket and reproducible test case I thought it would be good to first see if other customers and partners are also aware of this current MDX limitation and if @Hubert Heijkers or @STUART KING are aware of the defect and if plans already exist to fix it.

I will try and be as clear and succinct as possible in explaining the issue.

In a dimension containing alternate hierarches when querying a cell value via MDX for an intersection containing a leaf member ​if any hierarchy ​other than the same named hierarchy is addressed for the member then the query will return no data. For consolidated elements the MDX works as expected and alternate hierarchies can be addressed and data is returned. Since leaf elements are "shared resources" what should happen is that regardless of which alternate hierarchy is referenced the same value shoudl be returned (as long as the leaf element in question is a member of that hierarchy).

Let's try and make a very simple example. We have a dimension Product which contains hierarchies Product, Brand, Color, Size and Leaves. Product ABC is a leaf and is present in all hierarchies. The dimension is used in a cube called Sales which for simplicity just contains 2 other dimensions, Time and Sales Measure. The use case is that we want a member set query on the Time dimension to return the periods in which there are sales of ABC. So the MDX looks like this ...

{Filter(
    {TM1FilterByLevel(
        {TM1SubsetAll([Time].[Time])},
        0
    )},
    [Sales].([Time].[Time].CurrentMember,[Product].[Product].[ABC],[Sales Measure].[Sales Measure].[Sales])>0
)}

The above MDX will work and return the set of leaf elements in Time which have sales of ABC. However, if we address any other hierarchy of Product then TM1 returns a null set. If querying for a consolidation not a leaf then the appropriate hierarchy can be addressed and the MDX works.

This is clearly a defect as 1) it shouldn't matter which hierarchy in Product we use to address ABC as the result should be the same in all and 2) if you have a leaf element which isn't in the same named hierarchy then you are flat out of luck, there's no way to address the value in a filter.

Provided the same named hierarhcy always contains all leaves it is possible to work around this by first checking the element level and if a leaf then ignore the hierarchy context and substitute the same named hierarchy. But this is a sub-optimal workaround, we shoudl be able to just use the currently selected hierarchy.

I hope that made sense. Anyone else hase come accross this? And anyone from IBM able to comment on when it might be fixed?

------------------------------
Scott Wiltshire
------------------------------

#PlanningAnalyticswithWatson