Forum Discussion
use parameters in sql query to import data
Anonymous And here you go - Feb update with Incremental Refresh for PRO users 🙂
Hi Anonymous,
I've been primarily working with premium workspaces in and "App Owns Data" embedded environment, so I've used the out of the box methods, tied in with the 'jiggery-pokery' I posted earlier. I've not even considered how a similar solution might be obtained in a non-premium environment.
Your solution sounded interesting on the face of it, until you consider a scenario where data from your "new" table reaches the age threshold whereby it is no longer considered new and therefore, presumably, moved into the "old" table. In this case, you not only have to refresh your "new" table, but also your full "old" table which is likely to be much larger.
Depending on your use case, running a full refresh isn't necessarily a bad thing. You simply have to consider the tradeoff between cost, speed, and data availability (Ask the question of your users: Do you really need to see an entire history , or do you mostly look at the last X days/weeks/months?).
Anonymous
All your considerations are correct and things I faced. In my test case (last year) I was downloading 2010->2018 data in a big one-off file and 2019 in the update table. Then the idea was to change the model in 2020.
The idea was to speed up import because loading 4M rows where 3.9M haven't changed is much inefficient than loading just the 100k that have been updated. Unfortunately it does not work. Honestly I do not remember why but I did a few tests and PowerBI was refusing to work. I don't remember if it was updating the data EVEN IF was selected not to refresh that table or what, but I spend a lot of time trying to make it work with no success (and I thought about it: it was a nice way to go around the MS limitation of incremental refresh so maybe they thought about it 🙂 )
If I remember correctly it was like with Excel files: when you load an Excel file on a PBI and then publish to web, NO MATTER HOW YOU DISABLE REFRESH it will still forever try to load that local file on the web, failing. The only way to load "one off" an excel file is to push the data as a loaded table in PBI.