Forum Discussion
Is there any REST API to Refresh Data for PBIX files existing on Power BI Report Server?
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
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
Thank you.
I could achieve it using 'CacheRefreshPlans' API.
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 ?
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.
