cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowBI_USER_2020
Helper II
Helper II

Connection to IBM Cognos TM1 via Web using Restapi

Hello, 

 

I am a new bee in PBI. I have tried TM1 Restpi in GetData-> Web-> Advanced option in PBI Desktop. 'Get' Method mdx query executed succesfully and pulled the dimension elements. But now I tried to make POST request in MDX query, in fiddler it says 

"message=The content type specified is not supported. Please use JSON (application/json; charset=utf-8) as the content type."

 

Though I have changed the content-type, same error. 

 

Here is my code: 

 

let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Text.ToBinary(body)
]
))
in
#"Source"

 

 

Thanks in advance

1 ACCEPTED SOLUTION

Thanks for your support@

 

I have removed the paresdJSON.

 

Fianlly the below code works for me and return the value:

 

let
url = "http://host:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Source = Json.Document(Web.Contents(url, [Headers=[#"Authorization"="Basic YWRtaW46", #"Content-Type"="application/json"], Content = Text.ToBinary(body) 
])),
in
Source

 

 

 

View solution in original post

8 REPLIES 8
v-lid-msft
Community Support
Community Support

Hi @PowBI_USER_2020 ,

 

We can try to put the json into the body by using the following queries to meet your requirement:

 

let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Parsed_JSON
]
))
in
#"Source"

 

Have you tried to verify the request by posting this request with other tools?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, 

Thanks for your reply. 

I have tried your solution, but end up with "Expression.Error: We cannot convert a value of type Record to type Text."

 

Any suggestion on this.

 

Hi @PowBI_USER_2020 ,

 

Sorry for our late reply, Could you please try to use the following query ?

 

let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Binary.FromText(body),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Parsed_JSON
]
))
in
#"Source"

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, Thanks for your reply.

 

Again Expression.Error: Invalid binary encoding.
Details:
{
"MDX": "SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])"
}

 

Any suggestions. Thanks

Thanks for your support@

 

I have removed the paresdJSON.

 

Fianlly the below code works for me and return the value:

 

let
url = "http://host:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Source = Json.Document(Web.Contents(url, [Headers=[#"Authorization"="Basic YWRtaW46", #"Content-Type"="application/json"], Content = Text.ToBinary(body) 
])),
in
Source

 

 

 

View solution in original post

Hi @PowBI_USER_2020 ,

 

First of all, we suggest you to remove the confidential information in your query, such as server address and authen key.

 

Glad to hear that you have resolved your problem. It seems chang the Binary.FromText to Text.ToBinary works,  Thank you for sharing this wonderful solution, would you please kindly mark your sharing solution as an answer so that it can benefit more users?

 

If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-lid-msft , 

 

Once again thanks for the support. 

 

As you suggested, when I remove the credentials , its saying the below error: 

credential error- PBI.png

 

Is it a security breach or its not safe enough to expose credentials in the url in Power BI. User info is the encoded one. 

 

 

 

 

Hi @PowBI_USER_2020 ,

 

Apology for our misleading reply, we want to suggest you to delete the credential of the query in your reply of this forum. Please keep your actual query if it has been working successfully.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors