Power BI Report Server unable to Refresh Reports

[rajiv_tarafdar]

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 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?  

Thanks

[Anonymous]

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

[rajiv_tarafdar]

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]

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]

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

[rajiv_tarafdar]

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.