Craig Porteous

How to manage Power BI dataset refresh failures

November 30, 2017 by

As I covered in a previous post How to connect to (and query) Power BI and Azure using PowerShell, Power BI can be difficult to manage and administer, unlike on-premises BI solutions. One such concern that will often require quick action is the failure of a dataset refresh.

If your reports and dashboards all rely on live connection or DirectQuery data sources like Azure SQL Database, Azure SQL Data Warehouse or SQL Server Analysis Services (on-premises or in Azure) then you won’t have to worry about dataset refreshes and this post will just be some interesting reading.

Chances are, that you probably have at least one report that relies on an on-premises database or file source and you need to schedule it to refresh regularly to get up to date data. It’s in managing this functionality that the current offering within Power BI falls short, hopefully not for long with the rate updates are coming!

Built-in failure notifications

Power BI has a method to notify you of a failed dataset, which is great. You obviously want to know when data wasn’t updated & you probably want to notify users or customers too. You may have received such an alert already. They look something like this:

That’s a great first step but there is a great deal further we need to go to satisfy my inner Administrator.

Who owns this!?

You may have noticed the little checkbox near the bottom of each dataset settings page that lets you turn these failure notifications on.

The issue here is that there is no CC for a wider admin user or group. This notification will only email the current dataset owner in the event of a failure. Depending on your process for deploying content to Power BI or how users interact with it, this could be an analyst, a sales manager, a developer or indeed, the person who would be able to source & fix the issue.

Even with processes in place to “hand over” dataset ownership to a single administrator account, there will always be fluidity in where these notifications get sent, thus delaying the resolution of any issues that arise

But WHAT went wrong!?

Oops, something went wrong! You’ve received your failure notification; now let’s check the email to see what happened.

Referring to the email excerpt above, we can see the dataset name & its next refresh time. Great, but what if you have the same dataset across several workspaces, maybe in a DEV, QA, PROD scenario?

There’s no link to the failing dataset or the parent workspace. At the time of writing this, the Learn More link also hits a 404.

Here is a good alternative page for troubleshooting dataset refresh issues: Troubleshooting Refresh Scenarios

PowerShell alerting

Building on the API interactions within PowerShell from my previous blog posts I wanted to create my own dataset failure monitor. It’s not perfect but it will get me a lot more information than I get from the built-in functionality, and allow me to respond quicker too.

I started by splitting out two reusable components of this monitor script to functions. The authentication & the notification.

I reference these in the main script but I plan on expanding and building these into a module in the future. Keep an eye on my GitHub. For now, save them with their function names in the same folder as the monitor script and you’re ready to go.

Get-PBIAuthTokenUnattended

This first one is the authentication function. It enables unattended authentication into Azure with the use of an encrypted text file for your account’s password. The main blocker you will have with this function is the requirement for a Power BI “app”. If you already have your ClientID & Client Secret to hand, you can skip Steps 1 & 2.

  1. The Power BI app can be created from the Power BI app registration page. You do not need to be an Azure or Power BI admin to do so which is great. Make sure you choose the Server-side Web App type, which will generate both a ClientID and a Client_Secret. The ClientID can be retrieved from Azure if needed but if you don’t make note of the Client_Secret when you create the app, it’s gone. Start again. You will want to at least tick all the options under the Dataset API section for permissions.

  2. The next thing you will have to do is log in to Azure using the account you created the app with to Grant Permissions, allowing users to authenticate with the app. This can be found under the App Registrations section shown below.

  3. Your tenant ID can be found a few ways. The authentication script also tries to find it for you if you leave that parameter off when you call the script, based on the domain of the user you provide so don’t worry too much about that.
    • Log in to Microsoft Azure as an administrator. In the Microsoft Azure portal, click Azure Active Directory. Under Manage, click Properties. The tenant ID is shown in the Directory ID box.
    • Using this command, it will query Azure anonymously (thanks to user5347643 on StackOverflow):
Note: I added the -UseBasicParsing parameter which negates the need for IE to be installed/configured locally.

Invoke-AlertEmail

The notification function basically builds up a generic HTML email that I intend on using for more PowerShell scripts & error handling, so feel free to adjust as you wish. I replicated the style of the Power BI failure notifications as a quick, clean solution. There’s even a parameter to pass a hex colour to this function, so you can colour code your emails! If you need to authenticate to your mail server, we can use a similar method to the authentication function, by using an encrypted text file. I have included this and added a switch (-auth) when calling the script to enable that functionality.

PowerBI_Dataset_Alerting

Finally, here is the actual monitoring script. You’ll notice that I use the email function above in my catch blocks; this is because the script is intended to be run as a scheduled task so email makes the most sense to notify of exceptions.

Here’s an overview of what the script does:

You will want to schedule the monitor script to run at the interval to suit your SLA/OLAs. I set it to run every 15 minutes. This means, at the longest interval, it would take up to 14 minutes to detect a failed dataset, force a manual refresh, then another 15 minutes before it would email out to warn of the failure. This is a good window to accommodate a timeout failure where the manual refresh might run for a while before completing or failing again.

Oops, this is what went wrong…

So, my dataset has failed and then failed again when we tried to force a refresh which means it’s likely not a short-term issue that will resolve itself. The refresh failure email will then be sent to your chosen recipient(s).

It’s going to contain:

  • A relevant title, (in this case, the name/purpose of the script)
  • The name of the parent workspace & a hyperlink to that workspace
  • The name & a link to the failing dataset’s settings page so you can diagnose the failure easily
  • A courteous Thank you from the script owner 😉 (which can be set in the parameters)

Some things to consider with this script:

  • The user account you use to connect to Power BI with is very important, it doesn’t have to own any of the datasets but it cannot monitor datasets if it doesn’t have the permission to the workspace the dataset is contained in. Bear this in mind. In my case, I have implemented an admin account that gets added to all workspaces as a matter of process but even this can miss user created workspaces. You may have better luck with an Azure admin. Please let me know if you do.

Further analysis with PowerShell

You can use an extension of this script to query all dataset refresh history & store it in a local database. This could be used to pick up more nuanced issues

  • Repeated failure trends
  • Refresh durations over time
  • Datasets being refreshed too often, or not often enough

See more

For BI documentation, consider ApexSQL Doc, a tool that documents SQL Server instances, databases, objects, SSIS packages, SSAS cubes, SSRS reports, Tableau server sites and SharePoint Server farms.

References


Craig Porteous

Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner

View all posts by Craig Porteous
Craig Porteous
PowerBI

About Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner View all posts by Craig Porteous

4,529 Views