Planning Analytics

Any suggestion from experts? @Hubert Heijkers, @STUART KING, @Edward Stuart

------------------------------
Vlad Didenko
Founder at Succeedium
TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
https://succeedium.com/teamone/
Succeedium Planning Analytics Cloud Extension
https://succeedium.com/space/

Original Message:
Sent: Wed September 13, 2023 12:46 AM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

If I read between the lines here, we should avoid using special URL characters in the name of elements? i.g "&", ";", "$", "!"... This really is nothing new, but becomes a much harder requirement with the REST API. 

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

Hi @Ryan Clapp, it would be great if OData allowed for escaping special characters in API URLs

If I read between the lines here, we should avoid using special URL characters in the name of elements? i.g "&", ";", "$", "!"... This really is nothing new, but becomes a much harder requirement with the REST API. 

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

I'll bite;-), it is technically an issue caused by myself where I decided to map attributes to open properties of an open type to make querying and interacting with attributes way easier then treating attribute names as values of a property itself, full well knowing that attribute names do not have to be valid OData identifiers.

But you just did inspire me for MAYBE a TM1 specific extension to the OData URL conventions (I have to test if there is no ambiguity cases first) to potentially get around this. Don't think this will be something OData would ever adopt but, as an OData TC member from the first hour, I'll nevertheless share my thoughts with my peers in the TC;-). 

Hi @Ryan Clapp, it would be great if OData allowed for escaping special characters in API URLs

If I read between the lines here, we should avoid using special URL characters in the name of elements? i.g "&", ";", "$", "!"... This really is nothing new, but becomes a much harder requirement with the REST API. 

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

Thank you very much @Hubert Heijkers , for writing such a detailed answer, it is very helpful! In most cases I already know the cube and only need to create a user interface for the subset editor. And in the case when I need my users to explore the dimensions only, I can make an extra API request to figure out which cube the user can access to use with ExecuteMDX.

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

@Hubert Heijkers, what about lsiting Attributes in ExecuteMDXSetExpression payload?

ExecuteMDXSetExpression?$expand=Tuples($expand=Members($select=Name,Attributes))

PAYLOAD:

{  MDX: "{HIERARCHIZE({[Dim].[Dim].members})}",  Attributes: ['Caption', 'Cool attr name !@#$%^&*=*', 'attr3']}

------------------------------
Vlad Didenko
Founder at Succeedium
TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
https://succeedium.com/teamone/
Succeedium Planning Analytics Cloud Extension
https://succeedium.com/space/

Original Message:
Sent: Mon September 18, 2023 12:06 PM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

Thank you very much @Hubert Heijkers , for writing such a detailed answer, it is very helpful! In most cases I already know the cube and only need to create a user interface for the subset editor. And in the case when I need my users to explore the dimensions only, I can make an extra API request to figure out which cube the user can access to use with ExecuteMDX.

------------------------------
Vlad Didenko
Founder at Succeedium
TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
https://succeedium.com/teamone/
Succeedium Planning Analytics Cloud Extension
https://succeedium.com/space/

Original Message:
Sent: Mon September 18, 2023 03:35 AM
From: Hubert Heijkers
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Wed September 13, 2023 12:46 AM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

------------------------------
Vlad Didenko
------------------------------

Don't particularly like it, as IMHO that's mixing up protocol, query with actual action execution but I can see that could be a workaround (yet still something not trivial as you can imagine request execution happens at a different level/layer then selection/projection, but not impossible).

My preferred solution would be to simply allow adding a DIMENSION PROPERTIES clause to the set expression as in:

{

"MDX": "{HIERARCHIZE({[Dim].[Dim].members})} DIMENSION PROPERTIES MEMBER_CAPTION, [Dim].[Cool attr name !@#$%^&*=*], [Dim].[attr3]"

}

The request would be the same as in your example and expanding Attributes would have the same effect then as expanding Attributes when doing ExecuteMDX request.

Short of that I was, as a TM1 specific hack, contemplating using a backtick characters as they are not valid in OData identifiers nor do they have any use in the OData URL conventions, at least not just yet. The nice thing about that 'hack' would be that I envision the ability to use those anywhere where you'd be referring to a property and as such then would also be able to $filter or $order based on those.

Again, just thoughts, no promises yet, but happy to hear if you have any preferences ;-)

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Fri September 22, 2023 09:20 AM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

@Hubert Heijkers, what about lsiting Attributes in ExecuteMDXSetExpression payload?

ExecuteMDXSetExpression?$expand=Tuples($expand=Members($select=Name,Attributes))

PAYLOAD:

{  MDX: "{HIERARCHIZE({[Dim].[Dim].members})}",  Attributes: ['Caption', 'Cool attr name !@#$%^&*=*', 'attr3']}

