Power BI's two best kept secrets: the PBIRS REST API and Power BI REST API

PBIRS REST API (public, completely customizable API)

If you use PBIRS to publish PBI reports to a centralized portal, or just want the ability to programmatically control your PBI development, deployments and system maintenance, there is a PBIRS API that you may not be aware of that can supercharge your PBI development environment.

It is not published but it is completely documented on SwaggerHub.com

It bears a close resemblance to the original SSRS v1 and v2 APIs, but in addition to all the SSRS functionality, it provides functionality for managing PBI reports, data sources, PBI data refreshes (data is refreshed on demand in SSRS reports), scheduling of PBI reports, managing uploaded ExcelWorkbooks and many other really useful features that developers can leverage to get the most out of your Power BI environment.

Publishing, updating PBI reports, data sources, posting ExecutionLog entries and more is made available via the PBIRS API

Power BI REST API (MSFT proprietary API)

And then there is the all-powerful Power BI REST API which serves as a successor to the ReportExecution2005.asmx and ReportService2010.asmx Reporting Services SOAP APIs that the original SSRS REST API was built upon. 

This API is not publicly documented (the SDKs are, however, which suffices for most usage scenarios) and not as extensible as original the SSRS REST API (ie. you need to authenticate with Entra and an online MSFT Power BI account; no custom authentication is possible), but it provides most all of the abundant paginated report and data source functionality in the original SSRS SOAP API along with a cornucopia of new API operations for managing Power BI users, workspaces and content.

