Power BI Report refresh based on condition- PBIRS

[NaveenGattu]

Hello Experts,

I am new to Power BI - We have reports scheduled in Power BI Reporting Server and data is coming from oracle DB scheduled procedures.  We have kept some buffer time between stored procedures and PBIRS schedules.  We have instances where stored procedure fails due to which the schedules in Reporting Server also fails, in these scenarios we are manually refreshing the reports.  Now we dont want this manual intervention because some reports has to run on weekends/holidays. 

All these procedures writes to a log table of Success/Failed status - Lets say if I extract this log table data to Power BI and when the refresh is trigged - is there anyway based on the value of this log table can we stop/start the refresh?  I mean if it fails can we create another schedule which runs only if the previous schedule failed? Or is there anyway to trigger the refresh outside of Power BI Reporting Server?

Really appreciate any ideas/suggestions.   

[FarhanAhmed · Accepted solution]

There is a possibility of a bit of work around.

Power BI Report Server creates a refresh schedule in SQL Server Agent job where it is hosted.

Now I think in your scenario you need to create 2 schedules for your report refresh. one that is scheduled other will be on-demand.

Once your stored procedure fails you can disable your current schedule 

EXEC msdb.dbo.sp_update_job @Anonymous_name='JobName Scheduled',@enabled = 0

and then use this in your trigger in your log table whenever it updates or inserts new status to executes On Demand Job

EXEC dbo.sp_start_job N'JobName On Demand' 

to start your on-demand schedule and then enable your scheduled job when it finishes

EXEC msdb.dbo.sp_update_job @Anonymous_name='JobName Scheduled',@enabled = 1

[Greg_Deckler]

@NaveenGattu - Use Power Automate coupled with the Power BI REST API to kick off a dataset refresh. Power Automate may actually already have an action for Power BI refresh built in.

[d_gosbell]

I'm pretty sure that the Power Automate/REST API only works for reports on the service, not for ones hosted in PBIRS on-prem.

For on-prem it's a bit trickier. There are 2 options I'm aware of.

1. You can setup a once-off schedule with a date in the past and then trigger that using an API call (see this thread for more details)
2. You could move the data model out of your report and host it in Analysis Services tabular (SSAS), then you can use any of known methods for refreshing a SSAS model (eg XMLA request, SSIS package, Powershell script, SQL Agent job)