Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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.
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
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.
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
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
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
------------------------------------------------------------------------------------------------------
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.
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).
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |