cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KetanB Frequent Visitor
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
stpnet Established Member
Established Member

Re: 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

3 REPLIES 3
Super User
Super User

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

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)

stpnet Established Member
Established Member

Re: 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

KetanB Frequent Visitor
Frequent Visitor

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

Thank you. 

I could achieve it using 'CacheRefreshPlans' API.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 39 members 977 guests
Please welcome our newest community members: