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
gemidriver
Helper II
Helper II

Receive email for Refresh failure

Is it possible to setup email alerts if a scheduled refresh fails?

13 REPLIES 13
Jayendran
Solution Sage
Solution Sage

 Hi @gemidriver  The feature which you are looking for is now available with the latest update 🙂 

 

https://powerbi.microsoft.com/en-us/blog/sending-refresh-notifications-to-others/

 

 

that looks like it is for cloud

we use on prem

matthewtmitchel
Frequent Visitor

How do we request that this feature is added to the On Premise version of Power BI Report Server?  We have internal clients that are requesting this, and although there are ways documented in the message string around it, it would be much easier for our internal clients to have the "Send me a notification email if the refresh fails" option available.

seena
Frequent Visitor

You can schedule refresh at the dataset level. Goto Schedule refresh and select below option at the bottom of where you enter the schedule time.

 

Send refresh failure notification to me

please explain?

we have different types of error messages

 

refresh at dataset level?

we have to setup a refresh on each report

what i would like is someway of knowing if a refresh has failed

Scheduled refreshing is done at the dataset level for the Power BI Service. Reports will use the latest data that is in the dataset it connects to.

 

There's limited email notification settings for a refresh failure, but you can tick the 'Send refresh failure notification email to me'. I can confirm this works (example below) :). I'm not sure if this sends an email to all owners of the workspace or not, someone else may be able to confirm that.

Refresh.PNG

 

Refresh Failure.PNG

we are using the on premise report server - not the cloud version

Ahh yes! My apologies, I jumped the gun. Man Frustrated It seems you and I both did not read the reference articles that Yuliana Gu posted above. Noteably, https://workingondata.wordpress.com/2017/11/02/pbirs-scheduled-data-refresh-execution-history/

 

That contains all the details we are after 🙂

v-yulgu-msft
Employee
Employee

Hi @gemidriver,

 

At the moment, it is not supported to setup email alert for scheduled refresh failure.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ok so it isn't supported

is there something that can be used ?

we have some schedules failing to refresh, and it would be great to be able to be notified in some way

cheers

Anonymous
Not applicable

We have setup a couple of SSRS reports to make it easier to see the status of the refreshes across the server, the top level report shows the status of all data refreshes. You could schedule this report to be delivered via email and indeed you could schedule a version of it to be delivered that only showed recent failures. Its not quite what you want but its better than nothing.

 

The query to get the status of the refreshes is here (NOTE this can be broken by changes to the underlying ReporTServer DB schema in future relases of SSRS-PBI Server

 

SELECT
	sj.name AS SQLAgentJobName
	,c.name AS ReportName
	,c.[path] AS ReportPath
	,c.ItemID
		, CASE 
		WHEN s.laststatus = 'Completed Data Refresh' THEN s.laststatus
		WHEN LEFT(s.laststatus,19) =  'Data Refresh failed' THEN 'Data Refresh failed'
		ELSE 'Status Unknown'
		END AS ShortStatus
	, 'http://yourservername/yourserverpath/manage/catalogitem/properties' + c.[path]  AS managePath
	, REPLACE(  LEFT(c.[path], CHARINDEX('/',c.[path],2)) , '/' ,'') AS basepath
	,s.[Description] as SubscriptionName
	, rs.SubscriptionID
	, s.laststatus

	,sh.SubscriptionHistoryID
	, CASE s.laststatus
		WHEN 'Completed Data Refresh' THEN 1
		ELSE 0
		END AS successFlag
	, CASE s.laststatus
		WHEN 'Completed Data Refresh' THEN 0
		ELSE 1
		END AS FailFlag
	, s.eventtype
	, s.LastRunTime
	, CASE jsch.next_run_date
            WHEN 0 THEN cast('n/a' as char(10))
            ELSE	convert(char(10), 
							CONVERT(datetime, 
									CONVERT(char(8),
											jsch.next_run_date)
									)
							,120)  
					+ ' ' 
					+ left(
							STUFF(
									(stuff(
											(replicate(
														'0', 
														6 - len(jsch.next_run_time)
													  )
											)
											+ convert(VARCHAR(6),
														jsch.next_run_time
													  )
											,3
											,0,
											':'
											)
									)
									,6
									,0,
									':'
								  ),
							8)
        END AS NextRunTime
	, sj.date_created
	, sj.date_modified
	, 1 AS ct
FROM ReportServer.dbo.ReportSchedule rs 
		INNER JOIN msdb.dbo.sysjobs sj
            ON rs.ScheduleID = CAST(sj.name AS uniqueidentifier)
			and 101 = sj.category_id
			LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch
				ON sj.job_id = jsch.job_id
        INNER JOIN ReportServer.dbo.Subscriptions s
            ON  rs.SubscriptionID = s.SubscriptionID
			AND 'DataModelRefresh' = s.eventTYpe
        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

Hi @gemidriver,

 

The scheduled refresh history will be recorded in Power BI Report Server database hosted on SQL Server. Maybe you could configure an alert in SQL Server when the state of scheduled refresh is recorded as "Failure" which will send an email to specific recipients.

 

Reference: PBIRS scheduled refresh data refresh: Execution history
                  Query Details of given SSRS Report from ReportServer Database tables

                  Send an email from a trigger

                  Get an alert when a certain record changes in SQL Server

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

Any ideas on tables/columns to monitor?

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.