cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NaveenGattu
Regular Visitor

Power BI Report refresh based on condition- PBIRS

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.   

 

 

1 ACCEPTED SOLUTION
FarhanAhmed
Super User
Super User

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

 

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
FarhanAhmed
Super User
Super User

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

 

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.