Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

 

 

Power BI Server Schedule.PNGPower BI Report Schedule - Not Refreshing.PNG

 

Thanks

5 REPLIES 5
Anonymous
Not applicable

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

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
Not applicable

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.

 

 

 

 

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

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.