cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gautier
Frequent Visitor

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://<base url>:6405/biprws/raylight/v1/documents/<doc id>/reports/<report id>
with the following headers:
X-SAP-LogonToken=<logontoken>
Accept="text/csv"

 

In Power query the source line looks like this:
Source=Csv.Document(Web.Contents("<base url>:6405/biprws/raylight/v1/documents/<doc id>/reports/<report id>",[Headers=[#"X-SAP-LogonToken"="""<logontoken>""",Accept="text/csv"]]))

 

My only problem is how to get the logontoken from the post request with powerquery. My knowledge of powerquery is limited so maybe someone can help me to send the required POST call to SAP from Powerquery and then store the logontoken in a variable that will be used in the GET report call.

1 ACCEPTED SOLUTION
Gautier
Frequent Visitor

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.

 

 

 

View solution in original post

11 REPLIES 11
Gautier
Frequent Visitor

Replying to myself:

 

I used this thread to send a POST call to SAP BO.

 

My GetToken() function in powerquery looks like this:

 

let
Source = () => let
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>"),
actualUrl = "baseurl",
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]
in
logonToken
in
Source

 

 

Gautier
Frequent Visitor

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.

 

 

 

View solution in original post

sbo94120
Frequent Visitor

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

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 !

 

 

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

 

------------------------------------------------------------------------------------------------------

Hi,

 

which webcontent is failing ? The one to get the token ?

 

Can you first make sure that your authentication process works ?

 

 

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

requestWebContentCSV.PNG

 

You can help me?

 

Thanks

 

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

 

------------------------------------------------------------------------------------------------------

sbo94120
Frequent Visitor

yep just share the global query it's a interesting feature to explore

regards

 

Hi can you give me some more details.

 

When I pass this for logging into the system 

 

Logon: POST

http://SERVERapp01.ORGnet.org:6405/biprws/logon/long

 

Now it is returning just the attributes and I am unable to pass the username/password to retreive the report content. 

 

I even tried by using my report URL 

 

http://SERVERapp01.ORGnet.org:6405/biprws/raylight/v1/documents/7555555/reports/5   

 

Did you sucessfully get back the report content if so please guide me here.

What am I missing.

Gautier
Frequent Visitor

Hi,

 

you need to post the logon string as a POST call (not GET).

 

To do that you need to use the Web.Content() with the content option (adding a content option to webcontent transforms the WebContent call into a POST call).

 

Also you need to have the logon string as a binary using the Text.ToBinary() function.

 

Username and password need to be hard coded in the xml logon string. Just replace the red text of the below xml code with your username, password and authentication type.

 

"<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>"

 

Look at my first post to see the complete query I used to get the logon token back from logon API.

 

To export the report content I used the following function:

 

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"&"?]"])

 

 

with

 

baseUrl = http://server:6405/biprws

token = the result of the logon query (see my first post)

LatestInstanceID= ID of the document

pageID=ID of the report

 

And before that query is made I have another query browsing the document repository to find the latest instance of the report (scheduled to run every our on BO).

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.