cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JSiebrecht
Resolver I
Resolver I

Incremental Refresh: Should RANGESTART also fold?

Im am trying to set up incremental refresh for an OData source.

My "normal" filters fold back to the source fine.

But the custom filters using the RANGESTART and RANGEEND parameters do not seem to fold.

When I look at the query provided by the Query Editor's Step Diagnostics tool, via Fiddler Anywhere or in the log file of the OData service on the server. My "normal" filters show in the query, but the Range filters do not.
Even once published to the service (it is a premium capacity workspace) the Range filters do not show in the query string.
"
2021-03-10 15:11:25,847 - INFO - >> https://xxxxint.azure.xxxx.xxxx/xxxxODataTssService/odata/Contract?$filter=DATE_FROM le 2021-03-20T00:00:00Z and DATE_TO ge 2021-02-28T00:00:00Z and STATUS ne 'DELETED'
"

Shouldn't a filter on the field specified in the incremental refresh policy (in my case CREATE_DATE) be included in the query by the incremental refresh service when performing the refresh increment? Should I not see this in the query string received by the OData service?
And if I have "Detect Data Changes" activated and the fields MODIFIED_DATE specified, should this not also show in the query string when the service is performing the refresh increment?

Thanks for your help!
Maybe @JWedge or @d_gosbell ? Seem you two have quite some experience with incremental refresh and query folding...

1 ACCEPTED SOLUTION

Hi @d_gosbell 
thanks for your input.
I also expected the parameters to filter into the query. And I DID define them with the case-sensitive spelling as you showed and as per the Documentation. The all caps was just to make them spring out - but in hind-sight might have caused more confusion.

Double-checking the documentation once more I DID find an interesting hint, which actually let me find the solution to the riddle.
The date field I wanted to use for the range parameters is on the DB side a DateTimeZone rather than the DateTime mandatory for the range parameters. Since I could not have the range parameters as DateTimeZone, I upon import converted the CREATE_DATE field from DateTimeZone to DateTime. Now the filtering was possible. But only on the Power BI side, not the server side.
So I now use the DateTime range parameters and convert them within the filter step into the CREATE_DATE's DateTimeZone format (rather than converting the field).

JSiebrecht_1-1615542584444.png


Now they fold correctly to the source.

JSiebrecht_0-1615542504080.png


THANKS FOR YOUR HELP @d_gosbell !
Many times - even if not presented to you on a silver plate - such interactions help you find the solution yourself.
Have a nice weekend!

View solution in original post

3 REPLIES 3
JSiebrecht
Resolver I
Resolver I

One challange remains though:
If you want to have the Service detect data changes, and the field holding the MODIFIED_DATE is of type DateTimeZone on the DB.
How can you prevent all the data of the increment steps being loaded and only evaluated on the Power BI side?
Ideally, this filter should also be folded into the query string.
But if the data types do not match (DateTime vs. DateTimeZone) I doubt this willhappen in this case as seen above...

JSiebrecht_0-1615544112847.png

 

d_gosbell
Super User II
Super User II

I have not tried incremental refresh against an OData source, but if other filters are folding I would expect the incremental refresh filters to fold also and be reflected in the $filter= query string. 

 

However one possible issue here is that the RangeStart and RangeEnd parameters are case-sensitive (as noted in the docs) So if you are using RANGESTART and RANGEEND in all upper case I would not expect it to work.

Hi @d_gosbell 
thanks for your input.
I also expected the parameters to filter into the query. And I DID define them with the case-sensitive spelling as you showed and as per the Documentation. The all caps was just to make them spring out - but in hind-sight might have caused more confusion.

Double-checking the documentation once more I DID find an interesting hint, which actually let me find the solution to the riddle.
The date field I wanted to use for the range parameters is on the DB side a DateTimeZone rather than the DateTime mandatory for the range parameters. Since I could not have the range parameters as DateTimeZone, I upon import converted the CREATE_DATE field from DateTimeZone to DateTime. Now the filtering was possible. But only on the Power BI side, not the server side.
So I now use the DateTime range parameters and convert them within the filter step into the CREATE_DATE's DateTimeZone format (rather than converting the field).

JSiebrecht_1-1615542584444.png


Now they fold correctly to the source.

JSiebrecht_0-1615542504080.png


THANKS FOR YOUR HELP @d_gosbell !
Many times - even if not presented to you on a silver plate - such interactions help you find the solution yourself.
Have a nice weekend!

View solution in original post

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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors