Exploring The New SSRS 2017 API In Power BI

One of the new features in Reporting Services 2017 is the new REST API. The announcement is here:

https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/10/02/sql-server-2017-reporting-services-now-generally-available/

And the online documentation for the API is here:

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

Interestingly, the new API seems to be OData compliant – which means you can browse it in Power BI/Get&Transform/Power Query and build your own reports from it. For example in Power BI Desktop I can browse the API of the SSRS instance installed on my local machine by entering the following URL:

http://localhost/reports/api/v2.0

…into a new OData feed connection:

image

image

image

This means you can build Power BI reports on all aspects of your SSRS reports (reports on reports – how meta is that?), datasets, data sources, subscriptions and so on. I guess this will be useful for any Power BI fans who also have to maintain and monitor a large number of SSRS reports.

However, the most interesting (to me) function isn’t exposed when you browse the API in this way – it’s the /DataSets({Id})/Model.GetData function. This function returns the data from an SSRS dataset. It isn’t possible to call this function direct from M code in Power BI or Excel because it involves making a POST request to a web service and that’s not something that Power BI or Excel support. However it is possible to call this function from a Power BI custom data extension – I built a quick PoC to prove that it works. This means that it would be possible to build a custom data extension that connects to SSRS and that allows a user to import data from any SSRS dataset. Why do this? Well, it would turn SSRS into a kind of centralised repository for data, with the same data being shared with SSRS reports and Power BI (and eventually Excel, when Excel supports custom data extensions). SSRS dataset caching would also come in handy here, allowing you to do things like run an expensive SQL query once, cache it in SSRS, then share the cached results with multiple reports both in SSRS and Power BI. Would this really be useful? Hmm, I’m not sure, but I thought I’d post the idea here to see what you all think…

10 thoughts on “Exploring The New SSRS 2017 API In Power BI

  1. The dataset caching is certainly an interesting route to improve the performance of the refresh of PBI models. The reports in the PBI model likely won’t be affected since they don’t go directly to the SSRS dataset.

  2. Interesting idea Chris. Specifically the one about custom connectors.Might actually use it soon.
    One thing about the POST request maybe a little bit deceiving, as I can make POST requests with Web.Contents for other web services. The only limitation I can think of is passing the Windows credentials on.Which MS considers a no-no for post requests.

  3. Looks interesting, however I’m curious why the old SSRS report data source was never included given it’s part of the excel powerpivot sources. We use SSRS 2012 and the folder level security would give us a nice way to provide an intermediate step between ssrs data source old report.

  4. Looks interesting, however I’m curious why the old SSRS report data source was never included given it’s part of the excel powerpivot sources. We use SSRS 2012 and the folder level security would give us a nice way to provide an intermediate step between ssrs data source old report.

  5. This is really interesting to me. We have had use cases where we would like to share data from an existing SSAS tabular model to an application through managed code. The pain point has been the need to have a developer write DAX in managed code. Can I assume we could create the SSRS data set based on the SSAS tabular model and expose the data to the application via the post request?

  6. I think it’s funny that you can read from SSRS using ODATA, but SSRS itself still can’t use ODATA as a data source.

    That being said, I wonder if a use for your example would be to accomplish some kind of incremental refresh with SSRS caching and use that as the Power BI data source.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s