Forum Discussion
Datamart and dataflow incremental refresh
Since SharePoint list data source isn't compatible with incremental refresh functionality in Power BI dataflows, and you've already observed performance degradation when refreshing frequently from the datamart directly, here are a few alternative approaches you can consider:
Consolidate and Transform Data Before Loading: Instead of directly connecting to the SharePoint list, consider consolidating and transforming the data before loading it into Power BI. You can use tools like Power Query in Excel or Power BI Desktop to perform data transformations, consolidate multiple files, and then load the transformed data into Power BI. This approach allows you to optimize the data structure and reduce the number of refreshes needed.
Use Automation Tools: Consider using automation tools like Power Automate (formerly Microsoft Flow) to automate the process of consolidating data from SharePoint lists into a single location. You can create a flow that triggers multiple times a day to extract data from SharePoint lists, consolidate it, and load it into a datamart or data warehouse. Once the data is centralized, you can then use incremental refresh in your dataflows.
Optimize Data Model and Refresh Process: If you're experiencing performance degradation during refreshes, optimize your data model and refresh process. This includes optimizing data types, reducing unnecessary columns, minimizing data transformations, and optimizing query performance. You can also consider scheduling refreshes during off-peak hours to reduce the impact on system performance.
Consider Premium Capacity: If you have access to Power BI Premium capacity, you can leverage the enhanced dataflow capabilities available in Premium, such as incremental refresh on more data sources and higher refresh frequency options. With Premium, you can also scale out your dataflows to handle larger volumes of data and frequent refreshes more efficiently.
Evaluate Alternative Data Sources: Depending on your organization's requirements and infrastructure, consider evaluating alternative data sources that are compatible with incremental refresh and offer better performance. This may involve migrating your data to a different platform or using a combination of data sources to achieve your reporting goals.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Thanks for your response.
I pulled out data from sharepoint list and appended all of excel file and in dataflow and datamart.
The problem is that, when I applied the incremental refresh in dataflow it does not work.
but when I applied the incremental refresh it worked however the refresh time became longer than than normal refresh time.
razieh1990 that is expected to have longer refresh time, if you are not convinced, you can implement Azure AS model for faster executions.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!