Forum Discussion
Incremental Refresh with Appended Web Source
Hey everyone,
I'm stuck with this problem and hoping someone has dealt with it before.
I have 3 large fact tables that I need to set up incremental refresh on. The catch is that each table needs to be appended with data from a web source before loading to the model (the 3 large table from SQL server have the information up to today, and the 3 tables from the web source have the information of today).
Here's my situation basically:
- Main 3 Fact tables from SQL Server (supports query folding)
- 3 tables from a web source that I need to append to each of the main Fact tables (no query folding)
- I need the final combined tables to have incremental refresh
The problem is that Power BI requires the RangeStart/RangeEnd filter in the final query to enable incremental refresh. But since one source is from the web, it still downloads ALL the data every time even though I'm filtering at the end.
I've tried filtering both sources before appending, but the web source doesn't support query folding so it still pulls everything.
When I tried doing it only on the model with the 3 Fact tables from SQL Server, it worked, but I must append them with the Web tables.
Is there any way to make incremental refresh actually work in this scenario? Or do I need to rethink my approach entirely?
Thanks in advance!
Hi aviha12 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you GilbertQ lbendlin for the prompt response.
To make incremental refresh work when appending SQL + non-folding Web data, separate the queries.Apply incremental refresh only on the SQL fact tables (they fold). Load the Web tables separately as small “today-only” datasets without incremental refresh.Then create a final table that simply UNIONs/APPENDs the SQL incremental table with the Web table.The incremental partitions are created from the SQL source, and the Web data is small enough to load fully each refresh.This pattern avoids breaking query folding and is the Microsoft-recommended approach for combining folding + non-folding sources.
That's something you would need to do in code, for example via EXCEPT. Having duplicated data across partitions is very highly undesirable.
9 Replies
Hi aviha12
One alternative approach I would think of is if you have the incremental refresh being applied to your SQL table that will always then incrementally refresh the data and then in the subsequent steps just append your web source. That should work fine because your web source is always getting all the data, but automatically first apply the incremental refresh and then append the data.
But incremental refresh is being done in the DAX level. so I can't do it.
each table needs to be appended with data from a web source before loading to the modelDoes that web source have a filter option?
Regardless, since it doesn't fold you will not get any performance improvements with incremental refresh.Rethink your approach entirely. Push both the web data and the SQL Server data into CSV Zip archives on a Sharepoint. Use the file names to define the partition boundaries. Even though this doesn't fold it is still fast enough as the network transfer penalty is reduced.
I can't do it since the data is oragnizational data.
Hi aviha12 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you GilbertQ lbendlin for the prompt response.
To make incremental refresh work when appending SQL + non-folding Web data, separate the queries.Apply incremental refresh only on the SQL fact tables (they fold). Load the Web tables separately as small “today-only” datasets without incremental refresh.Then create a final table that simply UNIONs/APPENDs the SQL incremental table with the Web table.The incremental partitions are created from the SQL source, and the Web data is small enough to load fully each refresh.This pattern avoids breaking query folding and is the Microsoft-recommended approach for combining folding + non-folding sources.
Thank you,
Will there be a way to delete duplicates on the final table?
Because in the Web table I also get rows that exist in the SQL table, but their status or whatever was updated. so if there is an ID that exist in both table, by remove duplicates it removes for me the one in the SQL table because of the order that the tables are appended, is it possible to do it in your solution?
And also, many of the columns that exist in the SQL table does NOT exist in the WEB table, will it still work?
Thanks!That's something you would need to do in code, for example via EXCEPT. Having duplicated data across partitions is very highly undesirable.
Hi aviha12 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you lbendlin GilbertQ for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