The following is an example of using the Power BI REST API with PowerShell (using the MicrosoftPowerBIMgmt PowerShell module, if connecting via .NET you'd use the Microsoft.PowerBI.Api .NET library/Nuget package; an example of using the Power BI REST API in .NET can be found here):

This demonstrates getting a list of PBI workspaces, reports and getting an individual PBI report by name

With these two APIs organizations can continue to manage their SSRS/PBIRS environments and start managing their cloud-based Power BI environments- programmatically.

References: 

https://app.swaggerhub.com/apis/microsoft-rs/SSRS/PBI3.0

https://learn.microsoft.com/en-us/rest/api/power-bi/reports 

https://github.com/Microsoft/PowerBI-CSharp

https://github.com/microsoft/PowerBI-Developer-Samples/blob/master/PowerShell%20Scripts/Export-PowerBIReport.ps1

SQL Server 2025's sp_invoke_external_rest_endpoint with OPENJSON CTE for quickly and easily getting data from REST APIs

SQL Server 2025 introduces a convenient way to get data from a REST API endpoint directly through T-SQL and SQL Server utilities.

Outlined in lime green are the two items SQL Server 2025 handles well, discussed in this post

Prior ways of doing this usually involved using MSXML2.XMLHTTP (a COM object provided by Microsoft XML Core Services) through extended stored procedures, but with MSSQL 2025, there is a new SP, sp_invoke_external_rest_endpoint that is very readable and easy to use to get JSON (or XML) from an API response.

This brief article describes what an SP to get this data may look like, as well as the code to parse the JSON in the response to format the result as a table (vs. sending back all the JSON for the client to parse).

Here is an SP which fetches polling data for the Approval Polling data on current U.S. president Donald Trump:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	colin fitzgerald
-- Create date: 20250625
-- Description: SP to fetch data from VoteHub API
-- =============================================
CREATE OR ALTER PROCEDURE dbo.sp_GetVoteHubPollingData
	@LongView BIT = 0
AS
BEGIN
SET NOCOUNT ON;

EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

DECLARE @ret AS INT, @response AS NVARCHAR (MAX);

EXECUTE
    @ret = sp_invoke_external_rest_endpoint
    @url = N'https://api.votehub.com/polls?poll_type=approval&subject=Trump',
    @headers = N'{"Accept":"application/json"}',
    @method = 'GET',
    @response = @response OUTPUT;

;WITH ResponseContent AS
(SELECT [key], [value] FROM OPENJSON(@response)),
 ResponseJson AS
(SELECT [value] FROM OPENJSON((SELECT [value] FROM ResponseContent WHERE [key]='result')))

SELECT --value,
--id,
pollster,
[subject],
poll_type,
sample_size,
created_at, 
approve,
disapprove
FROM ResponseJson
OUTER APPLY OPENJSON(value) WITH(
  id nvarchar(255), 
  pollster nvarchar(255),
  [subject] nvarchar(255),
  poll_type nvarchar(255), 
  sample_size int, 
  created_at datetime,
  approve decimal '$.answers[0].pct',
  disapprove decimal '$.answers[1].pct'
)
WHERE created_at >= CASE WHEN @LongView = 0 THEN dateadd(mm, -3, getDate()) ELSE created_at END 
ORDER BY created_at DESC

EXECUTE sp_configure 'external rest endpoint enabled', 0;
RECONFIGURE WITH OVERRIDE;

END
GO

And here is the design view of the data source for a report using this polling table data:

And here is the design view of the report that will use this data:

If we CREATE dbo.sp_GetVoteHubPollingData as stored procedure on a database (in this case, I created it in 'master') that our data source connects to, then we can deploy the report to a Report Server or Power BI Report Server and run it:

This is the report as rendered within Power BI Report Server's Portal using extRSAuth for authentication and authorization

This is the report as rendered within extRSAuth custom PBIRS reporting portal on extrs.net 

Lots of neat stuff you can do with the new features of SQL Server 2025- check 'em out.

Next up: embedding a PBI version of this report in extrs.net, and embedding a HighCharts JS version of this report in extrs.net- all using this dbo.sp_GetVoteHubPollingData SP that uses sp_invoke_external_rest_endpoint.

References:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql

https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

extRS for useful common logic, reference data and extending SSRS

extRS is a .NET assembly, available as a Nuget package here, which is a utility for getting data out of SSRS, FedEx, UPS, USPS, financial markets, among several other useful functionalities.       

  

            

extRS was initially designed as a set of helper functions to access things in RS, but it evolved to contain a lot more..

Here are a couple examples of some of the useful things you can do with extRS:

Create, Get and Delete an SSRS Report Subscription

     [TestMethod]  
     public async Task CreateGetDeleteSubscriptionSucceeds()  
     {  
       string json = @"{  
       ""@odata.context"": ""https://localhost/reports/api/v2.0/$metadata#Subscriptions/$entity"",  
         ""Owner"": """ + Resources.User + @""",  
         ""IsDataDriven"": false,  
         ""Description"": ""string..."",  
         ""Report"": ""/Reports/USPolls"",  
         ""IsActive"": true,  
         ""EventType"": ""TimedSubscription"",  
         ""ScheduleDescription"": ""string..."",  
         ""LastRunTime"": ""2023-04-13T15:51:04Z"",  
         ""LastStatus"": ""string..."",  
         ""DeliveryExtension"": ""Report Server Email"",  
         ""LocalizedDeliveryExtensionName"": ""Email"",  
         ""ModifiedBy"": """ + Resources.User + @""",  
         ""ModifiedDate"": ""2023-04-13T15:51:04Z"",  
         ""Schedule"": {  
           ""ScheduleID"": null,  
           ""Definition"": {  
             ""StartDateTime"": ""2021-01-01T02:00:00-07:00"",  
             ""EndDate"": ""0001-01-01T00:00:00Z"",  
             ""EndDateSpecified"": false,  
             ""Recurrence"": {  
               ""MinuteRecurrence"": null,  
               ""DailyRecurrence"": null,  
               ""WeeklyRecurrence"": null,  
               ""MonthlyRecurrence"": null,  
               ""MonthlyDOWRecurrence"": null  
             }  
           }  
         },  
         ""DataQuery"": null,  
         ""ExtensionSettings"": {  
           ""Extension"": ""DeliveryExtension"",  
           ""ParameterValues"": [  
             {  
               ""Name"": ""TO"",  
               ""Value"": ""colin@sonrai.io"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""IncludeReport"",  
               ""Value"": ""true"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""Subject"",  
               ""Value"": ""true"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""RenderFormat"",  
               ""Value"": ""PDF"",  
               ""IsValueFieldReference"": false  
             }  
           ]  
         },  
         ""ParameterValues"": []  
       }";  
       Subscription subscription = await ssrs.SaveSubscription(JsonConvert.DeserializeObject<Subscription>(json)!);  
       Assert.IsTrue(subscription.DeliveryExtension != null);  
       var getResponse = await ssrs.GetSubscription(subscription.Id.ToString()!);  
       Assert.IsTrue(getResponse.Id != null);  
       var delResp = await ssrs.DeleteSubscription(subscription.Id.ToString()!);  
       Assert.IsTrue(delResp);  
     }  

Getting FedEx/USPS/UPS Shipping Rates

"A meaningful and useful abstraction that is able to withstand the test of time is very difficult to design. The main difficulty is getting the right set of members—no more and no fewer. If an abstraction has too many members, it becomes difficult or even impossible to implement. If it has too few members for the promised functionality, it becomes useless in many interesting scenarios." -Designing for Extensibility in .NET (Cwalina, Abrams, Barton)

References:

https://github.com/sonrai-LLC/extRS

https://www.informit.com/articles/article.aspx?p=3089304