Forum Discussion
Can't query data from database directly - have to use Stored Procedures...
The wizard sounds promising, but it is still very limited in Power BI side, as there's no any desiner API you can use to change a PBIX file. Check this idea Power BI Designer API and vote it up if you have interest.
For Power BI Embeded, The only avaiable approach I can think of is, instead of result out to a file, save the result into a Azure SQL DB. Then use a direct query to the DB and you have no worry about data refresh.
For Power BI Service, you can result out to a file and then use a gateway to schedule refresh.
Microsoft Employee
The wizard sounds promising, but it is still very limited in Power BI side, as there's no any desiner API you can use to change a PBIX file. Check this idea Power BI Designer API and vote it up if you have interest.
For Power BI Embeded, The only avaiable approach I can think of is, instead of result out to a file, save the result into a Azure SQL DB. Then use a direct query to the DB and you have no worry about data refresh.
For Power BI Service, you can result out to a file and then use a gateway to schedule refresh.
Azure SQL is probably the way to go (although due to the nature of the data being so confidential there has been a.... reluctance.... to go to cloud which I am trying to overcome.
This is definitely enough for me to explore further, thanks very much for your help!