topic Re: incremental refresh with Web.Contents not working in Report Server

incremental refresh with Web.Contents not working

jlammens — Fri, 19 Feb 2021 17:13:33 GMT

I have prepared a PBI M query using Web.Contents with RangeStart and RangeEnd parameters as described here which works fine in PBI desktop, but produces apparently unrelated errors in PBI Service (with or without gateway). Has anybody gotten something similar to work? This is the Source statement:

Source = Json.Document(Web.Contents(
"https://somehost.net/analytics/api/v1/order", [
Query=[
updated_after=DateTimeZone.ToText(DateTimeZone.From(RangeStart), "yyyy-MM-ddThh:mm:sszzz"),
updated_before=DateTimeZone.ToText(DateTimeZone.From(RangeEnd), "yyyy-MM-ddThh:mm:sszzz")
]]))

Thanks,

Johan

Re: incremental refresh with Web.Contents not working

lbendlin — Sat, 20 Feb 2021 03:23:30 GMT

You have to make sure that one of the parameters is inclusive and the other is not. so either updated_on_or_after or updated_on_or_before (but not both, and not neither).

Re: incremental refresh with Web.Contents not working

jlammens — Sun, 21 Feb 2021 17:15:29 GMT

That is the case, in fact. The effective condition is updated_after <= date < updated_before.

Re: incremental refresh with Web.Contents not working

v-deddai1-msft — Tue, 23 Feb 2021 02:33:39 GMT

Hi jlammens ,

Query folding is the root of incremental refresh. It's important the partition filters are pushed to the source system when queries are submitted for refresh operations. To push filtering down means the datasource should support query folding. Most data sources that support SQL queries support query folding. However, data sources like flat files, blobs, and web feeds typically do not. In cases where the filter is not supported by the datasource back-end, it cannot be pushed down. In such cases, the mashup engine compensates and applies the filter locally, which may require retrieving the full dataset from the data source. This can cause incremental refresh to be very slow, and the process can run out of resources either in the Power BI service or in the on-premises data gateway if used.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Re: incremental refresh with Web.Contents not working

jlammens — Tue, 23 Feb 2021 08:55:53 GMT

Thank you for your help. This particular web API does in fact support query folding, as evident from the RangeStart and RangeEnd parameters being passed as query parameters. This works fine and produces the expected results in PBI desktop or service without incremental refresh turned on, but it fails in the service once incremental refresh is turned on.

Re: incremental refresh with Web.Contents not working

v-deddai1-msft — Tue, 23 Feb 2021 09:28:19 GMT

Hi jlammens,

First, you create the post in report server forum. I assume that you want to enable incremental refresh in Report Server, It is not supported in Report Server, it's currently a feature of the cloud service only.

>>as evident from the RangeStart and RangeEnd parameters being passed as query parameters. This works fine and produces the expected results in PBI desktop or service without incremental refresh turned on,

Incremental refresh is currently a feature of the cloud service only. It works well in desktop and service without incremental refresh turned on don't mean it support query folding. As mentioned by https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#query-folding, if there is a warning during your configuration, it didn't support query folding.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Re: incremental refresh with Web.Contents not working

jlammens — Tue, 23 Feb 2021 13:51:13 GMT

I must have chosen the wrong forum, sorry about that. Which would be the right one?

This particular web API does support the date filter at the datasource back-end, i.e., it only returns items that satisfy (RangeStart <= date < RangeEnd). Is that not considered predicate push-down and/or query folding? As I read the documentation, the warning about query folding does not mean that the data source does not support it, but that such support cannot be determined automatically as is the case for e.g. SQL-based sources.

Re: incremental refresh with Web.Contents not working

lbendlin — Tue, 23 Feb 2021 13:55:31 GMT

Exactly, it's just a warning, not an error message. If you know that your data source honors the RangeStart/RangeEnd parameters (in the appropriate format conversion ) then incremental refresh will work. The only sure way to validate incremental refresh is to look at the partition statistics in the XMLA endpoint. If you see only one partition then incremental refresh likely doesn't work. If you see multiple partitions then it works. I have a blog entry on that subject if you are interested.

Re: incremental refresh with Web.Contents not working

jlammens — Tue, 23 Feb 2021 16:32:42 GMT

Yes, please point me to your blog entry. How do I get access to the XMLA endpoint?

Re: incremental refresh with Web.Contents not working

lbendlin — Tue, 23 Feb 2021 18:28:22 GMT

Incremental Refresh with Slow-Changing Source Data - Microsoft Power BI Community

For XMLA endpoint access go to dataset settings...server settings and use that string to connect from your fvorite query tool (like SSMS or DAX studio)

Re: incremental refresh with Web.Contents not working

jlammens — Wed, 24 Feb 2021 09:33:27 GMT

Excellent, thanks for your help!

Re: incremental refresh with Web.Contents not working

jlammens — Thu, 04 Mar 2021 11:05:53 GMT

Finally got this to work, thanks! A few things to note, though:

Incremental refresh seems to query data one day at a time, perhaps only for the initial data set retrieval; in my case this sometimes resulted in no data being returned, which the M script choked on
JSON objects seem to be translated to M records and JSON arrays to M lists by the Json.Document function, but that is not documented (you can infer it from the inverse mapping documented for Json.Value, though)
After publishing a new version of this project to the PBI service I kept getting a “gateway needed” error the first time it was refreshed in the service, but not after that (and everything worked as expected without gateway), nor with automatic refresh after that

Re: incremental refresh with Web.Contents not working

akashgera — Sat, 14 Aug 2021 10:09:40 GMT

Hello sir,

I am using incremental refresh for Web API data source, after I define IR policy I get the following error:-

Unable to confirm if the M query can be folded. It is not recommended to use incremental refresh with non-foldable queries

Does that mean my data source doesn't support Inc. refresh ?

OR Is there any alternative to avoid this error?

Pls help !!

Re: incremental refresh with Web.Contents not working

lbendlin — Sat, 14 Aug 2021 15:42:37 GMT

That's not an error. It is merely a warning. You can proceed. The ultimate proof is the presence of multiple partitions for that table in the service dataset.