Is there any REST API to Refresh Data for PBIX files existing on Power BI Report Server?

[KetanB]

Hi Friends,

I am working on REST APIs for Power BI Report Server. I could upload a report (Power BI Dashboard) using REST APIs.

Now, I am curious to know if we have any APIs to set the schedule refresh plan for files exisitng on Power BI Report Server. Based on documentation available on "https://app.swaggerhub.com/apis/microsoft-rs/pbirs/2.0#/"; I could not find any data refresh APIs for Power BI Report Server.

As per one blog "https://powerbi.microsoft.com/en-us/blog/announcing-data-refresh-apis-in-the-power-bi-service/" , data refresh APIs are available for Power BI Service; but no clues for this for Power BI Report Server.

Is there any ways (if I am correct about no APIs available) to programatically set scheduled refresh plan for Power BI Report Server?

Any help will be appreciated.

Thanks.

[Anonymous · Accepted solution]

If your objective is to force a data refresh for a PBIX file there are a couple of approaches to this.

Every scheduled refresh becomes a SQL Agent job with a transact SQL command that looks somehting like this

exec [ReportServer].dbo.AddEvent @EventType='DataModelRefresh', @EventData='ac452376-cfda-4772-8cf4-6b5a59ea321b'

The GUID in @EventData is the SubscriptionID from the dbo.Subscription Table. You can also use this subscriptionID to query the dbo.ReportSchedule table which will give you the ScheduleID which is the GUID used for the name of the SQL Agent Job that gets built on the SQL Server that actually runs the refresh. The same server as the ReportServer DB backend.

If you can fugure out the ID (GUID) of the PBIX (look it up in dbo.catalog) this gives you access to the subscription via the Report_OID column in the dbo.Subscriptions table.

So you could run some dirty PowerShell and some SQL queries to get you to the SubscriptionID GUID you need then just send the relevant SQL command to the backend Server.

This is really really not supported and will produce howls of anguish as being a dirty hack!

As an alternative I think you can trigger it via the REST API.

You can find the cache refresh plan for a report via the REST API like this

http://localhost/reports/api/v2.0/PowerBIReports(6654B9C1-8A84-432E-BC5F-883103A22832)/CacheRefreshPlans

Which gives you the ID of the cacheRefreshPlan. You can obviously figure out the reportID (GUID) using the REST-API

You can then access the cacherefreshplan using this

http://localhost/reports/api/v2.0/CacheRefreshPlans(ac452376-cfda-4772-8cf4-6b5a59ea321b)

Now the swagger documentation says you can make the cache refresh plan run using a POST request to

http://localhost/reports/api/v2.0/CacheRefreshPlans(ac452376-cfda-4772-8cf4-6b5a59ea321b)/Model.Execute

Having just poked this on something here it seems to work. Though the data hasn't moved so I've no menaingful way to tell.

I get an error if I try and run the Model.Execute off the end of the PBIReport/cacherferhsplans like this

http://localhost/reportsI/api/v2.0/PowerBIReports(6654B9C1-8A84-432E-BC5F-883103A22832)/CacheRefreshPlans(ac452376-cfda-4772-8cf4-6b5a59ea321b)/Model.Execute

But the CacheRefreshPlans stuff seems to work as per the swagger docs

[KetanB]

Thank you. 

I could achieve it using 'CacheRefreshPlans' API.

[AndreyBunin]

@KetanB @Anonymous , thanks for topic . I am take this approach, but Power Query receive error like : "DataSource Error :... 404 not found... ". I am trying connect via Odata in PowerBi Report Server(on screenshot)
Did you connect via Power Query, or just some progs like Postman ?

[d_gosbell]

---

@AndreyBunin wrote:

 but Power Query receive error like : "DataSource Error :... 404 not found... ". I am trying connect via Odata in PowerBi Report Server(on screenshot)
Did you connect via Power Query, or just some progs like Postman ?

---

The Model.Execute endpoint for the cache refresh plans only accepts POST requests. see pbirs | 2.0 | microsoft-rs | SwaggerHub. If you do an OData call in Power Query it will do a GET by  default. 

Generally if you wanted to trigger a refresh operation you would use a scripting language like PowerShell or Python to call the API with a POST request. Using Power Query does not make any sense.

[d_gosbell]

If you use the Chrome dev tools and check the network tab while creating a schedule on the portal you will see post commands being sent to a endpoint like "/reports/api/v2.0/schedules/model.describe" but the fact that this is not documented on swaggerhub means that this is not a publicly supported API. So while it might be possible to reverse engineer the commands sent by the portal to this endpoint there is probably a chance that that using it incorrectly will have unintended consequences or that this API will change in future versions (there would be some reason why this endpoint is not documented)