topic How Data Refresh programmatically? in Report Server

How Data Refresh programmatically?

MeiSalas — Fri, 07 Oct 2022 16:47:16 GMT

Is there another way to do a data refresh for a PowerBI report (via PowerShell or Rest API, how to access the Rest API, do we enable this?)

I have a query to check what powerBI report has failed, but in order to do the refresh, i either go to the report and do the refresh, or run the job that corresponds to the Schedule GUID.

Is there a way to do a Data Refresh just by knowing the Catalog Item? Without doing a Schedule?

Thanks a lot!

Re: How Data Refresh programmatically?

lbendlin — Sat, 08 Oct 2022 21:39:32 GMT

yes to all - plus you can do it via Power Automate. You can even get more granular and refresh individual tables and even partitions.

Enhanced refresh with Power BI REST API - Power BI | Microsoft Learn

Re: How Data Refresh programmatically?

d_gosbell — Mon, 10 Oct 2022 06:40:07 GMT

@MeiSalas wrote:

Is there a way to do a Data Refresh just by knowing the Catalog Item? Without doing a Schedule?

No, for the on-prem Report Server you can only trigger a refresh via a schedule. Note that you can create a schedule with a one off date in the past and trigger that, but there is no API to refresh with just the catalog item reference.

@lbendlin - Power Automate is a great solution for the cloud service, but it's not a solution for the on-prem Report Server.

Re: How Data Refresh programmatically?

lbendlin — Mon, 10 Oct 2022 11:44:19 GMT

@d_gosbell noted. I never know if a question posted in the report server section is actually related to report server. The "catalog" wording should have been my hint to leave this alone as I have no experience with report server.

Is there a way to use the SQL scheduler?

Re: How Data Refresh programmatically?

d_gosbell — Mon, 10 Oct 2022 19:37:55 GMT

@lbendlin wrote:

I never know if a question posted in the report server section is actually related to report server. The "catalog" wording should have been my hint to leave this alone as I have no experience with report server.

🙂 it can be hard to tell. A lot of people do not differentiate between "Server" and "Service"

@lbendlin wrote:

Is there a way to use the SQL scheduler?

Not easily, the SQL agent jobs are created with Schedule GUID so you are faced with the orignal issue of having to link a ScheduleID and a catalog item

Re: How Data Refresh programmatically?

enricocorizza — Mon, 27 Feb 2023 12:10:29 GMT

You can alternatively run an SQL Query to do that and call it from PowerShell. We do it directly with SQL in some agent jobs since we want to refresh after certain updates in the database for our datawarehouse. We have a custom view with all subscriptions and relative agent jobs and then it's very easy to trigger subscriptions and schedule refresh this way:

DECLARE @SQLAgent_Job_Name NVARCHAR(36) = (
SELECT [SQLAgent_Job_Name]
FROM [Reports].[AllSubscriptionsAndJobs]
WHERE ReportName = 'Your report name in Report Server / PBRS'
)

EXEC msdb.dbo.sp_start_job @job_name = @SQLAgent_Job_Name