------------------------------
Vlad Didenko
Founder at Succeedium
TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
https://succeedium.com/teamone/
Succeedium Planning Analytics Cloud Extension
https://succeedium.com/space/

Original Message:
Sent: Mon September 18, 2023 12:06 PM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

Thank you very much @Hubert Heijkers , for writing such a detailed answer, it is very helpful! In most cases I already know the cube and only need to create a user interface for the subset editor. And in the case when I need my users to explore the dimensions only, I can make an extra API request to figure out which cube the user can access to use with ExecuteMDX.

------------------------------
Vlad Didenko
Founder at Succeedium
TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
https://succeedium.com/teamone/
Succeedium Planning Analytics Cloud Extension
https://succeedium.com/space/

Original Message:
Sent: Mon September 18, 2023 03:35 AM
From: Hubert Heijkers
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Wed September 13, 2023 12:46 AM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

------------------------------
Vlad Didenko
------------------------------

Thanks @Hubert Heijkers,

Using DIMENSION PROPERTIES is a brilliant idea!!! It will give total freedom to request the desired attribute(s).

Aren't $filter and $order only used for the "REST API object properties"? Those should not contain special characters

And you don't need them for ExecuteMDXSetExpression / ExecuteMDX requests, as you can filter and sort using the MDX itself.

I would love to see this enhancement!

Don't particularly like it, as IMHO that's mixing up protocol, query with actual action execution but I can see that could be a workaround (yet still something not trivial as you can imagine request execution happens at a different level/layer then selection/projection, but not impossible).

My preferred solution would be to simply allow adding a DIMENSION PROPERTIES clause to the set expression as in:

{

"MDX": "{HIERARCHIZE({[Dim].[Dim].members})} DIMENSION PROPERTIES MEMBER_CAPTION, [Dim].[Cool attr name !@#$%^&*=*], [Dim].[attr3]"

}

The request would be the same as in your example and expanding Attributes would have the same effect then as expanding Attributes when doing ExecuteMDX request.

Short of that I was, as a TM1 specific hack, contemplating using a backtick characters as they are not valid in OData identifiers nor do they have any use in the OData URL conventions, at least not just yet. The nice thing about that 'hack' would be that I envision the ability to use those anywhere where you'd be referring to a property and as such then would also be able to $filter or $order based on those.

Again, just thoughts, no promises yet, but happy to hear if you have any preferences ;-)

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Fri September 22, 2023 09:20 AM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

@Hubert Heijkers, what about lsiting Attributes in ExecuteMDXSetExpression payload?

ExecuteMDXSetExpression?$expand=Tuples($expand=Members($select=Name,Attributes))

PAYLOAD:

{  MDX: "{HIERARCHIZE({[Dim].[Dim].members})}",  Attributes: ['Caption', 'Cool attr name !@#$%^&*=*', 'attr3']}

------------------------------
Vlad Didenko
Founder at Succeedium
TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
https://succeedium.com/teamone/
Succeedium Planning Analytics Cloud Extension
https://succeedium.com/space/

Original Message:
Sent: Mon September 18, 2023 12:06 PM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

Thank you very much @Hubert Heijkers , for writing such a detailed answer, it is very helpful! In most cases I already know the cube and only need to create a user interface for the subset editor. And in the case when I need my users to explore the dimensions only, I can make an extra API request to figure out which cube the user can access to use with ExecuteMDX.

------------------------------
Vlad Didenko
Founder at Succeedium
TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
https://succeedium.com/teamone/
Succeedium Planning Analytics Cloud Extension
https://succeedium.com/space/

Original Message:
Sent: Mon September 18, 2023 03:35 AM
From: Hubert Heijkers
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

Hi Vlad,

That is indeed correct, our REST API, which is OData compliant, to be able to parse whatever you put on the URL of the request, sticks to the URL convention rules which state that what you can use in $select, $expand and other query options, have to be valid OData Identifiers. We have been a little bit lenient, specifically TM1 has traditionally allowed you to omit spaces so we've done that here too, but special characters, especially if they have meaning according to the URL conventions, would be something that cannot make work.

For MDX queries (not set expressions) one has the option to use the DIMENSION PROPERTIES clause and that's what would help you get around it if you have the ability to form a query using that same set expression. The only restriction is that you have to reference a cube that has the dimension /hierarchy you are executing the set expression against. By using an empty set (read: []) on a second axis and only looking at the axis that has your set expression you'd effectively get the same effect then ExecuteMDXSetExpression but with the implicit expansion of properties you are after without the limitation on the attribute names (note: do not reference attributes in $select in that case as that overrules what you specified using DIMENSION PROPERTIES - should only limit further but that's something that doesn't work correctly at the moment).

Cheers,

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Wed September 13, 2023 12:46 AM
From: Vlad Didenko
Subject: TM1 REST API expand a single attribute using ExecuteMDXSetExpression

------------------------------
Vlad Didenko
------------------------------