Issues with running MDX in Postman

Post Reply
jandoram
Posts: 4
Joined: Fri Jan 08, 2016 9:26 pm
OLAP Product: Planning Analytics
Version: 2.0.6
Excel Version: Excel 2016

Issues with running MDX in Postman

Post by jandoram » Thu May 30, 2019 3:09 pm

Hi,

tl;dr: If Postman doesn't like all MDX statements generated by PAx, what other tools are available? If someone has a good idea on how to fix syntax issues with MDX statements in Postman, I'd like to have a conversation.

I'm currently trying to explore the world of the TM1 REST API and have begun using PAx and Postman to execute some GET, POST, etc. commands.
However, I'm finding there are some limitations of Postman or MDX or even my knowledge of the subject. Basically, in this example, I'm trying to query the }ClientGroups control cube using MDX. I wish to generate a list of Active Directory groups and the clients in Planning Analytics which are assigned to the various Active Directory groups.

In PAx I have created an Exploration, dragged the }clients and }groups dimensions to the rows and columns respectively. Once placed I launch the MDX editor and copy the contents to Postman where I execute the following POST command, along with the MDX statement below:

POST /api/v1/ExecuteMDX?$expand=Axes($expand=Hierarchies/$ref,Tuples($expand=Members/$ref)),Cells

{
"MDX": "SELECT [}Groups].LEVELS(0).MEMBERS DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY,
[}Groups].[}Groups].[}TM1_DefaultDisplayValue] ON 0,
HEAD({{
[}Clients].[}Clients].DEFAULTMEMBER},
[}Clients].[}Clients].DEFAULTMEMBER.CHILDREN,EXCEPT([}Clients].LEVELS(0).MEMBERS , {
[}Clients].[}Clients].[Admin]
})}, 500) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY,
[}Clients].[}Clients].[}TM1_DefaultDisplayValue] ON 1
FROM [}ClientGroups]"

}

This results in the response1.txt, which is great. You can change it to JSON format if that would be helpful in reading it.

Now comes the actual issue. If I leave the POST command the same, but change the MDX statement to something else, like say what I have pasted below, its has an issue in Postman. This is another MDX statement generated from PAx when altering the subsets within the }clients and }Groups dimensions inside my Exploration. I have pasted the error message at the end of the post. Postman seems to have an issue with the syntax of the MDX statement. I have attached that as a .jpg.
Expected commas instead of D.jpg
Expected commas instead of D.jpg (42.2 KiB) Viewed 366 times
It seems it doesn't like some of the MDX statement which PAx generates. Has anyone ran into this issue? Are there other ways to run these MDX statements and POST commands outside of Postman? It seems to be an issue when I try to use a private or public subset and use that in the MDX statement within Postman:

{
"MDX": "SELECT {
[}Groups].[}Groups].DEFAULTMEMBER,
[}Groups].[}Groups].DEFAULTMEMBER.CHILDREN,
[}Groups].LEVELS(0).MEMBERS} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
HEAD({
[}Clients].[}Clients].DEFAULTMEMBER,
[}Clients].[}Clients].DEFAULTMEMBER.CHILDREN,TM1SubsetToSet([}Clients].[}Clients] , "Default" , "public")}, 500) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1
FROM [}ClientGroups]"

}

The error returned is:

{
"error": {
"code": "248",
"message": "Invalid JSON: expected ArraySeparator, received Error"
}
}
Attachments
Expected commas instead of D.jpg
Expected commas instead of D.jpg (42.2 KiB) Viewed 363 times
response1.txt
(17 KiB) Downloaded 3 times

dr.nybble
Posts: 135
Joined: Wed Aug 17, 2011 3:51 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: Excel 2007

Re: Issues with running MDX in Postman

Post by dr.nybble » Thu May 30, 2019 6:31 pm

Looks like you have unescaped quotation marks in your string.
You need to escape quotes like \", see: https://www.json.org/

jandoram
Posts: 4
Joined: Fri Jan 08, 2016 9:26 pm
OLAP Product: Planning Analytics
Version: 2.0.6
Excel Version: Excel 2016

Re: Issues with running MDX in Postman

Post by jandoram » Fri May 31, 2019 1:34 pm

Thanks! Worked like a charm.

Post Reply