Incremental refresh - desktop vs. dataflow

[Veles]

I've been trying to get incremental refresh working as I would like it to in a dataflow but I'm running into some problems which is because the way you setup incremental refreshes in Power BI Desktop on a data model is different to how you setup incremental refresh on a dataflow.

On desktop you need to add in the filter line referencing RangeStart and RangeEnd yourself. The advantage here is that you can place where you want this refesh to happen in your query yourself. This is a huge advantage with query folding as it allows you to place a refresh before any query functions you know will break query folding. Your SQL (or other) query will be fired off to the server and the server will only return the data in the date range your incremental refresh policy wants and you then perform any subsequent queries on it that aren't available with query folding.

The way it works with dataflows is you just select the relevant datetime column and it will automatically add in both the RangeStart/End parameters and also the filter line. However it always adds this filter line to the end of the query. I've then gone and built subsequent steps but when I save the query again, it puts the incremental refresh filter back at the end of the query. This is not good as the date range of my incremental refresh will not be included in the initial folded query so the whole dataset will be received from SQL anyway!

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

https://docs.microsoft.com/en-us/power-bi/service-dataflows-incremental-refresh

[ManuGP]

I'm also seeing this issue when trying to set up incremental refresh in a dataflow from SAP HANA. In the "Added Items" step I was able to filter one of the calculation view variables with the generated RangeStart and RangeEnd parameters, converted to the appropriate format:

#"Added items" = Cube.Transform(CV_XXXXXX, {{Cube.ApplyParameter, "Xxxxxxxx", {0, "xxx"}}, {Cube.ApplyParameter, "Document_Date", {6, Number.ToText(ConvertToDocDateKey(RangeStart)), Number.ToText(ConvertToDocDateKey(RangeEnd))}},...

This is the right way to filter the calculation view to run efficiently on HANA (aka not process all the data and filter it at the end).

But as stated above the autogenerated line filter is added at the end. If I delete this step the preview in the dataflow is processed fine. If I click Save & Close everything is validated and I can start refreshing. But it fails because of some date format incompatibility that when I go in to check again is in the filter line added at the end again. 

It's a pity, I was so close to have this simplified.

[FlorentRousseau]

Hi,

I'm facing the same issue with dataflow incremental refresh. When enabling incremental refresh on one of my dataflow tables, it auto-generates RangeStart and RangeEnd "parameters" (which are not seen as parameters in the manage parameters dialog) crashing the editor if I already had explicit RangeStart and RangeEnd parameters in my query, and adds a filter step at the end of the query's steps to filter the selected datetime field >= RangeStart and < RangeEnd.

It lets you modify the steps and reorder things, but when you save and come back, the last auto-generated filter is back (if you had deleted it or moved it).

I tried however to work with it, but to no avail.

I'm sourcing data from a Web.Contents connector with startdate and enddate parameters ; I'm using RangeStart and RangeEnd values to feed the Web.Contents' startdate and enddate.

I'm keeping the last step auto-generated filter ; it won't filter anything as it will be filtered at the Web.Contents step but I don't mind keeping it if the dataflow is happier with it.

The preview is working fine (the auto-generated RangeStart and RangeEnd have default values of 01/01/0001 and 31/12/9999), the Web.Contents is returning rows between these two extreme dates, and I can validate the dataflow.

However, when I try to refresh the dataflow, I get an error.

Looking at the error, it seems to me that the Web.Contents source didn't return any row, as if empty parameters were provided to startdate and enddate (through auto-generated RangeStart and RangeEnd).

Questions :

Are the auto-generated RangeStart and RangeEnd in dataflow incremental refresh only accessible to the auto-generated filter step or can they be referenced in other steps, like mine on Web.Contents source?

Is there a way to check what values they were given so I can understand why the refresh would fail? (I setup the incremental refresh with 1 year data retention and 30 days refresh period, and I have data available on both 1 year and the last 30 days, so the Web.Contents step shouldn't return empty results neither for the initial load nor for the incremental ones, hence my suspicion of empty/unreferencable RangeStart/RangeEnd auto-generated parameters)

Has someone been successful setting up dataflow incremental refresh with a Web.Contents source?

Regards,

Florent  

[v-shex-msft]

HI @Veles ,

According to your description, it sounds like incremental refresh formula are auto generated and not allow you do custom on it. Can you please share some detail information to help us clarify this?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng