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.
I had an idea... what if I made my "wizard" run the SQL queries and right the result out to a file? I would then just need a way of getting the report to refresh on command (does such functionality exist?)
The reports are generally only going to be viewed by one user (they are client specific) so I could use a file as the data source as this makes it flat file but I would need to be able to get my "wizard" to be able to tell Power BI to refresh it's data from this file...
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!