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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
xantari
Helper II
Helper II

Scheduled Refresh notifications (On-Premise)

We can not find a way to get Refresh Notification failure emails when a scheduled data refresh occurs on-premise.

 

Apparently this has existed for a long time on Power BI (because they have to deal with a lot more data connectivity issues with the gateway service I am guessing).

 

Where is this function in the on-premise version? As it is important on-premise as it is on cloud service.

12 REPLIES 12
agent_007
New Member

This is my solution as second step in SQL Agent. Preliminarily you should configure MS SQL MAIL and find out GUID of your Catalog.ItemID = '????'. This step sends notification by mail after successfully refreash data (1 step) to all users of this report

 

DECLARE	
	@xml  XML,
	@str varchar(8000) = ''
SELECT @xml = CAST(XmlDescription as XML)
FROM Catalog WITH (NOLOCK)
INNER JOIN Policies WITH (NOLOCK) ON Catalog.PolicyID = Policies.PolicyID
LEFT OUTER JOIN SecData WITH (NOLOCK) ON Policies.PolicyID = SecData.PolicyID AND AuthType = 1
WHERE Catalog.ItemID = 'E3280AC8-EFF4-4AF6-8ED3-583C6112139A' 
AND PolicyFlag = 0
declare
	@t table (mail_to varchar(100))
SET QUOTED_IDENTIFIER ON;
insert 
	@t
SELECT	xmlData.A.value('.', 'VARCHAR(100)') AS mail
FROM	@xml.nodes('Policies/Policy/GroupUserName') xmlData(A)
delete @t
where mail_to = 'BUILTIN\Administrators'
update @t
set mail_to = REPLACE(REPLACE(mail_to, 'KCELL.KZ\',''), '@kcell.kz','') + '@kcell.kz'

SELECT @str = @str + ISNULL(t.mail_to + ';', '') FROM @t t

SET @str = SUBSTRING(@str, 1, LEN(@str) - 1)

WAITFOR DELAY '00:10:00'; 

DECLARE @body NVARCHAR(MAX)
SET @body = '
<html>
	<body>
		<H2>Daily RSSS-OU-CCHT statistics</H2>
		<H3>Data were updated successfully!</H3>
		<p><a href="http://192.168.223.13/Reports/powerbi/CCD%20reports/Daily%20RSSS-OU-CCHT%20statistics">Open report</a></p>
	</body>
</html>'

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'power_bi_report_server',
    @recipients = @str,
    @copy_recipients = 'your_name@kcell.kz',
    @body = @body,
    @subject = 'Power BI Report Server Alert',
    @body_format = 'HTML';

 

 

 

 

 

Anonymous
Not applicable

In Power BI report Server, every schedule you create goes to SQL Server Agent JOB, so you can go to the SQL Server Agent JOB and then you can add one more step after the current step for sending the email. You might need to configure email in SQL in order to send the email through JOB.

WHich one of the 150 SQL Agent jobs do I do that on. They are just labeled as a GUID.

 

This doesn't seem like a good solution.

 

Microsoft needs to fix this if there is no other way. Why don't they just add a task at the end of these SQL Agent jobs for the server administrator if it is setup in the Power BI Server backend? 

 

Seems like basic functionality is missing here.

Please use the below query and schedule it through SQL Server Agent to notify/email certain users if a Data Refresh failure happens and that will notify you if a data refresh did not run successfuly. 

 

SELECT Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
MAX(SubscriptionHistory.StartTime) start_time,
MAX(SubscriptionHistory.EndTime) end_time,

SubscriptionHistory.Message,
SubscriptionHistory.Details

FROM subscriptions
JOIN dbo.SubscriptionHistory
ON Subscriptions.SubscriptionID = SubscriptionHistory.SubscriptionID
WHERE CAST(StartTime AS Date) = CAST(GETDATE() AS DATE)

GROUP BY Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
SubscriptionHistory.Status,
SubscriptionHistory.Message,
SubscriptionHistory.Details

HAVING Subscriptions.LastStatus LIKE 'Data Refresh failed%'

 

Note: This query will be run in the BI Database that has the metadata for the Power BI Report Server.

 

 

Hope this helps!

Please use the below query and schedule it through SQL Server Agent to notify/email certain users if a Data Refresh failure happens and that will notify you if a data refresh did not run successfuly. 

 

SELECT Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
MAX(SubscriptionHistory.StartTime) start_time,
MAX(SubscriptionHistory.EndTime) end_time,

SubscriptionHistory.Message,
SubscriptionHistory.Details

FROM subscriptions
JOIN dbo.SubscriptionHistory
ON Subscriptions.SubscriptionID = SubscriptionHistory.SubscriptionID
WHERE CAST(StartTime AS Date) = CAST(GETDATE() AS DATE)

GROUP BY Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
SubscriptionHistory.Status,
SubscriptionHistory.Message,
SubscriptionHistory.Details

HAVING Subscriptions.LastStatus LIKE 'Data Refresh failed%'

 

Note: This query will be run in the BI Database that has the metadata for the Power BI Report Server.

 

 

Hope this helps!

FYI, I found the SQL AGent job (labeled as GUID 87B024E7-A122-4DE9-818A-8016826EB9E3) in SQL Agent.

 

It shows as successful this morning. But the data refresh shows error on Power BI front end.

 

So it appears SQL Agent failures do NOT occur when the data refresh fails.

Anonymous
Not applicable

The problem is the call to the subscription succeeds though the subscription itself fails. Which is, as you say, a bit rubbish!

 

You can add a second SQL Step to each SQLAgentJob and basically force a failure using some code like the stuff below. You can then just add your "operators" to the Notifications for the SQL Agent job and set the first step up to call the second on success and both steps to Fail the job!

 

This will generate a SQL Agent ALERT to the operators!  You could of course log the details of the query into a table somewhere  before you call RAISEERROR so you would have the details of what had failed.

 

Figuring out why the **bleep** thing failed is another matter entirely.

 

DECLARE @SQLAgentJobID UNIQUEIDENTIFIER;


SET @SQLAgentJobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)));

IF EXISTS(

	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
	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.ReportSchedule c
			--    ON CAST(sj.name AS uniqueidentifier) = c.ScheduleID
			INNER JOIN ReportServer.dbo.Subscriptions s
				ON rs.SubscriptionID = s.SubscriptionID
			INNER JOIN ReportServer.dbo.[Catalog] c
				ON s.Report_OID = c.ItemID
	-- to find specific report last status 
	-- WHERE e.name = 'Usage Stats'
	-- to find failed status
	WHERE	LastStatus <> 'Completed Data Refresh'
	-- for a specific SQL Agent Job
			AND sj.name = @SQLAgentJobID)
BEGIN
	--LOG the query results above to something if you want a simple way of finding out what has failed
RAISERROR('Data Refresh Failed!',16,1); END -- NOTE you have to look in subscription history for any error messages/details (good luck with that!)

PS Have I mentioned how shoddy and third rate the logging and monitoring is for PBI SSRS Server?

 

 

 

Anonymous
Not applicable

I have just realised 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 these 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

 

 

Anonymous
Not applicable

Thanks much @Anonymous, for the info. Much Appriciated.

 

Anonymous
Not applicable

I extended the query above slightly in the following post to show the errors that are recorded by the refresh process

 

Data Refresh Fail

 

 

 

 

Anonymous
Not applicable

HI @xantari,

 

You can create your idea for this on the below URL and keep voting for the same.

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.