Advice needed : On-Demand Refresh Optimization

[Anonymous]

Hello everyone !

I'll writte you to get some advice and best practices regarding the automation and optimization of the refresh of one of my dataset regarding stock history and forecast.
To help you out, I've illustrated the flows of my sources of data : 

Flow of data and refresh rate wantedSo I've built a tool to merge together stock history and stock projection on a rather aggregated level. 

I've got different sources of information that I gather to create one dataset which will be eventually used with a direct connection via Excel Publisher (the fact it ends up in Excel is not on my hand unfortunately).  

For the data in the past, I have a stock history table in Azure SQL Database, that I aggregate and format then append it to my main table. The load is pretty big and because of the aggregation it takes a couple of minutes. It can be refreshed weekly or daily. 

For the future though, it's only a small .csv file that's created/modified everytime users trigger a chain in the planning system. The file is stored on sharepoint and is formated then included in my dataset. 

I would like that when an on-demand refresh is made by the user, the dataset only refresh the future data (as the history won't change). To improve the responsiveness and allow multiple refresh per day  while my history data is refreshed via the scheduled refresh. 

Would incremental refresh work here ? 

Additional : Do you think I could use Microsoft Flow to trigger this (incremental) refresh each time my .CSV file is modified ? 

Thanks for your help and advice, let me know if anything wasn't clear. 

Regards, 
Kylo

[v-shex-msft · Accepted solution]

HI @Anonymous ,

Current power bi not support to setting different schedule refresh plans on dataset tables.

For you scenario, I'd like to suggest you to use web connector to get sharepoint files and setting incremental refresh on azure sql table.

After you publish to service side, power bi will enable onedrive refresh on csv file and you can setting scheduled refresh of dataset to refresh azure sql database.

Notice:

1. Incremental refresh require premium license.
2. Please not use composite models on this report, it may disable onedrive refresh option.

Regards,
Xiaoxin Sheng

[Anonymous]

Thank you for your answer, that was what I expected : different schedule refresh plans on dataset tables is not (yet?) accepted. 

Incremental refresh would work so it's good to know. Now it's time to convince my company to go premium 😉