Forum Discussion
Incremental refresh
Hello my_patil ,
If you company use Microsoft products, you can use Azure SQL Database.
And for python script you can use Azure Functions. It runs on a schedule and it is serverless (no virtual machine to manage).
After writing to Azure SQL Database, you can apply incremental refresh policies.
In Azure SQL DB, create table and add updated_at and write_date columns.
In python script, you can use xmlrpc.client library.
Hope it gives you an idea.
Hy monikapatil2612
In my case, I have a SharePoint folder where users upload files every day, and the datasets have grown to more than 6 million rows. Performance became a huge problem, and refresh times were unacceptable. Because of corporate restrictions and cost limitations, I couldn’t use any other storage solutions like a database.
What I did was create two dataflows:
One dataflow for the historical data (which doesn’t change).
Another dataflow only for the incremental data uploaded daily.
This approach solved my performance issues and made the refresh process much faster.
What I did was create two dataflows:What you did was to create a manual process for partition management. You could have done the same in the semantic model directly, without the dataflows.
What you have now is an incremental refresh on top of an incremental refresh. That can be optimized.
lbendlin , thanks for the input! While I agree that standard Incremental Refresh is usually the way to go, the bottleneck here is the SharePoint Folder connector, which does not support Query Folding.
If I relied on the native Incremental Refresh in the Semantic Model, the mashup engine would still likely scan the metadata of all files to apply the date filters, causing the same I/O bottleneck.
By separating the Dataflows effectively, I force the engine to completely ignore the historical files during the daily refresh, which creates a 'hard partition' that SharePoint can't provide natively.Ideally, I would use a SQL DB with folding, but given the constraints, this manual partitioning was the only way to bypass the scanning overhead.
