Currently, if your dataflow fails the only person who will be notified is the owner of the dataflow.
We want all our developers within the team to know. There doesn’t appear to be any way to do this at the moment but there is a workaround that was suggested to me by on the Power BI Forums by collinq as an idea starter and I thought I would run with it and see what happens.
It all relies on a Refresh date
Refresh date
In my main dataflow I have the following Query
This was created from a blank query
let Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})in Source
This gets updated every time there is a refresh on the main dataflow
Create a New Report in Power BI Desktop
Go to Power BI desktop and Get Data
Dataflow is the source of the data
And we only need this object
I am going to edit the Query in Power Query Editor
Last Refresh date has been split into Date and time.
Then a custom column was created for todays date
DateTime.LocalNow()
This was split into Date and Time. It is very likely that we may decide to use time later so this is why it has been added for now.
Now we find the number of days between the last refresh and today
0 -Duration.Days(Duration.From([last Refresh Date]-[Todays Date]))
0- is added to remove the minus at the start of the number so -1 becomes 1
Close and Apply and a card is added
Publish to Service
Power BI Service
Go to the report that has just been created
And Pin the card to a dashboard. In this case, an Issues dashboard has been created
The idea at this point is. If the Refresh date isn’t refreshed the number of days between will become 2 because todays date will change and we can be notified
This will need testing
Power BI Dashboard (Create Alerts)
Go to the dashboard
Choose Manage Alerts
We want to know if it goes above 1. this means that last nights refresh has failed to happen.
(But if it fails to happen, will the Current date refresh?)
At present, an alert will only be sent to the owner of the report and it will be seen within Power BI but we want everyone to know.
This is why we are going to use Power Automate / Logic Apps
We have an Azure Subscription so I can add a Logic App within our subscription
Instead of clicking the above link we are going to go into Azure, but the principal will be the same
Save the Alert
Schedule a refresh
The new report has created a dataset
go to Settings and Scheduled refresh to keep this up to date
Azure Logic Apps
Create a new Logic App in Azure
Search for Power BI. We want the trigger ‘When a data driven alert is triggered(Preview)
I am signing in with my own credentials (Note that my password is updated every month so if these credentials are used it will need adding into the governance.
Our alert has been saved and added to the alert list
Just for the time being its being left at 3 times a day
We have our trigger, now we need to know what will happen on the trigger
New Step
for the time being chose a fairly easy option of sending an email
You can search for the dynamic content as you create the body and subject. Here we want to bring to attention the value in the tile and the alert threshold.
- The HTML <li> element is used to represent an item in a list
- The <strong> tag is used to separate the text from the rest of the content. Browsers traditionally bold the text found within the <strong> tag
- The <big> tag is used to make the text one size bigger
- The <ul> tag defines an unordered (bulleted) list
- The <a> tag defines a hyperlink, which is used to link from one page to another. The most important attribute of the <a> element is the href attribute, which indicates the link’s destination.
I have added two users to the email so they can both be notified
Save your logic app. Its ready
Testing the New Process
The dataflow is schedule to refresh at 11 PM
Dataflow issues data flow is scheduled at 12 AM
On the night of the 28th of September, everything failed. I got the emails because I am the dataflow owner but no email from the actual set up.
Testing has failed
lets have a look to see whats happened.
We have two fails, and one on the dataflow we have set up
It looks like the refresh token expired. Please go to this dataset’s settings page, and reenter the OAuth2 credentials for the Extension data source.
Going into the report and we still see this
Which is incorrect.
We would get more of an understanding if we could match up the dates to what is happening.
However its clearly not updated
Dataflow Settings
Scheduled refresh is on and set to run at 12 midnight. The errors were emailed through just after 11.
The alert is there.
lets go back to desktop and add some more information.
After a refresh in Desktop we can now see this information
Which is correct. this says to me that even though we have a refresh going on, it didn’t refresh in Service possibly. The new report is published up to Service.
Back in Service
This is interesting. Our new multi row card shows the correct information. However our card still says 1 day which isn’t correct.
A quick refresh of the data set and we can still see one on the card so we have a difference in between Service and Desktop.
Refresh of the report and now its worked and we can see 2 days difference
So there are a few issues here. Why did it not refresh the card on the data set refresh but it did when the actual report was refreshed?
Its actually the dashboard that is doing the work here. the new multi card is pinned to the dashboard. lets go and have a look at it.
The dashboard only updated once the new visual was pinned to it
So the failure has been that the report and dashboard didn’t refresh, even though it is set to refresh.
You can get to the data set refresh history in Data sets and then Refresh History
And you can get to the Dataflow refresh history via Dataflows
Data Set Issues Refresh History
Dataflow Issues Refresh History
The actual Schedule seems to be fine. All I can think of is that possibly at 12 it is still 1 day so I could possibly introduce more data refreshes to the dataflow issues data set
Test 2 Adding more refreshes on the Dataflow issues data set
Its a very quick refresh because its just two dates. Lets see if this changes things.
Debbies Microsoft Power BI, SQL and Azure Blog 