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

incremental refresh with Web.Contents not working

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

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

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.

View solution in original post

11 REPLIES 11
lbendlin
Super User III
Super User III

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)

Excellent, thanks for your help!

lbendlin
Super User III
Super User III

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.

View solution in original post

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

 

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

v-deddai1-msft
Community Support
Community Support

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

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. 

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

 

 

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
Frequent Visitor

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

lbendlin
Super User III
Super User III

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

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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!