Datamart and dataflow incremental refresh

[razieh1990]

Hello

I am a SharePoint list as my data source and have established a dataflow to extract sales data from the past three years. Each day's data is stored in a separate Excel file, resulting in approximately 900 files currently, with the count increasing daily.

I tried to implement incremental refresh on the dataflow, it did not work. I think this because SharePoint list as a data source isn't compatible with incremental refresh functionality.

So, I  created a datamart and set up a dataflow with the datamart as its source, successfully applying incremental refresh on data flow.

my question:

I need the report to be refreshed three times daily. When scheduling these frequent refreshes directly on the datamart (which lacks incremental refresh capability), I observed a noticeable degradation in refresh performance in terms of elapsed time.

I would greatly appreciate any suggestions or insights on how to efficiently extract data from a SharePoint list into a dataflow and enable incremental refresh. 

Thanks you 

[johnbasha33]

@razieh1990  

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:

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

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

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

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

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

[razieh1990]

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.

[johnbasha33]

@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 !!