Forum Discussion
Incremental Refresh on a dataset with Dataflows as a data source
Anonymous Anonymous
Any luck at all? I just found this thread while searching for a solution:
1) I have a 50+ million row dataflow which updates incrementally (under 1 minute to refresh which is pretty neat)
2) The visualization developers need to create datasets based on my dataflow, and make some additional transformations or even join multiple entities
This led to an initial challenge since PBI desktop tried to download all 50 million rows. As a workaround, I created an entity with a small slice of the data. They have parameters for workspace_id, dataflow_id, and entity_name that they can swap over to point to the real historical dataflow once they are done.
3) They seem to need to schedule their dataset to refresh (which is based on my dataflow), and it appears to do a complete refresh and does not respect the incremental nature of the dataflow source at all. Seems like a waste of resources and time.
The ideal state for me would be: 1) I publish an incremental dataflow, 2) people connect to that dataflow and make different datasets, 3) my scheduled dataflow refresh would refresh all downstream datasets incrementally as well
Hi ldacey ,
Unfortunately not. To summarize:
- Incremental Refresh implemented on datasets that connect to Power Bi Dataflows does not work at the moment. The reason is that the queries can not get folded to the data source (https://docs.microsoft.com/en-us/power-query/power-query-folding). Which is weird, obviously.
- If your workspaces are in a Premium capacity, you could try out DirectQuery on Dataflows (https://powerbi.microsoft.com/en-us/blog/power-bi-dataflows-direct-query-support/). I haven't tested this yet, so I can't share any learnings. As far as I know though, there are some limitations to DirectQuery in general (https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery). So you might want to scroll through that page before trying it.
What we did on our side is manage this in our database, where we create SQL VIEWS per use case which we ingest into datasets directly. Since our data source (Snowflake) supports query folding, we can use Incremental Refresh to ingest the data.
Hope this helps!
Hi pat_mecee ,
While composite models is a great solution to a whole lot of problems, I'm afraid it won't 'solve' this one. As far as I know, it won't change the environment that Dataflows is set up in and so it does not support query folding (needed for incremental refresh).
You could of course try to use DirectQuery on Dataflows, but I'm not sure if you will reach the same level of performance as storing it in-memory in a data model.
I am seriously considering cutting out Dataflows entirely and directly connect to the database from the PBI dataset. It seems like less overhead.
Based on some tests I did on enhanced compute dataflows (which do support some query folding, see https://ssbipolar.com/2020/09/16/dataflows-enhanced-compute-engine-will-it-fold/ and within that article there is a link to which queries will fold), it seems like incremental dataset refreshes against an incremental dataflow can work.
I didn't proceed very far, but I set up an incremental refresh dataset which is pointed towards a dataflow (enhanced compute turned on, incremental refresh turned on). I then checked the XMLA endpoint in SSMS and I inspected the partitions. They contained the dates/rows I was expecting and each time I refreshed the test dataset it updated the correct partitions.
But yes, dataflows have been a headache with some weird failures and behavior. Sad because it is nice to allow people to access data and do their own thing without requiring access to the source (either DB or Azure Blob parquet datasets in this case). And I do not need to open up PBI desktop at all which is nice because I can do my job (providing data access) within PBI service. I am hoping for more improvements and expanded documentation..