incremental refresh with Web.Contents not working

[jlammens]

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

[lbendlin]

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)

[jlammens]

Excellent, thanks for your help!

[lbendlin · Accepted solution]

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.

[akashgera]

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

[lbendlin]

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.

[jlammens]

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

[jlammens]

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

[v-deddai1-msft]

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

[jlammens]

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. 

[v-deddai1-msft]

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

[jlammens]

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.

[jlammens]

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

[lbendlin]

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).