Forum Discussion

NaveenGattu's avatar
NaveenGattu
Regular Visitor
6 years ago
Solved

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 ...
Power BI Desktop
Power BI Report Server
  • FarhanAhmed's avatar
    FarhanAhmed
    6 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
d_gosbell
Icon for Super User rankSuper User
6 years ago

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)