Not possible to refresh a web source in Report Server

[Lazzer897]

Our customer is using Power BI Report Server. The current report should get Parquet files from S3 storage. We tested the Parquet connector but it failed. Only small files could be read into PBI.

Next step was to, in our own framework, create an API that can be called via https. We created an empty query defining a function, something like this:

let
get_pq = (param1, param2 =>
let
authString = "<auth_string>"
request_query = Web.Contents(<request_url>&<auth_string>)
buffered_query = Binary.Buffer(request_query)
Source = Parquet.Doument(buffered_query)
in
Source
in
get_pq

The function does it job and data can be retrieved without any issues. Everything in PBI Desktop works fine.

So far so good.

When publishing this report to PBI Repot Server, there is no way to perform a refresh the report since PBI does not recognize any data source. That can be seen as well in PBI Desktop. The report thus has a fixed content and to see new content I need to refresh it in PBI Desktop and then republish it!!!!

I have tried to  search for alternatives but without success.

I find it strange that you can create a function that retrieves data but that is not recognized as a data source. 

Is there anyone that can provide one or more options or ideas to test?

(we had hoped to be able to use the Delta Sharing connector but it seems to only be available in the PBI Service)

Regards

Lars

[Lazzer897 · Accepted solution]

Hi
The suggested solution helped. It was necessary to only have the main Url as the first parameter and then I added Relative Path and Headers as part of second parameter. The Web call now showed up as a Data Source and it was possible to publish it to Report Server.

In PBIRS it was not possible to test connection, but I ignored that, since it was possible to define a schedule for the report.

Now, when refreshing the report I got an error where PBI claims that it is not possible to refresh due to dependent data. Holy Moly!!!. 
(a simplified report was possible to refresh in PBIRS)

Noticed that ithe same has happend for others.

So, original case closed and I will have to look into the refresh issue instead.

[d_gosbell]

The most likely cause for your issues is the fact that you have a concatenation expression as the source for the Web.Contents call so the server cannot figure out what data source it needs to authenticate for. Can you try using the optional relative path or query parameters as suggested here Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An... instead of doing the concatenation?

There are a number of operations which you can do in Desktop which get blocked in shared environments (like Report Server) due to potential security issues. In this dynamic expression example if you had the url set as a parameter or read from a table it would be potentially possible to change the destination address after credentials had been stored. This could potentially allow you to hijack these credentials for some other purpose which is why the server does not allow this.

[timabard]

Hi! I have the same problem. I've searched through this article and much more, but I can't find why it doesn't work for me. Maybe you can point out the error?

let

url = "http://host:port/resto/api/",
headers = [#"Content-Type"="application/json"],
body = Text.ToBinary("{<query>}"),
token = Text.FromBinary(Web.Contents(url & "auth?login=" & "login" & "&pass=" & "pass")),//here I get a new token every time I update
Source = Json.Document(
Web.Contents(url & "v2/reports/olap?key="& token & "&reportType=SALES",
[Query =[token = Text.FromBinary(Web.Contents(url & "auth?login=" & "login" & "&pass=" & "pass"))] ,
Headers= headers,
Content=body])),
#"Converted to Table" = Record.ToTable(Source)

in

#"Converted to Table"

[timabard]

Rest was good and I was able to find a solution for myself.

let

Source = Json.Document(
Web.Contents("http://host:port/",
[RelativePath = "resto/api/v2/reports/olap?key="&
(Text.FromBinary(Web.Contents("http://host:port/",
[RelativePath = "resto/api/auth?login=" & "login" & "&pass=" & "pass"]))) & "&reportType=SALES",
Headers= [#"Content-Type"="application/json",

Content=body]])),

#"Converted to Table" = Record.ToTable(Source)

in

#"Converted to Table"

[Lazzer897]

Thanks d_gosbell for your input. I will look at the suggested blog and see if it can provide some ideas.