Forum Discussion
Incremental Refresh and CSV Files
Here's an article to get you going.
If you place your CSV files on a sharepoint then you don't need a gateway.
A gateway will work if needed, but it's a lot of extra effort - that gateway needs to stay up 24x7, needs to be updated every month, is very finicky if you have proxies etc etc. If you can avoid that then your solution will be much easier.
Check the code sample in the blog post - it enumerates all CSV files in a particular location and then decides which ones to load based on some sort of datestamp - could be last modified date, or a date that is part of the file name.
(Note: Incremental Refresh requires DateTime values so you need to do a little bit of casting)
That is entirely up to you. It depends on the size of the CSV files - try not to go over 500-ish MB per file. It also depends on the "reach" of changes you expect for older data. In our case we can see transactions change not only for the current quarter but also for prior quarters, so our buffer for the incremental refresh is bigger. If your data is immutable (doesn't change after submission) then you can use a much tighter interval.
NOTE: Incremental Refresh is not the same as selective refresh. If you want selective refresh you can look into refreshing individual partitions in your dataset.
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla
"Essentially, we just want to incrementally refresh any changes to the old data, while loading in the new data. "
That is exactly what incremental refresh cannot do for you. This would be a selective or differential refresh. As I mentioned you can fudge it by enforcing a wide enough flush-and-fill window. For example instead of
"keep last five years and refresh last 7 days"
you would specify
"keep last five years and refresh last three months"
with the assumption that your data changes all happen for that window. If you have a risk of older data changing then you need to make the window wider, or do an occasional full refresh or refresh impacted partitions.
There is that "detect data changes" option but I haven't found it to be useful for this scenario yet as it requires a different column - which we mostly don't have.
If you have that field and are confident that it does what it says then yes, you can specify it in the extra setting. That is supposed to instruct the Power BI service to refresh all partitions that fall into that range. You will still want to validate that it does this correctly, and you don't end up with duplicate, missing or un-updated data.
This article is worth reading: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#override-incremental-refresh-behavior
I assume you know how to check partition refreshes with SSMS or DAX Studio?
Sorry, I don't have experience beyond Premium Capacity, and no sandbox available to test other setups. Maybe someone else can chime in.
Premium is a capacity, not a license. The smallest capacity is a P1.
What you can look into is PPU - Premium per user. That might work in your senario.
Amazing, that you! So one more questions... Is there a way I can connect my shared folder (Multiple CSV files included) in sharepoint or would I have to add each file? I have a data export updating these CSV files on shared drive. If these files get updated on shared drive would they do the same on sharepoint? Any good links?
Check the code sample in the blog post - it enumerates all CSV files in a particular location and then decides which ones to load based on some sort of datestamp - could be last modified date, or a date that is part of the file name.
(Note: Incremental Refresh requires DateTime values so you need to do a little bit of casting)
Okay Thank you. Do I need to extract the CSV files in months or quarters in order to do Incremental refresh or will it work just using one CSV file?
That is entirely up to you. It depends on the size of the CSV files - try not to go over 500-ish MB per file. It also depends on the "reach" of changes you expect for older data. In our case we can see transactions change not only for the current quarter but also for prior quarters, so our buffer for the incremental refresh is bigger. If your data is immutable (doesn't change after submission) then you can use a much tighter interval.
NOTE: Incremental Refresh is not the same as selective refresh. If you want selective refresh you can look into refreshing individual partitions in your dataset.
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla
Okay good to know thanks. Essentially, we just want to incrementally refresh any changes to the old data, while loading in the new data. The purpose of this would be to avoid a full refresh when we can just only refresh only the changed data in the old and detect any new data. Would this be a selective rehresh then? In the past using a different software I would use Record ID to preform this refresh, rather than date/datetime field so its all new to me.