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 EmployeePower BI Embedded at this moment is not a good option for this case I think. As it doesn't has refresh feature except DirectQuery mode to Azure SQL DB/DW. And even your SQL happens to be Azure SQL and not a on-premises one, as per my experience and test, stored procedure doesn't work well with DirectQuery mode.
What are the parameters in your case? Is it possible load all data in the report?
Unfortunately loading all the data is not really an option as so much is calculated on the fly. For example we may want to query financial data in a number of different currencies (the stored procedures work out the currency exchange for a trade/transaction for the date that the trade happened meaning it's not as simple as running the report in just USD or GBP and then converting.
While it theoretically could be possible to extract this information from the database and work out these calculations within Power BI the number of possible computations would be extremely high leading to a huge amount of overhead as opposed to doing the "heavy lifting" in SQL as we do now.
Is there no way of getting Power BI to refresh data from database? My goal is to have some form of wizard (that I will write myself) which takes the required parameters and passes them through to the report... I can get to the point of automatically generating the SQL but the next bit seems to be impossible at the moment...
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...
- Eric_Zhang
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!