Forum Discussion

rajiv_tarafdar's avatar
rajiv_tarafdar
Icon for Helper I rankHelper I
8 years ago

Power BI Report Server unable to Refresh Reports

Hi Guys,    We have an instance of Power BI Report Server (Oct 2017) version.   There are 5 reports scheduled to refresh from the Oracle Data Source daily at 6 am.    On the Schedule Panel in S...
Schedule issue
Schedule Refresh
rajiv_tarafdar's avatar
rajiv_tarafdar
Icon for Helper I rankHelper I
8 years ago

I ran the SQL and it gave me nothing. I checked the ID for ReportServer was 100 so modified the SQL and still nothing. 

The ReportSchedule table has 4 entries. 

 

I also ran an SQL on the ExecutionLog3 View. 

 

SELECT TOP (1000) [InstanceName]
,[ItemPath]
,[UserName]
,[ExecutionId]
,[RequestType]
,[Format]
,[Parameters]
,[ItemAction]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
,[AdditionalInfo]
FROM [ReportServer$PURDBPRD1001A].[dbo].[ExecutionLog3]
WHERE TIMESTART >= '2018-04-16 16:00:00'

 

This returned 3 records showing 1 report had successfully "Data Model" Refresh from "LIVE" source.

Rest of the reports have no entries in the view inspite of being scheduled on the same schedule.

 

Any Idea where I should be looking into next? 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Try changing all the INNER JOIN to LEFT OUTER JOIN and see what the query gives you

 

ReportServer.dbo.ReportSchedule is the scheduled refresh (this gives you the GUID that is used for the name of the Job that undertakes the refresh in SQL Agent)

 

msdb.dbo.sysjobs gives you the details of the SQL Agent Jobs that get setup to execute the refresh (if these are missing delete the scheduled refresh and recreate it)


INNER JOIN ReportServer.dbo.Subscriptions This just joins the ReportSchedule to the definition of the report/dataset in the RepoRTServer DB.

ReportServer.dbo.[Catalog] gives you the detail of the report or dataset

 

ReportServer.dbo.SubscriptionHistory gives you the history of the refreshes that have been issued

 

Try isolating out the report items that you think aren't running and see what you can find in each of the tables in turn.

 

 

 

 

  • rajiv_tarafdar's avatar
    rajiv_tarafdar
    Icon for Helper I rankHelper I
    8 years ago

    HI Anonymous

     

    I ran both SQL and see no errors. However the schedules are not running at each hourly interval. It seems to be missing some of the Schedule. E.g. Below 

     

    ItemPath RequestType Format ItemAction TimeStart TimeEnd Source Status ByteCount RowCount AdditionalInfo
    Report 1 Refresh Cache DataModel DataRefresh 24/04/2018 9:45 24/04/2018 9:45 Live rsSuccess 0 0 NULL
    Report 2 Refresh Cache DataModel DataRefresh 24/04/2018 5:45 24/04/2018 5:45 Live rsSuccess 0 0 NULL
    Report 3 Refresh Cache DataModel DataRefresh 24/04/2018 0:45 24/04/2018 0:45 Live rsSuccess 0 0 NULL
    Report 4 Refresh Cache DataModel DataRefresh 23/04/2018 23:45 23/04/2018 23:45 Live rsSuccess 0 0 NULL
    Report 5 Refresh Cache DataModel DataRefresh 23/04/2018 17:45 23/04/2018 17:45 Live rsSuccess 0 0 NULL
    Report 6 Refresh Cache DataModel DataRefresh 23/04/2018 15:45 23/04/2018 15:45 Live rsSuccess 0 0 NULL
    Report 7 Refresh Cache DataModel DataRefresh 23/04/2018 11:45 23/04/2018 11:45 Live rsSuccess 0 0 NULL
    Report 8 Refresh Cache DataModel DataRefresh 23/04/2018 10:45 23/04/2018 10:45 Live rsSuccess 0 0 NULL
    Report 9 Refresh Cache DataModel DataRefresh 23/04/2018 8:45 23/04/2018 8:46 Live rsSuccess 0 0 NULL
    Report 10 Refresh Cache DataModel DataRefresh 23/04/2018 6:45 23/04/2018 6:45 Live rsSuccess 0 0 NULL
    Report 11 Refresh Cache DataModel DataRefresh 23/04/2018 5:45 23/04/2018 5:45 Live rsSuccess 0 0 NULL
    Report 12 Refresh Cache DataModel DataRefresh 23/04/2018 4:45 23/04/2018 4:45 Live rsSuccess 0 0 NULL
    Report 13 Refresh Cache DataModel DataRefresh 23/04/2018 3:45 23/04/2018 3:46 Live rsSuccess 0 0 NULL
    Report 14 Refresh Cache DataModel DataRefresh 23/04/2018 1:45 23/04/2018 1:45 Live rsSuccess 0 0 NULL
    Report 15 Refresh Cache DataModel DataRefresh 23/04/2018 0:45 23/04/2018 0:45 Live rsSuccess 0 0 NULL