Something cool has just been announced for Azure SQL DB: the ability to call a limited number of REST APIs direct from TSQL. The announcement is here:
There are code samples here:
https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints
And guess what, the Power BI REST API is one of the APIs you can call! This means, for example, you can run DAX queries via the Execute Queries endpoint (bearing in mind all the limitations) from TSQL:
I think this opens up a lot of possibilities. What do you think? Leave a comment!
Does this work in Power BI Datamarts?
I’m pretty sure it doesn’t work, but I’m not 100% sure
I can confirm it doesn’t work in datamarts
I haven’t used it yet but I was under the impression that Synapse Serverless Pools already had some of these capabilities. Eg. here is a link that demonstrates how to write a query against remote parquet data:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files
In the very least, I know that lots of ADF customers are already doing similar things.
They often consume REST api data from a pipeline, and then immediately execute queries against it. I suspect this feature makes it easier to perform the data retrieval from TSQL directly – and avoid the cost of the ADF billing meters. (That would be a very welcome change, given the surprisingly high cost of ADF relative to Azure SQL. I’m not sure it was Microsoft’s intention to cannibalize their ADF sales, but that might be the unintended consequence.)
Very pleased to see this released now. My interest in it was to be able to process Azure AS and/or PBI from within T-SQL. When I’ve loaded data using T-SQL into a DW, I then want to process the model or tables. Am planning to write a blog post about this soon.
Chris, Power BI’s purpose is to call data from different sources, one being a database/s, SQL being one of them and display the data in an understandable way the users can understand or make sense of it. It has the capability to connect to the database to retrieve the data built in from Power BI to SQL as an example. What would be the purpose or goal for SQL to call Power BI? I can see this in a DW or DL scenario where you want to do something with data (i.e. ETL) but want to query your thinking and get ideas why you would need to do it against a Power BI API?
Hi Chris
Does this new feature require a Premium or Premium per User license?
I have a Pro license and got this error message:
Msg 31612, Level 16, State 1, Procedure sys.sp_invoke_external_rest_endpoint_internal,
Connections to the domain app.powerbi.com are not allowed.
Ha, stuff like this has been possible for ages thanks to OLE Automation:
https://zealousweb.medium.com/calling-rest-api-from-sql-server-stored-procedure-85ec1ab73504
(Just stating facts, not saying I support this 🙂
This is a game-changer! Being able to call REST APIs directly from TSQL in Azure SQL DB opens up a whole new world of possibilities. The integration with Power BI REST API is particularly exciting. Imagine the seamless integration between SQL queries and Power BI visualizations. Can’t wait to try it out!