Hi, I have a question about implementing Incremental Refresh on a dataset that connects solely to Dataflows. I tried setting it up but it appears Dataflows do not support query folding. Few questions:- Is it possible in any way to get Incremental Refresh working on a dataset that runs on Dataflows at this moment?- If not, is there anything on the roadmap to enable this?- If not, is there any way to refresh only part of a Dataflow using a workaround? It feels really weird to have all the data in Azure Data Lake (Dataflows) but not being able to load it into a dataset due to memory issues.

Hi Anonymous, Thanks for your answer. It is absolutely clear to me that I can set up incremental refresh in dataflows. This is what I currently do. So the main benefit of this is that we only refresh the last 14 days of data each morning (approx. 2 million rows of data). This is working well and I'm happy with the feature. However, our total dataset consists of approximately 70 million rows of data. And each morning, when I'm refreshing my dataset, it will refresh all of these 70 million rows of data. Even when I implement incremental refresh on the dataset, it will still refresh all rows. What I would expect it to do is refresh only the last 14 days of data, so that it only has to refresh 500 MB of data, instead of the entire ~7 GB. How can I make sure that my dataset will only refresh the part that is refreshed in dataflows (the incremental part)?

Hi Anonymous , Thanks again for your answer. I do want to emphasize that I'm not trying to set up incremental refresh in Dataflows. I already got that working. I'm trying to set up incremental refresh in the dataset that connects to dataflows. So, because dataflows is the source, the question is if dataflows supports query folding. Does it?

Anonymous How is the underlying data source relevant? I'm connecting to Dataflows. The dataset doesn't care where I get the data from before that point, does it? The question is, when I'm refreshing my dataset, do the queries it sends to dataflows get folded and is Incremental Refresh possible in this case? Or will it always load in the entire dataflow?

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

This seems to be working with the "Dataflow" connector (but not with the "Power BI Dataflow" connector).The source Dataflow should be on SQL Cache (Enhanced Compute Engine On) to enable DirectQuery so the Incremental Refresh filter can be folded.

Incremental Refresh on a dataset with Dataflows as a data source

34 Replies

Anonymous
Not applicable
4 years ago
This seems to be working with the "Dataflow" connector (but not with the "Power BI Dataflow" connector).
The source Dataflow should be on SQL Cache (Enhanced Compute Engine On) to enable DirectQuery so the Incremental Refresh filter can be folded.
Anonymous
Not applicable
6 years ago
Hi Anonymous ,
You can refer the following documentation to configure incremental refresh for dataflow:
Using incremental refresh with Power BI dataflows
Incremental Refresh for Dataflows
Best Regards
Rena
- ldacey
  Advocate I
  5 years ago
  @yingyinr Hi - you did not answer the actual question.
  
  Anonymous is asking whether a user can connect a dataset (using PBI desktop) to an existing dataflow and then turn on incremental refresh on the dataset.
  
  We can have incremental refresh datasets for database sources, but can we do the same against a dataflow source? It seems to allow me to do so when I tested it, but I cannot find any official documentation on the subject.
- Anonymous
  Not applicable
  6 years ago
  Anonymous Did you actually read my message? I'm not trying to set up Incremental Refresh in Dataflows, I'm trying to set it up for a dataset that uses Dataflows as a data source.
  
  Could you provide any information on that or perhaps even address my actual questions?
  - Anonymous
    Not applicable
    6 years ago
    Hi Anonymous,
    
    Any update on this?
Anonymous
Not applicable
6 years ago
Hi Databeest,

You are absolutely correct - Power BI Dataflows ingest the data from external sources into the (implicit) Azure Datalake so the question is if Datasets can execute "query folding" towards Dataflows as this is a requirement for the efficient implementation of "Incremental Refresh", i.e. the queries have to check any changes in the underlying data partitions as created by the Incremental Refresh settings.

So the simple question to Microsoft is - can Datasets use Incremental Refreshs on Dataflows ? As the "tandem" Dataflow => Datasets is the proposed Data Ingest and Query Strategie within Power BI I strongly hope (request !) that this is working - if not Power BI NOT ready for Enterprise Business Intelligence !

Thanks for your clarifying on this, best regards
- Anonymous
  Not applicable
  6 years ago
  Hi Anonymous,
  
  Thanks for backing me up :).
  
  I tried to ask this simple question multiple times in this thread but it doesn't seem to land properly. I just want to know if it's on the roadmap at least. Anyways, it became a lower priority for me since the rollout of Direct Query on Dataflows. Haven't gotten the chance yet to test it properly, but at least in theory it offers a solution to the unnecessary refresh time of the datasets that are running on Dataflows.
  - Anonymous
    Not applicable
    6 years ago
    Hi Anonymous ,
    
    Thanks for your answer and hint regarding "DirectQuery on Dataflows".
    
    I am not sure if this is really an "equivalent" solution because like this we cannot use the (speed & performance) advantage of importet Datasets (i.e. in-memory/columnar engine) so I wonder if you have already results and user feedback for interactive queries/filters on "DirectQuery-based" reports ? Are they still performant (latency ?) at all and especially with a larger number of users sending direct queries every time to the underlying Dataflow ?
    
    AnonymousCould you please check again on the "simple question" if the setup "Incremental Data Refresh of Datasets on incrementally refreshed Dataflows" works and is officially supported by Microsoft, thank you.
    
    Best regards
Anonymous
Not applicable
6 years ago
Hello Anonymous,

Any updates from your/Microsoft side ?

Could you please officially confirm, that our requirement
Incremental Refresh of Dataflow
Incremental Refresh of Datasets depending on this Dataflow
is technically possible and supported by Microsoft.

Thanks and best regards
- Anonymous
  Not applicable
  6 years ago
  Hi GilbertQ ,
  Sorry to bother you again. I read the related blogs you wrote about increment refresh, it have good explanation of increment refresh. May I ask for your help answer Anonymous 's question about increment refresh of dataflow? Thanks for your help in advance.
  Any updates from your/Microsoft side ?
  
  Could you please officially confirm, that our requirement
  Incremental Refresh of Dataflow
  Incremental Refresh of Datasets depending on this Dataflow
  is technically possible and supported by Microsoft.
  Best Regards
  Rena
  - GilbertQ
    Super User
    6 years ago
    Hi there
    
    As far as I know it is possible to incrementally refresh the dataflow.
    
    I do not think it is currently supported in Power BI pro to incrementally refresh off a dataset depending on a dataflow.
ldacey
Advocate I
5 years ago
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
- Anonymous
  Not applicable
  5 years ago
  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!
  - pat_mecee
    Frequent Visitor
    5 years ago
    Anonymous ldacey GilbertQ Anonymous Anonymous
    
    Any official change in functionality to allow incremental refresh of a dataset against a dataflow with the advent of composite models?
    Is it easier just to keep historical and refreshing dataflows separate and then dax union within the data model?
iBusinessBI
Kudo Collector
4 years ago
Any updates on this?
June 2022...
- Phillipe_soares
  Frequent Visitor
  4 years ago
  Did you find a solution? I'm having the same problem and I can't find anything about it.

Forum Discussion

Incremental Refresh on a dataset with Dataflows as a data source

34 Replies

Related content

Incremental Refresh on Dataflow sourced from SharePoint

DataFlow Incremental Refresh

Incremental Refresh Dataflow Gen2

Increment Refresh

Dataflow gen2 - Incremental refresh