Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

 

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

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,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

       

       

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        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,