04-09-2018 06:33 PM
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 Site Setting, it shows the scheduler is running and refreshing every morning (Pic 1)
But the actual report is not refreshing and the schedule on the report shows not refreshed (Pic 2).
I have checked the log files on the server but unable to find anything specific to schedule refresh fail. SQL Server Agent is Running on the Servers and there are no errors on the Event Logs for SSAS.
How do I find out the cause of this error? Is there a place to check the execution of each Schedule / Report?
04-10-2018 03:10 AM - edited 04-10-2018 03:15 AM
To "see" the underlying error messages for the failures you can use something like the query below (this should be run against the ReportServer DB of your PBI SSRS Server)
SELECT sj.name AS SQLAgentJobName ,c.name AS ReportName ,c.[path] AS ReportPath ,s.[Description] as SubscriptionName , rs.SubscriptionID , s.laststatus , s.eventtype , s.LastRunTime , sj.date_created , sj.date_modified ,err.[Message] AS StatusMessage ,err.SessionID ,err.Errs ,ErrData.ErrCode AS ErrorCode ,ErrData.ErrMsg AS ErrorMessage FROM ReportServer.dbo.ReportSchedule rs INNER JOIN msdb.dbo.sysjobs sj ON rs.ScheduleID = CAST(sj.name AS uniqueidentifier) and 101 = sj.category_id INNER JOIN ReportServer.dbo.Subscriptions s ON rs.SubscriptionID = s.SubscriptionID INNER JOIN ReportServer.dbo.[Catalog] c ON s.report_oid = c.itemid LEFT OUTER JOIN (SELECT MAX(SubscriptionHistoryID) AS SubscriptionHistoryID, SubscriptionID FROM dbo.SubscriptionHistory GROUP BY SubscriptionID) sh ON rs.SubscriptionID = sh.SubscriptionID LEFT OUTER JOIN (SELECT SubscriptionHistoryID, [Message], JSON_VALUE(Details, '$.SessionID') AS SessionID, JSON_QUERY(Details, '$.Errors') AS Errs FROM dbo.SubscriptionHistory ) err ON sh.SubscriptionHistoryID = err.SubscriptionHistoryID CROSS APPLY OPENJSON(err.Errs) WITH( ErrCode INT '$.ErrorCode', ErrMsg NVARCHAR(4000) '$.Message') AS ErrData -- to find specific report last status -- WHERE e.name = 'Usage Stats' -- to find failed status WHERE LastStatus <> 'Completed Data Refresh'
You can obviously filter this for specific reports and or date ranges as required.
This should at least point you in the right direction
Note that there is a "magic number" in that SQL that makes it work.
INNER JOIN msdb.dbo.sysjobs sj ON rs.ScheduleID = CAST(sj.name AS UNIQUEIDENTIFIER) AND 101 = sj.category_id
the 101 is the category_id of the Job Category called "Report Server"
you can find the correct value for this using the following.
USE msdb GO SELECT * FROM dbo.syscategories
I think 101 is safe on most systems but it may well be different on your installation
04-15-2018 04:17 PM
Thanks for the suggestion. Sorry I have been away and no one has checked. I will try it today.
Why does it have to be so complicated. MS surely needs to implement a simpler way of looking at the log issues.
04-15-2018 11:36 PM
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]
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?
04-16-2018 05:47 AM
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.
04-23-2018 06:01 PM
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