Forum Discussion

Frequent Visitor

9 years ago

Solved

Connect to Business Object Webi report through RESTful request

Hi, I have found a workaround to get access to my Webi reports using the SAP Restful API. It works very well with Power BI. Basically the GET request I am sending looks like this: http://<ba...

Gautier
9 years ago
Continuing my previous post:

2 issues arise when I upload to Power BI service and try to refresh through the data gateway:

1) functions are not accepted - fixed easily by adding the logon function within the main query as a subquery

2) the main query credential (anonymous) isn't accepted by Power BI services - fixed by using relativepath in my webcontent()

Also I have added to my query a first subquery to retrieve the report ID of the latest instance of the report stored in Business Object.

If anyone is interested to see the details I am happy to share my query.

sbo94120

Frequent Visitor

9 years ago

yep could you please post the detailed query you made to fix these 2 issues, including a explanation of the goal and structure of your query sent to a BO universe datasource.

regards

Gautier

Frequent Visitor

9 years ago

Hi,

My query is structured as follow (with critical variables in red) :

1) authentication phase:

body = Text.ToBinary("<attrs>
<attr name=""userName"" type=""string""><username></attr>
<attr name=""password"" type=""string""><password></attr>
<attr name=""auth"" type=""string"" possibilities=""secEnterprise,secLDAP,secWinAD,secSAPR3"">secWinAD</attr>
</attrs>"),
baseUrl= http://server:6405/biprws,
options = [
Headers =[#"Content-type"="application/xml",#"Accept"="application/json"],
Content=body
],
result = Web.Contents(baseUrl, options),
#"Imported JSON" = Json.Document(result,1252),
token = #"Imported JSON"[logonToken]

Now that we have retrieved a token we can get access SAP BO and retrieve the content of a Webi report

2) export report content as csv:

LatestInstanceID= ID of the document

pageID=ID of the report

Export latest instance as CSV = Web.Contents(baseUrl,[ Headers =[#"Content-type"="application/xml",#"X-SAP-LogonToken"=token,#"Accept"="text/csv"], RelativePath="raylight/v1/documents/"&#"Latest Instance ID"&"/reports/"&#"pageID"&"?]"])

The 2 issues I mentioned are solved as follow:

- unable to refresh on Power BI service when the logon token is retrieved through a function => simply integrate the authentication part of the query at the beginning of the query used to get the content of the report (no use of a separate function)

- Power BI services not accepting anonymous connection to web data source => used RevalivePath trick. Basically when Power BI services stores credentials for data sources it makes a test connection to the url provided in the query with the credentials given by the user. When Power BI service tries to access http://server:6405/biprws/raylight/v1/documents/etc. as anonymous the server will return an error => Power BI service will interpret this as incorrect credentials.

But if you use the RelativePath trick Power BI service will only test the baseurl http://server:6405/biprws and fortunately the server will return a different response to that url which will be accepted by Power BI service.

Hope this answers your questions !

Shamrao041
New Member
9 years ago
Hi, I was trying this but looks I'm stuck. here is my below query.
Error Message - web.contents failed to get contents 405 method not allowed.
----------------------------------------------------------------------------------------------------------------------------------
let
Source = () => let
body = Text.ToBinary("<attrs>
<attr name=""userName"" type=""string"">Usera</attr>
<attr name=""password"" type=""string"">Passworda</attr>
<attr name=""auth"" type=""string"" possibilities=""secEnterprise,secLDAP,secWinAD,secSAPR3"">secLDAP</attr>
</attrs>"),
actualUrl = "http://base:6405/biprws",
options = [
Headers =[#"Content-type"="application/xml",#"Accept"="application/json"],
Content=body
],
result = Web.Contents(actualUrl, options),
#"Imported JSON" = Json.Document(result,1252),
logonToken = #"Imported JSON"[logonToken],
DOCID= "ABC",
pageID="123456",
CSV =(Web.Contents(actualUrl,[ Headers =[#"Content-type"="application/xml",#"X-SAP-LogonToken"=logonToken,#"Accept"="text/csv"],
RelativePath="raylight/v1/documents/"&#"DOCID"&"/reports/"&#"pageID"&"?]"]))
in
logonToken
in
Source

------------------------------------------------------------------------------------------------------
Shamrao041
New Member
9 years ago
Hi, I was trying this but looks I'm stuck. here is my below query.
Error Message - web.contents failed to get contents 405 method not allowed.
----------------------------------------------------------------------------------------------------------------------------------
let
Source = () => let
body = Text.ToBinary("<attrs>
<attr name=""userName"" type=""string"">Usera</attr>
<attr name=""password"" type=""string"">Passworda</attr>
<attr name=""auth"" type=""string"" possibilities=""secEnterprise,secLDAP,secWinAD,secSAPR3"">secLDAP</attr>
</attrs>"),
actualUrl = "http://base:6405/biprws",
options = [
Headers =[#"Content-type"="application/xml",#"Accept"="application/json"],
Content=body
],
result = Web.Contents(actualUrl, options),
#"Imported JSON" = Json.Document(result,1252),
logonToken = #"Imported JSON"[logonToken],
DOCID= "ABC",
pageID="123456",
CSV =(Web.Contents(actualUrl,[ Headers =[#"Content-type"="application/xml",#"X-SAP-LogonToken"=logonToken,#"Accept"="text/csv"],
RelativePath="raylight/v1/documents/"&#"DOCID"&"/reports/"&#"pageID"&"?]"]))
in
logonToken
in
Source

------------------------------------------------------------------------------------------------------
- Gautier
  Frequent Visitor
  9 years ago
  Hi,
  
  which webcontent is failing ? The one to get the token ?
  
  Can you first make sure that your authentication process works ?
  - anamathius
    New Member
    9 years ago
    Hi,
    
    Thanks to the post, it is useful but I have problems with the authentication anonymous.
    
    I use the next code
    let
    body = Text.ToBinary("<attrs>
    <attr name=""userName"" type=""string"">usuario</attr>
    <attr name=""password"" type=""string"">pass</attr>
    <attr name=""auth"" type=""string"" possibilities=""secEnterprise,secLDAP,secWinAD,secSAPR3"">secEnterprise</attr>
    </attrs>"),
    actualUrl = "http://baseURL:6405/biprws/",
    options = [
    Headers =[#"Content-type"="application/xml",#"Accept"="application/json"],
    RelativePath="logon/long",
    Content=body
    ],
    result = Web.Contents(actualUrl, options),
    #"Imported JSON" = Json.Document(result,1252),
    logonToken = #"Imported JSON"[logonToken],
    #"Latest Instance ID"="123456",
    #"pageID"="2",
    optionsCSV = [
    Headers =[#"Content-type"="application/xml",#"Accept"="text/csv",#"X-SAP-LogonToken"=logonToken],
    RelativePath="raylight/v1/documents/"&#"Latest Instance ID"&"/reports/"&#"pageID"],
    CSV = Web.Contents(actualUrl,optionsCSV)
    in
    CSV
    
    And in CSV fails, said that "We couldn't authenticate with the credentials provided. Please try again."
    The authentication process works fine, because I can see the token in Power BI. I don't understand why the web.contents works with the part of logon but in the part of CSV fails.
    
    I send a photo where the request to Power BI
    
    You can help me?
    
    Thanks

Forum Discussion

Connect to Business Object Webi report through RESTful request

Related content

CANNOT connect to Dynamics 365 Business Central

How to connect Business Central Financial Reports to Power BI

Power BI / Business Central App Connectivity issue

Realtime - Business Events - cannot create business event shcema

Business Requirement Document Template