Use a cache of a dataset with old/static data and UNION fresh data (Incremental Update)

[WorkHard]

Let's say I have a huge dataset I'm pulling via a URL. Currently, the server I am pulling this from can't handle on-demand data pull of this size for extended periods of time so instead I'd like to "cache" the rows that no longer change (historical data that stays unchanged) and just keep refreshing daily the recent data.

My plan is to:

- create a table and dump all the historical data in it, (say all the data from 2000-2019)

- create another table and pull the recent data from a URL. (data from 2020 and future data) (i'll set a schedule to update this daily)

- UNION or join these two tables to recreate the entire dataset.

I'm not sure if there's a better way to achieve this. Any suggestions are greatly appreciated.

Update:

Upon further research, it looks like what I'm trying to achieve is called "Incremental refresh". It appears that Microsoft released this feature for Power BI Premium only. 

I've found a solution to perform an "incremental" update using UNION between old data and new data but I'm not sure if the new data will refresh once it's in the union table. I guess I'll test and find out.

https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

Update2:

As I feared, the data will not refresh in the service because Microsoft is not providing a way to specify the exact same option available in Power BI desktop. The option is called: "Include in Report Refresh" which gets ignored by the service and errors out your entire dataset refresh.

Please vote this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20579641-exclude-a-table-from-ref...

[ImkeF · Accepted solution]

Yes, that's a method I've blogged about here: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

Without a premium license, the only other way I'm aware about (without writing out static data) is using dataflows: http://excel-inside.pro/blog/2019/03/21/classical-incremental-refresh-for-cloud-data-sources-in-powe... 

[WorkHard]

wow, I just posted an update in my post about your method, 2 seconds before you posted. Thanks for that blog post!

Can you please confirm that once I create a UNION between the old data table and the new data table, the new data table will keep refreshing daily in the union table?

Meaning, my new data comes from a csv URL that I set to refresh once a day. Do I need to set the UNION table to refresh daily as well?

[ImkeF]

You can only edit refreshes for queries in the query editor. So the DAX-queries will automatically refresh with every refresh you take (but please have in mind, that this won't work in the service, so you cannot schedule automatic refreshes - as pointed out in the blogpost.)

[WorkHard]

ah. that sucks. This means I can't automate this process as these reports would need to refresh their data automatically without a person physically updating them daily. 

[ImkeF]

True. Maybe you try the Dataflows-alternative (I haven't tried it yet).

Alternatively you could use the new Flow/Power Automate robotic process automation (RPA) instead. Haven't tried it yet on a virtual machine running in the background, but that's what I would try next. 

[WorkHard]

That's a good idea. Basically create a mouse macro and run it on a server. Very primitive but I guess it would work with the least amount of effort until Microsoft finally includes the actual incremental updates as a service for us Pro mortals. 

I've looked into dataflows, looks straightforward but I don't have it enabled in this environment and I doubt I can get the admins to enable it.

I mean, the method you described would basically work beautifully if Microsoft would fix the bug or offer a way to specify which tables should be refreshed and which shouldn't in the scheduled service.

If someone reads this, please vote this idea:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20579641-exclude-a-table-from-ref...